将多行和多列值显示为单行，多列值 [英] Display multiple rows and column values into a single row, multiple column values

问题描述

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

I have to show multiple incomes, type of income and employer name values for a single individual in a single row. So, if 'A' has three different incomes from three different sources,

 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.....

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

I need both 'fixed number of columns' (where we know how many times employer, incometype and amount colums are going to repeat)logic and 'dynamic display of columns' ( unknown number of times these columns are going to repeat)

谢谢.

推荐答案

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

Since you are using SQL Server there are several ways that you can transpose the rows of data into columns.

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

Aggregate Function / CASE: You can use an aggregate function with a CASE expression along with row_number(). This version would require that you have a known number of values to become columns:

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小提琴.

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

PIVOT/UNPIVOT: You could use the UNPIVOT and PIVOT functions to get the result. The UNPIVOT converts your multiple columns of Employer, IncomeType and Amount into multiples rows before applying the pivot. You did not specific what version of SQL Server, assuming you have a known number of values then you could use the following in SQL Server 2005+ which uses CROSS APPLY with UNION ALL to unpivot:

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小提琴.

动态版本::最后，如果值的数量未知，则需要使用动态SQL来生成结果.

Dynamic Version: Lastly, if you have an unknown number of values then you will need to use dynamic SQL to generate the result.

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小提琴.所有版本都给出结果:

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

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

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