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.