如何在SQL Server中获取列明智的数据?
格式:
Name Date
---- -----
xxx 10/15/2015
xxx 12/15/2015
xxx 15/15/2015
yyy 20/15/2015
yyy 25/15/2015
所需的输出:
Name Date Date Date
--------------------------------------------
xxx 10/15/2015 12/15/2015 15/15/2015
yyy 20/15/2015 25/15/2015
例如,您可以使用以下代码来透视数据:
在MySQL上:
SELECT data.name,
if(data.row_number=1,date,null) as date1,
if(data.row_number=2,date,null) as date2,
if(data.row_number=3,date,null) as date3,
if(data.row_number=4,date,null) as date4,
if(data.row_number=5,date,null) as date5
FROM (
SELECT @row_number:=@row_number+1 AS row_number, name, date
FROM yourTable, (SELECT @row_number:=0) AS t
ORDER BY date
) as data
GROUP BY data.name;
在SQL Server上:
-- Generate demo data
CREATE TABLE #yourTable(name nvarchar(20), date date)
INSERT INTO #yourTable(name,date)
VALUES(N'xxx',GETDATE()), (N'xxx', DATEADD(day,-1,GETDATE())), (N'yyy',GETDATE()), (N'yyy', DATEADD(day,1,GETDATE()))
-- this is your part
SELECT pvt.*
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rn, name, date
FROM #yourTable
) as data
PIVOT(
MIN(date)
FOR rn IN([1],[2],[3],[4],[5],[6])
) as pvt
-- cleanup
DROP TABLE #yourTable
如果您的日期列表会增加,这将是一个动态的枢轴,它将适应:
-- Generate demo data
CREATE TABLE #yourTable(name nvarchar(20), date date)
INSERT INTO #yourTable(name,date)
VALUES(N'xxx',GETDATE()), (N'xxx',DATEADD(day,1,GETDATE())), (N'xxx', DATEADD(day,-1,GETDATE())), (N'yyy',GETDATE()), (N'yyy', DATEADD(day,1,GETDATE()))
DECLARE @sql nvarchar(max), @columnlist nvarchar(max)
SELECT @columnlist =
COALESCE(@columnlist + N',['+CONVERT(nvarchar(max),ROW_NUMBER() OVER(ORDER BY date))+']',
N'['+CONVERT(nvarchar(max),ROW_NUMBER() OVER(ORDER BY date))+']'
)
FROM #yourTable
WHERE name = (
SELECT TOP (1) name
FROM #yourTable
GROUP BY name
ORDER BY COUNT(*) DESC
)
SELECT @columnlist
-- this is your part
SET @sql = N'
SELECT pvt.*
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rn, name, date
FROM #yourTable
) as data
PIVOT(
MIN(date)
FOR rn IN('+@columnlist+')
) as pvt'
EXEC(@sql)
-- cleanup
DROP TABLE #yourTable
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句