SQL 查询日期到具有唯一 ID 的新列中 [英] SQL query date into new columns with unique ID

问题描述

我有以下列、一个 5 位数的 ID、日期和一个值.ID 仅在出现新日期时重复.

I have the following columns, a 5 digit ID, date and a value. The ID repeats only when a new date is present.

ID        Date     Value
11111  2014-12-31  45 
22222  2014-12-31  435 
33333  2014-12-31  11 
11111  2014-12-30  5 
22222  2014-12-30  2245 
33333  2014-12-30  86 
11111  2014-12-29  43 
22222  2014-12-29  4678 
33333  2014-12-29  2494  

我正在尝试创建一个将显示以下内容的 SQL 查询(日期是列名):

I am trying to create an SQL query that will display the following (dates are column names):

ID      2014-12-31     2014-12-30       2014-12-29 
11111      45               5               43 
22222     435              2245            4678 
33333      11               86             2494 

使用 MS SQL 执行此操作的最佳方法是什么.

What is the best way of doing this using MS SQL.

谢谢

推荐答案

正如评论所指出的，您需要对数据进行 PIVOT.这是使用动态交叉表的一种方法.

As pointed out by the comments, you need to PIVOT your data. Here is one way using a Dynamic Crosstab.

阅读 Jeff Moden 的这篇文章以供参考:http://www.sqlservercentral.com/articles/Crosstab/65048

Read this article by Jeff Moden for reference: http://www.sqlservercentral.com/articles/Crosstab/65048

CREATE TABLE temp(
    ID      INT,
    [Date]  DATE,
    Value   INT
)
INSERT INTO temp VALUES
(11111, '2014-12-31', 45),
(22222, '2014-12-31', 435),
(33333, '2014-12-31', 11),
(11111, '2014-12-30', 5),
(22222, '2014-12-30', 2245),
(33333, '2014-12-30', 86),
(11111, '2014-12-29', 43),
(22222, '2014-12-29', 4678),
(33333, '2014-12-29', 2494);

DECLARE @sql1 VARCHAR(2000) = ''
DECLARE @sql2 VARCHAR(2000) = ''
DECLARE @sql3 VARCHAR(2000) = ''

SELECT @sql1 = 
'SELECT
    ID
'

SELECT @sql2 = @sql2 + 
'   ,MIN(CASE WHEN [Date] = CAST(''' + CONVERT(VARCHAR(10), [Date], 120) + ''' AS Date) THEN Value END) AS [' 
    + CONVERT(VARCHAR(10), [Date], 120) + ']'+ CHAR(10)
FROM(
    SELECT  DISTINCT [Date] FROM temp
)t
ORDER BY [Date] DESC

SELECT @sql3 = 
    'FROM temp
    GROUP BY ID
    ORDER BY ID'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

DROP TABLE temp

如果您想使用固定名称，您需要为每个Date 分配一个数字.这可以使用 ROW_NUMBER() 来完成:

If you want to use fixed name, you'll want to assign a number for each Date. This can be done using ROW_NUMBER():

SELECT @sql2 = @sql2 + 
'   ,MIN(CASE WHEN [Date] = CAST(''' + CONVERT(VARCHAR(10), [Date], 120) + ''' AS Date) THEN Value END) AS [Date' + CONVERT(VARCHAR, rn) + ']'+ CHAR(10)
FROM(
    SELECT 
        [Date],
        rn = ROW_NUMBER() OVER(ORDER BY [Date])
    FROM (
        SELECT  DISTINCT [Date]FROM temp
    )x
)t
ORDER BY [Date] DESC

这篇关于SQL 查询日期到具有唯一 ID 的新列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！