SQL将行旋转为列...动态行数 [英] SQL rotate rows to columns...dynamic number of rows

问题描述

可能重复:
SQL Server动态PIVOT查询?

Possible Duplicate:
SQL Server dynamic PIVOT query?

我有一个具有以下结构的数据集.

I have a dataset that has the below structure.

CREATE TABLE #TempTable
  (
     Measure_ID  INT,
     measurement DECIMAL(18, 4)
  )

INSERT INTO #TempTable
VALUES
(1,2.3)
,(1,3.4)
,(1,3.3)
,(2,3)
,(2,2.3)
,(2,4.0)
,(2,4.5)

我需要产生看起来像这样的输出.

I need to produce output that will look like this.

1,2.3,3.4,3.3
2,3,2.3,4.0,4.5

基本上是Measure_ID的枢纽.不幸的是，可以有不限数量的measure_id.因此，Pivot退出了.

Basically its a pivot on Measure_ID. Unfortunately, there can be an unlimited number of measure_id's. So Pivot is out.

我希望避免使用CURSORS，但是如果事实证明这是最好的方法，那么将是这样.

I'm hoping to avoid CURSORS, but will if that turns out to be the best approach.

推荐答案

如果值的数量未知，则可以在动态SQL中使用PIVOT:

If you have an unknown number of values, then you can use a PIVOT with dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' 
                        + QUOTENAME('Measurement_' + cast(rn as varchar(10))) 
                    from temptable
                    cross apply
                    (
                      select row_number() over(partition by measure_id order by measurement) rn
                      from temptable
                    ) x
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT measure_id, ' + @cols + ' from 
             (
                select measure_id, measurement,
                  ''Measurement_''
                    + cast(row_number() over(partition by measure_id order by measurement) as varchar(10)) val
                from temptable
            ) x
            pivot 
            (
                max(measurement)
                for val in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL提琴

如果您拥有已知数量的值，则可以对这些值进行硬编码，类似于:

If you have a known number of values, then you can hard-code the values, similar to this:

SELECT measure_id, [Measurement_1], [Measurement_2], 
               [Measurement_3], [Measurement_4]
from 
(
  select measure_id, measurement,
    'Measurement_'
     + cast(row_number() over(partition by measure_id order by measurement) as varchar(10)) val
  from temptable
) x
pivot 
(
   max(measurement)
   for val in ([Measurement_1], [Measurement_2], 
               [Measurement_3], [Measurement_4])
) p 

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

两个查询都将产生相同的结果:

Both queries will produce the same results:

MEASURE_ID | MEASUREMENT_1 | MEASUREMENT_2 | MEASUREMENT_3 | MEASUREMENT_4
==========================================================================
1          | 2.3           | 3.3           | 3.4           | (null)
2          | 2.3           | 3             | 4             | 4.5

这篇关于SQL将行旋转为列...动态行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！