将多列转换为单行 [英] Convert multiple columns into a single row

问题描述

我有需要将当前数据更改为所需数据的情况.

I have scenario where I need to change the present data to required one.

下面是数据

    ColumnA ColumnB ColumnC   
      P       Q       R   
      S       T       U   
      V       W       X

所需格式为

 ColumnA1 ColumnB1 ColumnC1 ColumnA2 ColumnB2 ColumnC2   ColumnA3   ColumnB3 ColumnC3    
   P         Q         R       S        T         U            V        W       x

请帮助我.

推荐答案

有几种方法可以得到结果，所有这些方法都涉及使用row_number为数据的每一行生成一个序列.

There are a few ways that you can get the result all of them involve using row_number to generate a sequence for each row of your data.

您可以将聚合函数与CASE表达式一起使用:

You can use an aggregate function with a CASE expression:

select 
  max(case when seq = 1 then columna end) columna_1,
  max(case when seq = 1 then columnb end) columnb_1,
  max(case when seq = 1 then columnc end) columnc_1,
  max(case when seq = 2 then columna end) columna_2,
  max(case when seq = 2 then columnb end) columnb_2,
  max(case when seq = 2 then columnc end) columnc_2,
  max(case when seq = 3 then columna end) columna_3,
  max(case when seq = 3 then columnb end) columnb_3,
  max(case when seq = 3 then columnc end) columnc_3
from 
(
  select columna, columnb, columnc,
    row_number() over(order by columna) seq
  from yourtable
) d;

请参见带演示的SQL提琴.

您可以使用PIVOT功能，但是您将要先取消3列数据的透视，然后再应用PIVOT.取消透视过程将您的3列数据转换为多行.您可以使用UNPIVOT函数或交叉应用"来执行此操作:

You can use the PIVOT function, but you will want to unpivot your 3 columns of data first, then apply the PIVOT. The unpivot process converts your 3 columns of data into multiple rows. You can use the UNPIVOT function or CROSS APPLY to perform this:

select ColumnA_1, ColumnB_1, ColumnC_1,
  ColumnA_2, ColumnB_2, ColumnC_2,
  ColumnA_3, ColumnB_3, ColumnC_3
from
(
  select col = col+'_'+cast(seq as varchar(10)),
    value
  from
  (
    select columna, columnb, columnc,
      row_number() over(order by columna) seq
    from yourtable
  ) d
  cross apply
  (
    select 'ColumnA', columna union all
    select 'ColumnB', columnb union all
    select 'ColumnC', columnc
  ) c (col, value)
) s
pivot
(
  max(value)
  for col in (ColumnA_1, ColumnB_1, ColumnC_1,
              ColumnA_2, ColumnB_2, ColumnC_2,
              ColumnA_3, ColumnB_3, ColumnC_3)
) piv;

请参见带演示的SQL提琴.如果数量有限或已知值，上述两个版本效果很好，但是如果数量未知，则必须考虑使用动态SQL来获得最终结果:

See SQL Fiddle with Demo. The above two versions work great if you have a limited number or known values, but if you will have an unknown number, then you will have to look at using dynamic SQL to get the final result:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col+'_'+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(order by columna) seq
                      from yourtable
                    ) d
                    cross apply
                    (
                      select 'ColumnA', 1 union all
                      select 'ColumnB', 2 union all
                      select 'ColumnC', 3
                    ) c (col, so)
                    group by col, seq, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ' + @cols + ' 
            from 
            (
              select col = col+''_''+cast(seq as varchar(10)),
                value
              from
              (
                select columna, columnb, columnc,
                  row_number() over(order by columna) seq
                from yourtable
              ) d
              cross apply
              (
                select ''ColumnA'', columna union all
                select ''ColumnB'', columnb union all
                select ''ColumnC'', columnc
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

请参见带演示的SQL提琴.所有版本都会给出结果:

See SQL Fiddle with Demo. All versions will give a result:

| COLUMNA_1 | COLUMNB_1 | COLUMNC_1 | COLUMNA_2 | COLUMNB_2 | COLUMNC_2 | COLUMNA_3 | COLUMNB_3 | COLUMNC_3 |
|         P |         Q |         R |         S |         T |         U |         V |         W |         X |

这篇关于将多列转换为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！