选择行到列SQL

小编典典

选择行到列SQL

sql

我以前没有碰到行对列，您能帮我吗？

这是我的资料来源。

我想从以下选项中进行选择：

Id  Project ID  Date      Hour
-------------------------------
1   Project-1   1/1/2010    10
2   Project-1   1/2/2010     2
3   Project-1   1/3/2010     3
4   Project-1   1/4/2010     5
5   Project-2   1/1/2010     3
6   Project-2   1/2/2010     4
7   Project-2   1/3/2010     2
8   Project-2   1/4/2010     7
9   Project-3   1/1/2010     5
10  Project-3   1/2/2010     6
11  Project-3   1/3/2010     4
.
.
.

到

Project ID  1/1/2010    1/2/2010    1/3/2010    1/4/2010    ...
----------------------------------------------------------------
Project-1     10          2            3           5
Project-2     3           4            2           7
Project-3     5           6            4

请帮我。

更新：

这是我的解决方案。

--==============  Create stored procedure ============
if exists(select * from sys.procedures where name='usp_ProjectFollow')
    drop procedure usp_ProjectFollow
go

create proc usp_ProjectFollow 
as
begin
    DECLARE @cols AS NVARCHAR(MAX), 
            @query  AS NVARCHAR(MAX)
    Declare @ParamDefinition AS NVarchar(MAX) 
    SELECT @cols = STUFF((SELECT ',' + QUOTENAME([Date]) 
    FROM MyTable
    group by [Date]
    order by [Date]
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

    set @query = 'SELECT id, ProjectName, ProjectCode, ' + @cols +  
            ' FROM MyTable
            pivot 
            (
                sum(Hour)
                for Date in (' + @cols + ')
            ) p '
            --print @query
     execute sp_Executesql @query


end
go

希望它能帮助需要的人。

阅读 158

2021-04-22

共1个答案

小编典典

静态解决方案正在使用这种类型的查询：

SELECT
    ProjectID,
    SUM(CASE WHEN Date = '1/1/2010' THEN Hour ELSE 0 END) As '1/1/2010',
    SUM(CASE WHEN Date = '1/2/2010' THEN Hour ELSE 0 END) As '1/2/2010',
    SUM(CASE WHEN Date = '1/3/2010' THEN Hour ELSE 0 END) As '1/3/2010',
    SUM(CASE WHEN Date = '1/4/2010' THEN Hour ELSE 0 END) As '1/4/2010',
    ...
FROM
    yourTable
GROUP BY
    ProjectID;

为了使用动态解决方案，您需要使用动态SQL，如下所示：

Declare @SQL nvarchar(MAX)

SELECT @SQL = ISNULL(@SQL, 'SELECT ProjectID') + ', SUM(CASE WHEN Date = ''' + [Date] + ''' THEN [Hour] ELSE 0 END) As [' + [Date] + ']'
FROM @t
GROUP BY [Date]

SELECT @SQL = @SQL + ' FROM yourTable GROUP BY ProjectID;'

EXEC(@SQL)

2021-04-22