MSSQL transpose multiple rows into one row(s) with columns

Question

I need to transform, transpose, whatever the right terminology is a data set with multiple rows and instead return a single row with multiple columns. Here is a sample of the data that I start with

EID               PM             Project     HOURS    WeekStarting
joe@test.com      tom@test.com   Proj A       6         11/28/2016
joe@test.com      tom@test.com   Proj A       3         12/5/2016
joe@test.com      tom@test.com   Proj A       7         12/12/2016
joe@test.com      tom@test.com   Proj A       3         12/19/2016
sue@test.com      sam@test.com   Proj B       3         11/28/2016
sue@test.com      sam@test.com   Proj B       6         12/12/2016
sue@test.com      sam@test.com   Proj B       7         12/19/2016

I would like to format the data like so

EID            PM             Project    Week1  Week2 Week3  Week4 
joe@test.com   tom@test.com   Proj A       6     3      7      3
sue@test.com   sam@test.com   Proj B       3     0      6      7

Note that for sue there is no data in week2 in my source data so the result is 0 for week 2.

Currently I am doing this in Javascript using the reduce function but it's very messy. I would rather try to fix the data and have clean Javascript.

Any help is much appreciated.

Look at `PIVOT` and `UNPIVOT`: https://msdn.microsoft.com/en-us/library/ms177410.aspx — Dai, Nov 22 '16 at 19:50
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — S3S, Nov 22 '16 at 19:56
In addition to that link, you will just need the `DATEPART` function. https://msdn.microsoft.com/en-us/library/ms174420.aspx — S3S, Nov 22 '16 at 19:57
I should have also stated that I am performing this in a restricted environment and I am unable to use variables or any dynamic SQL. — Jeremy Ragsdale, Nov 28 '16 at 15:31

score 0 · Answer 1 · answered Nov 22 '16 at 20:32

Declare @SQL varchar(max)
Select  @SQL = Stuff((Select ','+QuoteName('Week'+cast(WeekNr as varchar(25)))
                       From  (Select Distinct Top 100 Percent WeekNr=Dense_Rank() over (Order By WeekStarting) From YourTable 
                       Order By 1) A
                       For XML Path ('')),1,1,'')

Select  @SQL = 'Select EID,PM,Project,' + @SQL + ' 
                From (
                        Select EID
                              ,PM
                              ,Project
                              ,Item = ''Week''+cast(Dense_Rank() over (Order By WeekStarting) as varchar(25))
                              ,Val  = Hours
                         From  YourTable
                     ) A
                Pivot (max(Val) For Item in (' + @SQL + ') ) p'

Exec(@SQL);

Returns

Kannan Kandasamy · Answer 2 · 2016-11-22T20:46:04.973

0

You can query like this:

;with cte as (
select *, DATEPART(WEEK, weekstarting)  -
    DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,weekstarting), 0)) + 1 AS WEEK_OF_MONTH
from yourtranspose
)
select Eid, PM, Project, [1], [2], [3], [4], [5] from
( select EID, PM, Project, WEEK_OF_MONTH , hours from cte ) a
pivot (max(hours) for week_of_month in ([1], [2], [3], [4], [5])) p

edited Nov 22 '16 at 20:46

answered Nov 22 '16 at 20:37

Kannan Kandasamy

13,405
3
25
38

score -2 · Answer 3 · answered Nov 22 '16 at 19:49

-2

for each extra row you need to join the table against itself

answered Nov 22 '16 at 19:49

edlerd

2,145
1
16
24

MSSQL transpose multiple rows into one row(s) with columns

3 Answers3