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!

Improve this question
1
  • create a dynamic sql query and execute Commented Apr 15, 2016 at 12:15

2 Answers 2

Reset to default
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             |
+--------+----------------+----------------+----------------+----------------+
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Great solution; creating dyn sql with another sql. simple & straight forward.
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.
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.

Improve this answer

1 Comment

Let's say I want to build dynamic SQL instead of relying on the front end, where would you start?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.