"Transpose-esque" - T/SQL

Question

DECLARE @TABLE TABLE (NAME varchar(10), DOB Datetime2, Location varchar(50), Phone int)
INSERT INTO @TABLE (NAME, DOB, Location, Phone)
SELECT 'Name1','2000-01-01','USA',1234567890
UNION ALL
SELECT 'Name2','2000-01-02','CAN',0987654321

SELECT * FROM @TABLE

/* Current Output

NAME    DOB                         Location    Phone
Name1   2000-01-01 00:00:00.0000000 USA         1234567890
Name2   2000-01-02 00:00:00.0000000 CAN         987654321

Desired Output

Catagory    N1              N2          ...Nn
            'NAME1'         'Name2'
DOB         '2000-01-01'    '2000-01-02'
Location    'USA'           'CAN'
Phone       1234567890      0987654321

Catagory, N1, N2,...Nn are column names (Nn = there can be dynamica number of "Name" There is no catagory name for 'Name1,'Name2',...'Namen' Not sure how to do this properly...XML maybe? Please help! */

Thank you

Answer 1

You can use the PIVOT function to get the result but you will need to use a few other functions first to get the final product.

First, you will want to create a unique sequence for each row (it doesn't look like you have one), this value is going to be used to create your final list of new columns. You can use row_number() to create this value:

select name, dob, location, phone,
  row_number() over(order by name) seq
from yourtable

See SQL Fiddle with Demo. Once you have created this unique value then you can unpivot the multiple columns of data name, dob, location and phone. Depending on your version of SQL Server you can use the unpivot function or CROSS APPLY:

select 'N'+cast(seq as varchar(10)) seq,
  category, value, so
from
(
  select name, dob, location, phone,
    row_number() over(order by name) seq
  from yourtable
) src
cross apply
(
  select 'name', name, 1 union all
  select 'DOB', convert(varchar(10), dob, 120), 2 union all
  select 'Location', location, 3 union all
  select 'Phone', cast(phone as varchar(15)), 4
) c (category, value, so);

See SQL Fiddle with Demo. This will get your data in the format:

| SEQ | CATEGORY |      VALUE | SO |
|-----|----------|------------|----|
|  N1 |     name |      Name1 |  1 |
|  N1 |      DOB | 2000-01-01 |  2 |
|  N1 | Location |        USA |  3 |
|  N1 |    Phone | 1234567890 |  4 |

Now you can easily apply the PIVOT function:

SELECT category, n1, n2 
FROM 
(
  select 'N'+cast(seq as varchar(10)) seq,
    category, value, so
  from
  (
    select name, dob, location, phone,
      row_number() over(order by name) seq
    from yourtable
  ) src
  cross apply
  (
    select 'name', name, 1 union all
    select 'DOB', convert(varchar(10), dob, 120), 2 union all
    select 'Location', location, 3 union all
    select 'Phone', cast(phone as varchar(15)), 4
  ) c (category, value, so)
) d
pivot
(
  max(value)
  for seq in (N1, N2)
) piv
order by so;

See SQL Fiddle with Demo. The above works great if you have a limited number of values but if you will have an unknown number of names, then you will need to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME('N'+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(order by name) seq
                      from yourtable
                    )d
                    group by seq
                    order by seq
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT category, ' + @cols + ' 
            from 
            (
              select ''N''+cast(seq as varchar(10)) seq,
                category, value, so
              from
              (
                select name, dob, location, phone,
                  row_number() over(order by name) seq
                from yourtable
              ) src
              cross apply
              (
                select ''name'', name, 1 union all
                select ''DOB'', convert(varchar(10), dob, 120), 2 union all
                select ''Location'', location, 3 union all
                select ''Phone'', cast(phone as varchar(15)), 4
              ) c (category, value, so)
            ) x
            pivot 
            (
                max(value)
                for seq in (' + @cols + ')
            ) p 
            order by so'

execute sp_executesql @query;

See SQL Fiddle with Demo. They both give a result of:

| CATEGORY |         N1 |         N2 |
|----------|------------|------------|
|     name |      Name1 |      Name2 |
|      DOB | 2000-01-01 | 2000-01-02 |
| Location |        USA |        CAN |
|    Phone | 1234567890 |  987654321 |