如何在 SQL Server 中获取列数据? [英] How to get column wise data in SQL Server?

问题描述

如何在 SQL Server 中获取列数据?

How to get column wise data in 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

推荐答案

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

You can use this code for example which will pivot the data:

在 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 上:

On 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

这将是一个动态枢轴，如果您的日期列表增长，它将进行调整:

This will be a dynamic pivot which will adapt if your date list will grow:

-- 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

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