sql - 将多行和多列值显示为单行多列值

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 表达式的聚合函数以及 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 with Demo .

枢轴/非枢轴:您可以使用 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 with Demo .

动态版本:最后，如果您有未知数量的值，那么您将需要使用动态 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 with Demo .所有版本都给出了结果:

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