How to convert each row to column in T-SQL?

Question

Perhaps this is a question that has already been asked. How to convert each row into columns in T-SQL? Here are some T-sql for creating tables 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 all home 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 can i do this?

Answer 1

here is sql that will do what you are looking for. It assigns a line number by splitting by family identifier. Then uses a self-join to lay 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 

Answer 2

Here's how you could do the same with help 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