将多行和多列值显示为单行，多列值

Question

我必须在一行中显示单个人的多个收入，收入类型和雇主名称值。因此，如果“ A”有来自三个不同来源的三个不同的收入，

 id  | Name | Employer     |  IncomeType       | Amount

 123 |  XYZ | ABC.Inc      |   EarningsformJob |  $200.00

 123 |  XYZ | Self         | Self Employment   |  $300.00

 123 | XYZ. |  ChildSupport|   Support         |  $500.00

我需要向他们展示

 id | Name | Employer1 | Incometype1| Amount1 | Employer2 | incometype2 | Amount2| Employer3 | Incometype3| Amount3.....

 123 |XYZ | ABC.Inc |EarningsformJob |  $200.00|Self  | Self Employment  |  $300.00|ChildSupport|   Support |  $500.00.....

我既需要“固定列数”（我们知道雇主，收入类型和金额列将要重复多少次）逻辑又需要“动态显示列”（未知这些列要重复的次数）

谢谢。

Answer 1

由于使用的是SQL Server，因此有几种方法可以将数据行转置为列。

聚合函数/ CASE： 您可以将聚合函数与CASE表达式一起使用row_number()。此版本要求您具有已知数量的值才能成为列：

select id,
  name,
  max(case when rn = 1 then employer end) employer1,
  max(case when rn = 1 then IncomeType end) IncomeType1,
  max(case when rn = 1 then Amount end) Amount1,
  max(case when rn = 2 then employer end) employer2,
  max(case when rn = 2 then IncomeType end) IncomeType2,
  max(case when rn = 2 then Amount end) Amount2,
  max(case when rn = 3 then employer end) employer3,
  max(case when rn = 3 then IncomeType end) IncomeType3,
  max(case when rn = 3 then Amount end) Amount3
from
(
  select id, name, employer, incometype, amount,
    row_number() over(partition by id order by employer) rn
  from yourtable
) src
group by id, name;

请参阅带有演示的SQL Fiddle。

PIVOT / UNPIVOT：
您可以使用UNPIVOT和PIVOT函数来获取结果。该UNPIVOT转换您的多列Employer，IncomeType并Amount为多排应用枢轴之前。您没有具体说明哪个版本的SQL
Server，假设您拥有已知数量的值，则可以在SQL Server 2005+中使用以下内容，该版本使用CROSS APPLY和UNION
ALL来取消透视：

select id, name, 
  employer1, incometype1, amount1,
  employer2, incometype2, amount2,
  employer3, incometype3, amount3
from
(
  select id, name, col+cast(rn as varchar(10)) col, value
  from
  (
    select id, name, employer, incometype, amount,
      row_number() over(partition by id order by employer) rn
    from yourtable
  ) t
  cross apply
  (
    select 'employer', employer union all
    select 'incometype', incometype union all
    select 'amount', cast(amount as varchar(50))
  ) c (col, value)
) src
pivot
(
  max(value)
  for col in (employer1, incometype1, amount1,
              employer2, incometype2, amount2,
              employer3, incometype3, amount3)
) piv;

请参阅带有演示的SQL Fiddle。

动态版本： 最后，如果您有未知数量的值，则需要使用动态SQL生成结果。

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(rn as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by id order by employer) rn
                      from yourtable
                    ) d
                    cross apply
                    ( 
                      select 'employer', 1 union all
                      select 'incometype', 2 union all
                      select 'amount', 3
                    ) c (col, so)
                    group by col, rn, so
                    order by rn, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, name,' + @cols + ' 
             from 
             (
                select id, name, col+cast(rn as varchar(10)) col, value
                from
                (
                  select id, name, employer, incometype, amount,
                    row_number() over(partition by id order by employer) rn
                  from yourtable
                ) t
                cross apply
                (
                  select ''employer'', employer union all
                  select ''incometype'', incometype union all
                  select ''amount'', cast(amount as varchar(50))
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

请参阅带有演示的SQL Fiddle。所有版本都会产生结果：

|  ID | NAME | EMPLOYER1 |     INCOMETYPE1 | AMOUNT1 |    EMPLOYER2 | INCOMETYPE2 | AMOUNT2 | EMPLOYER3 |     INCOMETYPE3 | AMOUNT3 |
-------------------------------------------------------------------------------------------------------------------------------------
| 123 |  XYZ |   ABC.Inc | EarningsformJob |     200 | ChildSupport |     Support |     500 |      Self | Self Employment |     300 |