How to convert every other row to column in T-SQL?

问题:

Perhaps this was a question that has already been asked. How should one convert every other row to columns in T-SQL? Here is some T-sql to create the table and sample data.

CREATE TABLE Names
(
     [ID] int, 
    HouseholdId int,
     [FullName] varchar(max), 
     [ssn] varchar(max),
    isPrimary bit
)
;

INSERT INTO names(ID, HouseholdId, [FullName], ssn, isPrimary)
VALUES
    (1, 10,'User1', '111111111', 1),
    (2, 10, 'User2', '222222222', 0),
    (3, 10, 'User3', '333333333', 0),
    (4, 10, 'User4', '444444444', 0),
    (5, 10,'User5', '555555555', 0),
    (6, 20, 'User6', '666666666', 1),
    (7, 20, 'User7', '777777777', 0)
;

I would like to convert every household's users into two columns, something like this.

HouseHold   User Name 1 SSN 1       User Name 2 SSN 2
10          User1       111111111   User2       222222222
            User3       333333333   User4       444444444
            User5       555555555       
20          User6       666666666   User7       777777777

how would I do this?

回答1:

here is the sql that will do what you are looking for. It assigns a row number by partitioning within household id. Then uses a self join to lay out side-by-side. http://sqlfiddle.com/#!6/c8933/10/0

WITH T AS (
select HouseholdId, ID, Fullname, ssn
  , ROW_NUMBER() OVER(PARTITION BY HouseHoldId Order by ID) AS RNUM
FROM Names
)
SELECT A.HouseholdId, A.fullname as username1, A.SSN as ssn1
, B.fullname as username2, B.ssn as ssn2
FROM T A
LEFT JOIN T B ON A.householdID = B.HouseholdId
  AND (A.RNUM = B.RNUM - 1)
WHERE A.RNUM % 2 = 1 

回答2:

This is how you could accomplish the same using a pivot.

select b.HouseholdID
, b.[User Name 1]
, b.[SSN 1]
, b.[User Name 2]
, b.[SSN 2]
from (
    select sub.HouseholdId
    , sub.col_nm_prelim + cast( (((sub.row_nbr + 1) % 2) + 1) as char(1)) as col_nm
    , ceiling(sub.row_nbr / 2.0) as row_nbr
    , sub.col_val
    from (
        select n.HouseholdId
        , 'User Name ' as col_nm_prelim
        , row_number() over (partition by n.HouseHoldID order by n.ID asc) as row_nbr
        , n.FullName as col_val
        from Names as n
        union all
        select n.HouseholdId
        , 'SSN ' as col_nm_prelim
        , row_number() over (partition by n.HouseHoldID order by n.ID asc) as row_nbr
        , n.SSN as col_val
        from Names as n
        ) as sub
    ) as a
pivot (max(a.col_val) for a.col_nm in ([User Name 1], [User Name 2], [SSN 1], [SSN 2])) as b
order by b.HouseholdID asc
, b.row_nbr asc