如何在sql中创建查询以透视数据? [英] How to create query in sql to pivot data?

问题描述

我有两个名为PRODUCT和DETAIL

TABLE: PRODUCT
slno  product
1          x    
2          y
3          z

TABLE: DETAIL
product    detail
x          good
y          bad
z          worse
x          bad

我需要获取输出

TABLE
X      Y       Z
good   bad     worse
bad

推荐答案

此数据转换被称为

This data transformation is known as a PIVOT and starting in SQL Server 2005 there is a function to convert the data from rows to columns.

有多种方法可以完成此操作，具体取决于您是否有要转换为列的静态数量的值.所有这些都涉及在数据中添加row_number()，以便您可以返回任何产品的多行.

There are several ways that this can be done depending on whether or not you have a static number of values to transpose into columns. All of them involve adding a row_number() to the data so you can return the multiple rows of any of the products.

您可以将聚集函数与CASE表达式一起使用:

You can use an aggregate function with a CASE expression:

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) z
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
) src
group by rn

请参见带有演示的SQL小提琴

您可以使用PIVOT功能:

select x, y, z
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
) src
pivot
(
  max(detail)
  for product in (x, y, z)
) piv

请参见带有演示的SQL小提琴.

如果您有未知数量的值(在这种情况下为乘积)要转换为列，那么您将要使用动态SQL:

If you have an unknown number of values (products in this case) to turn into columns, then you will want to use dynamic 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小提琴

所有查询的结果是:

|    X |      Y |      Z |
--------------------------
| good |    bad |  worse |
|  bad | (null) | (null) |

这篇关于如何在sql中创建查询以透视数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！