SQL Server PIVOT 列数据

2022-01-22 00:00:00 pivot sql-server

我有一个表格，数据如下:

I have a table with data as given below:

DATE            Price
----------      ------
31/12/2009  10
31/12/2009  11
31/12/2009  12
30/12/2009  20
30/12/2009  21
30/12/2009  22
29/12/2009  30
29/12/2009  32
29/12/2009  31

我想将这些数据转换如下:

I want to convert this data as given below:

31/12/2009  30/12/2009  29/12/2009
----------  ----------  ----------
10          10          10
11          11          11
12          12          12

但日期列中的值是动态的.所以，我不知道如何使用 SQL Server Pivot 来使用它.

But the values in the date column is dynamic. So, I dont know how to use this using SQL Server Pivot.

请告诉我如何获取这些数据.

Could you please let me know how to get this data.

以下是复制此场景的脚本:

Given below is the script to replicate this scenario:

CREATE TABLE TEMP(EffectiveDate DATETIME,Price INT)
INSERT INTO TEMP(EffectiveDate,Price)
SELECT GETDATE(),10
UNION ALL
SELECT GETDATE(),11
UNION ALL
SELECT GETDATE(),12
UNION ALL
SELECT GETDATE()-1,20
UNION ALL
SELECT GETDATE()-1,21
UNION ALL
SELECT GETDATE()-1,22
UNION ALL
SELECT GETDATE()-2,30
UNION ALL
SELECT GETDATE()-2,32
UNION ALL
SELECT GETDATE()-2,31

SELECT CONVERT(VARCHAR,EffectiveDATE,103) AS 'DATE',Price FROM Temp

提前致谢，

马赫什

推荐答案

好的，正如我提到的，你的数据没有意义，但也许这会有所帮助.

OK, as I mentioned, your data does not make sense, but maybe this can help.

创建动态数据透视表的唯一方法是创建动态 sql.

The only way to create a dynamic pivot, is by creating dynamic sql.

此外，PIVOT 要求您使用聚合函数 (SUM, AVG, COUNT).

Also, PIVOT requires that you use an Aggregate function (SUM, AVG, COUNT).

好的，看看能不能帮到你.

Ok, let see if this can help you.

CREATE TABLE #TEMP  (EffectiveDate DATETIME,Price INT) 
INSERT INTO #TEMP(EffectiveDate,Price) 
SELECT GETDATE(),10 
UNION ALL 
SELECT GETDATE(),11 
UNION ALL 
SELECT GETDATE(),12 
UNION ALL 
SELECT GETDATE()-1,20 
UNION ALL 
SELECT GETDATE()-1,21 
UNION ALL 
SELECT GETDATE()-1,22 
UNION ALL 
SELECT GETDATE()-2,30 
UNION ALL 
SELECT GETDATE()-2,32 
UNION ALL 
SELECT GETDATE()-2,31 

DECLARE @Cols VARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    (
            SELECT DISTINCT 
                    CONVERT(VARCHAR,EffectiveDATE,103) colName
            FROM    #TEMP
        ) s
ORDER BY colName DESC

DECLARE @query VARCHAR(MAX)
SET @query = N'SELECT *
FROM
(SELECT CONVERT(VARCHAR,EffectiveDATE,103) AS ''DATE'',Price
            FROM    #TEMP) p
PIVOT
(
SUM(Price) FOR DATE IN
( '+
@cols +' )
) AS pvt' 

EXECUTE(@query)


DROP TABLE #TEMP

相关文章