SQL Server PIVOT列数据 [英] SQL Server PIVOT Column Data

问题描述

我有一张表，数据如下:

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

这篇关于SQL Server PIVOT列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！