DangKhoa DangKhoa - 1 month ago 13
SQL Question

Select row to column SQL

I didn't meet row to column before, can you help me?

This is example of my source.

And I want to select from:

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


to

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


Please help me.

UPDATE:

This is my solution.

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


hope it's will help someone need.

Answer

A static solution is using this type of query:

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;

For using a dynamic solution you need to use dynamic SQL like this:

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