选择行对列SQL。

Question

我以前没见过一排排的人，你能帮我吗？

这是我的资料来源的例子。

我想从中选择：

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

希望它能帮助需要帮助的人。

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