SQL Pivot Query

Question

Post reply

SQL Pivot Query

anandrajuk

Grasshopper

Points: 20
More actions
December 22, 2009 at 8:14 pm

#217847

Hi Guys,
Need help with a pivot query.
My query after joining couple of tables is like this
IDNAME PLACEDATE
001ASydney1-Jun-2009
001AMacau2-Jun-2009
001ATexas3-Jun-2009
I want it to be displayed like this
IDNAMESYDNEY MACAU TEXAS
001A1-Jun-20092-Jun-20093-Jun-2009
Please help!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

select [ID],[NAME],

max(case when [PLACE] = 'Sydney'then [DATE] else '' end)as [Sydney],

max(case when [PLACE] = 'Macau'then [DATE] else '' end)as [Macau],

max(case when [PLACE] = 'Texas'then [DATE] else '' end)as [Texas]

from MYTABLE

group by [ID],[NAME]

anandrajuk Grasshopper Points: 20 More actions · Answer 2

Thanks Arun, I needed to make the column data dynamic instead of specifying the name in the 'case when' clause.

Is it possible..

Thanks

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi,

Yes, it’s possible and see the Jeff article “Cross Tabs and Pivots, Part2 – Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 4

use the PIVOT T-SQL

http://www.tsqltutorials.com/pivot.php

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

oliver.morris (12/23/2009)
use the PIVOT T-SQL
http://www.tsqltutorials.com/pivot.php

Can you demonstrate how to use the PIVOT T-SQL with an unknown i.e. dynamic column set?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 6

brief thought could you use a view to pull in the dynamic data and run the pivot t-sql on the view - you would need to now what the data types are before hand.

I am sure a stored procedure would be able to do this but I have no experience with this sorry.

LutzM SSC Guru Points: 107049 More actions · Answer 7

oliver.morris (12/23/2009)
brief thought could you use a view to pull in the dynamic data and run the pivot t-sql on the view - you would need to now what the data types are before hand.
I am sure a stored procedure would be able to do this but I have no experience with this sorry.

What would be the reason to build a dynamic view just to be able to use PIVOT?

Even the new view would include an unknown number of columns. The view definition basically is nothing more than a saved SQL query.

What would be a reason not to use the Dynamic Cross Tab Chris mentioned?

Please take the time and read the article, compare it to the solution you'd use, post it and we'd be more than willing to discuss pros and cons of both versions.

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]