SQL Query - Pivot

Question

SQL Query - Pivot

Sathya NK

SSC Journeyman

Points: 87
More actions
February 26, 2010 at 11:15 am

#100109

Hi,
There are many useful posts here on SQL Server pivot but am unable to get my head around to fix this. Any help is greatly appreciated.
My result set is this
Id CodeHours
2415.001
2619.999
I want to show the data as
id Code1 Code1Hours code2 code2hours
2 4 15.0016 19.999.
Please note that codes cannot be hardcoded.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Did you already study the two "main articles" on CrossTab and DynamicCrossTab? If not, please see the related links in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sathya NK SSC Journeyman Points: 87 More actions · Answer 2

I did read those articles. In this case, I really dont care whether we use pivots or case statements. By reading the articles, I was able to consturct the query using pivot but the results are like this. Not exactly what I wanted.

2NULL6NULLNULL19.999NULL

24NULLNULL15.001NULLNULL

I am not sure how to supress the nulls and make it one row.

Thanks.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Would you mind providing a test scenario so we have sometihng to play with? (table def, some sample data and your current query)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 4

lmu92 (2/26/2010)
Would you mind providing a test scenario so we have sometihng to play with? (table def, some sample data and your current query)

I'll second that. I'm happy to have a go at producing a beautiful PIVOT for you, but I'm not guessing at the data, and writing CREATE TABLE and INSERT statements for sample data as well.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi