如何在sql server中将多行数据转换为列数据

Question

我的sql查询的结果如下：

Id         atr1       atr2       atr3       atr4

1          a          bsdf       csdfs      djk
5          esdds      f          gds        hkkj
8          i          j          ksd        lk
9          ads        sdf        dfse       wer  

现在我需要上面的结果，格式如下：

S.no   1        2         3      4
Id     1        5         8      9
atr1   a        esdds     i      ads 
atr2   bsdf     f         j      sdf 
atr3   csdfs    gds       ksd    dfse
atr4   djk      hkkj      lk     wer

我无法使用Pivot和Unpivot执行此操作。

Answer 1

为了获得您想要的最终结果，您需要取消透视当前列，然后应用透视函数。但是在取消透视/透视数据之前，我建议使用row_number()为每一行生成一个惟一的值。

有几种不同的方法可以获得结果，包括使用带有CASE表达式的聚合函数、静态透视和动态透视。

使用CASE:进行聚合首先可以使用UNION ALL查询将多个列转换为行，然后使用带有CASE表达式的聚合函数来获得结果：

;with cte as
(
  select id, atr1, atr2, atr3, atr4,
    row_number() over(order by id) seq
  from yourtable
) 
select s_no,
  max(case when seq = 1 then value end) [1],
  max(case when seq = 2 then value end) [2],
  max(case when seq = 3 then value end) [3],
  max(case when seq = 4 then value end) [4]
from
(
  select seq, s_no = 'id', value = cast(id as varchar(5)), so = 1
  from cte
  union all
  select seq, s_no = 'atr1', value = atr1, so = 2
  from cte
  union all
  select seq, s_no = 'atr2', value = atr2, so = 3
  from cte
  union all
  select seq, s_no = 'atr3', value = atr3, so = 4
  from cte
  union all
  select seq, s_no = 'atr4', value = atr4, so = 5
  from cte
) d
group by s_no, so
order by so;

请参阅SQL Fiddle with Demo

PIVOT Static UNPIVOT/：如果您想要转换的值数量有限，那么可以对查询进行硬编码。取消透视的过程将转换多个列id、atr1、atr2、atr3和atr4，并将它们转换为多个行。您没有指定正在使用的SQL Server的版本，但可以使用UNPIVOT函数或CROSS APPLY完成此操作。

select seq, s_no, value, so
from
(
  select id, atr1, atr2, atr3, atr4,
    row_number() over(order by id) seq
  from yourtable
) s
cross apply
(
  select 'id', cast(id as varchar(5)), 1 union all
  select 'atr1', atr1, 2 union all
  select 'atr2', atr2, 3 union all
  select 'atr3', atr3, 4 union all
  select 'atr4', atr4, 5
) c (s_no, value, so);

参见SQL Fiddle with Demo。我使用了带有UNION ALL的CROSS APPLY来选择每一列并将它们转换为多行。此查询将以以下格式获取您的数据：

| SEQ | S_NO | VALUE | SO |
|   1 |   id |     1 |  1 |
|   1 | atr1 |     a |  2 |
|   1 | atr2 |  bsdf |  3 |
|   1 | atr3 | csdfs |  4 |
|   1 | atr4 |   djk |  5 |
|   2 |   id |     5 |  1 |

一旦将数据放入多个行中，就可以应用PIVOT函数：

select s_no, [1], [2], [3], [4]
from
(
  select seq, s_no, value, so
  from
  (
    select id, atr1, atr2, atr3, atr4,
      row_number() over(order by id) seq
    from yourtable
  ) s
  cross apply
  (
    select 'id', cast(id as varchar(5)), 1 union all
    select 'atr1', atr1, 2 union all
    select 'atr2', atr2, 3 union all
    select 'atr3', atr3, 4 union all
    select 'atr4', atr4, 5
  ) c (s_no, value, so)
) d
pivot
(
  max(value)
  for seq in ([1], [2], [3], [4])
) piv
order by so;

参见SQL Fiddle with Demo。

如果你有一个已知的或有限数量的新列要创建，但是如果你有一个未知数量的值要转换成列，那么你会想要考虑使用动态。这将生成一个sql字符串，然后执行该字符串以获得最终结果：

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

select @cols = STUFF((SELECT ',' + QUOTENAME(seq) 
                    from 
                    (
                      select row_number() over(order by id) seq
                      from yourtable
                    )d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT s_no,' + @cols + ' 
            from
            (
              select seq, s_no, value, so
              from
              (
                select id, atr1, atr2, atr3, atr4,
                  row_number() over(order by id) seq
                from yourtable
              ) s
              cross apply
              (
                select ''id'', cast(id as varchar(5)), 1 union all
                select ''atr1'', atr1, 2 union all
                select ''atr2'', atr2, 3 union all
                select ''atr3'', atr3, 4 union all
                select ''atr4'', atr4, 5
              ) c (s_no, value, so)
            ) x
            pivot 
            (
                max(value)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

参见SQL Fiddle with Demo。所有版本都会给出一个结果：

| S_NO |     1 |     2 |   3 |    4 |
|   id |     1 |     5 |   8 |    9 |
| atr1 |     a | esdds |   i |  ads |
| atr2 |  bsdf |     f |   j |  sdf |
| atr3 | csdfs |   gds | ksd | dfse |
| atr4 |   djk |  hkkj |  lk |  wer |