此数据转换称为A
PIVOT，从SQL Server 2005开始，具有将数据从行转换为列的功能。
有多种方法可以完成此操作，具体取决于您是否有要转换为列的静态数量的值。所有这些都涉及向
row_number()数据添加a
，因此您可以返回任何产品的多行。
您可以将聚合函数与
CASE表达式一起使用：
select max(case when product = 'x' then detail end) x, max(case when product = 'y' then detail end) y, max(case when product = 'z' then detail end) zfrom( select p.product, d.detail, row_number() over(partition by p.product order by p.slno) rn from product p inner join detail d on p.product = d.product) srcgroup by rn
参见带有演示的SQL Fiddle
您可以使用以下
PIVOT功能：
select x, y, zfrom( select p.product, d.detail, row_number() over(partition by p.product order by p.slno) rn from product p inner join detail d on p.product = d.product) srcpivot( max(detail) for product in (x, y, z)) piv
请参阅带有演示的SQL Fiddle。
如果您有未知数量的值（在这种情况下为产品）要转换为列，那么您将要使用动态SQL：
DECLARE @cols AS NVARCHAr(MAX), @query AS NVARCHAr(MAX)select @cols = STUFF((SELECT distinct ',' + QUOTENAME(product) from product FOR XML PATH(''), TYPE ).value('.', 'NVARCHAr(MAX)') ,1,1,'')set @query = 'SELECt ' + @cols + ' from ( select p.product, d.detail, row_number() over(partition by p.product order by p.slno) rn from product p inner join detail d on p.product = d.product ) x pivot ( max(detail) for product in (' + @cols + ') ) p 'execute(@query)参见带有演示的SQL Fiddle
所有查询的结果是：
| X | Y | Z |--------------------------| good | bad | worse || bad | (null) | (null) |
