0

I've built a query which outputs several thousand rows like:

| Person | Date      |  Hours1   |  Hours2  |
  Steve    2016 04      18          42.0
  John     2016 04      21          32.09
  Pete     2016 04      9           78.5
  Steve    2016 03      10          10
  Dave     2016 03      5           50
  etc...   etc...       etc...      etc...

But I need:

|Person   | 2016 04 Hours1 | 2016 04 Hours2 | 2016 03 Hours1 | 2016 03 Hours2  | etc...
 Dave            NULL             NULL              5               50           etc...              
 John            21               32.09             NULL            NULL         etc...
 Pete            9                78.5              NULL            NULL         etc...
 Steve           18               42.0              10              10           etc...
 etc...          etc...           etc...            etc...          etc...       etc...

I've had a look at other questions etc. using transpose / pivoting but I'm not so sure that this is a simple use-case as there are a dynamic number of dates and people, but I don't really know where to start!

TimJ
  • 399
  • 4
  • 18

2 Answers2

3

Create a dynamic query and execute it.

Query

declare @sql as varchar(max);

select @sql = 'select Person,' + stuff((
  select distinct 
  ',max(case [Date] when ''' + [Date] + ''' then Hours1 end) as [' + [Date] + ' Hours1]' + 
  ',max(case [Date] when ''' + [Date] + ''' then Hours2 end) as [' + [Date] + ' Hours2]'
from Person
for xml path('')), 1, 1, '');

select @sql += ' from Person group by Person;';

exec(@sql);

Result

+--------+----------------+----------------+----------------+----------------+
| Person | 2016 03 Hours1 | 2016 03 Hours2 | 2016 04 Hours1 | 2016 04 Hours2 |
+--------+----------------+----------------+----------------+----------------+
| Dave   | 5              | 50             | NULL           | NULL           |
| John   | NULL           | NULL           | 21             | 32.09          |
| Pete   | NULL           | NULL           | 9              | 78.5           |
| Steve  | 10             | 10             | 18             | 42             |
+--------+----------------+----------------+----------------+----------------+
Ullas
  • 11,450
  • 4
  • 33
  • 50
  • Great solution; creating dyn sql with another sql. simple & straight forward. – SQL Police Apr 15 '16 at 12:57
  • This works perfectly for me, although I must admit I have no idea how! Looks like I need to find a good course on dynamic SQL. – TimJ Apr 18 '16 at 09:10
0

There is no generalized pivot functionality in SQL Server. You either need to create a dynamic SQL statement, or you need to handle the data in your client application.

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • Let's say I want to build dynamic SQL instead of relying on the front end, where would you start? – TimJ Apr 15 '16 at 12:31