由于使用的是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) Amount3from( select id, name, employer, incometype, amount, row_number() over(partition by id order by employer) rn from yourtable) srcgroup 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, amount3from( 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)) srcpivot( 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) rnfrom yourtable ) d cross apply ( select 'employer', 1 union allselect 'incometype', 2 union allselect '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 |
