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
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 |