如何在SQL Server中获取列明智的数据？

例如，您可以使用以下代码来透视数据：

在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 date5FROM (    SELECt @row_number:=@row_number+1 AS row_number, name, date     FROM yourTable, (SELECt @row_number:=0) AS t    ORDER BY date     ) as dataGROUP BY data.name;

在SQL Server上：

-- Generate demo dataCREATE 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 partSELECT pvt.*FROM (    SELECt ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rn, name, date    FROM #yourTable) as dataPIVOT(    MIN(date)    FOR rn IN([1],[2],[3],[4],[5],[6])) as pvt-- cleanupDROp TABLE #yourTable

如果您的日期列表会增加，这将是一个动态的枢轴，它将适应：

-- Generate demo dataCREATE 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 #yourTableWHERe name = (    SELECt TOP (1) name    FROM #yourTable    GROUP BY name    ORDER BY COUNT(*) DESC)SELECt @columnlist-- this is your partSET @sql = N'SELECT pvt.*FROM (    SELECt ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rn, name, date    FROM #yourTable) as dataPIVOT(    MIN(date)    FOR rn IN('+@columnlist+')) as pvt'EXEC(@sql)-- cleanupDROP TABLE #yourTable