更新时间:2022-12-11 22:20:37
由于使用的是SQL Server,因此可以使用PIVOT
函数.
Since you are using SQL Server, then you can use the PIVOT
function.
如果您的值已知,则可以对值进行硬编码:
If your values are known, then you can hard-code the values:
select *
from
(
select id, value, date
from yourtable
) src
pivot
(
max(value)
for date in ([2012-12-29], [2012-10-29], [2013-02-02])
) piv
请参见带演示的SQL提琴.
但是,如果它们未知,那么您将需要使用动态sql:
But if they are unknown, then you will need to use dynamic sql:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(50), date, 120))
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + ' from
(
select id, value, convert(varchar(50), date, 120) date
from yourtable
) x
pivot
(
max(value)
for date in (' + @cols + ')
) p '
execute(@query)
请参见带演示的SQL提琴.
这两个查询的结果是:
| ID | 2012-10-29 00:00:00 | 2012-12-29 00:00:00 | 2013-02-02 00:00:00 |
------------------------------------------------------------------------
| 1 | 54 | 55 | 89 |
| 2 | 54 | 45 | (null) |
| 4 | 90 | 78 | (null) |