“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

/*电流输出

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

期望输出

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

N1，N2，...Nn都是列名(Nn =可以有动态数的“名称”，对于'Name1‘，'Name2'，’Namen‘不确定如何做properly...XML？请帮帮忙！*/

谢谢

Answer 1

您可以使用PIVOT函数来获得结果，但是您需要首先使用一些其他函数来获得最终产品。

首先，您需要为每一行创建一个唯一的序列(看起来不像有一个序列)，这个值将用于创建新列的最终列表。您可以使用row_number()创建此值：

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

见与Demo。一旦创建了这个唯一的值，就可以取消数据name、dob、location和phone的多列。根据Server版本的不同，您可以使用un枢轴函数或交叉应用：

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

见与Demo。这将以以下格式获取数据：

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

现在，您可以轻松地应用PIVOT函数：

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;

见与Demo。如果您有有限数量的值，但是如果您有一个未知数量的names，则需要使用动态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;

见与Demo。它们都给出了以下结果：

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