Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


pivot query


pivot query

Author
Message
axes2000
axes2000
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 34
hi all,
I have a table like below:
ID LOCATION STATUS MONTH MEASURE
1 PARIS OPEN JAN 25
2 PARIS CLOSED JAN 30
3 LONDON OPEN JAN 45
4 LONDON CLOSED JAN 50
5 PARIS OPEN FEB 27
6 PARIS CLOSED FEB 31
7 LONDON OPEN FEB 50
8 LONDON CLOSED FEB 61
How do I pull these numbers in a data set as seen below:
JAN 25 30 45 50
FEB 27 31 50 61
namely,
month| measure for LOC1,STATUS1| measure for LOC1,STATUS2| measure for LOC2,STATUS1|measure for LOC2,STATUS2
I believe I've done it before with a PIVOT clause but I am not sure if I have the PIVOT option at my disposal (not sure what version is the SQL Server in production).
Thanks,
kowalsky
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3005 Visits: 5016
This should give you a start

SELECT   MONTH,
SUM( CASE WHEN RN = 1 THEN MEASURE ELSE 0 END ) AS MEASURE1,
SUM( CASE WHEN RN = 2 THEN MEASURE ELSE 0 END ) AS MEASURE2,
SUM( CASE WHEN RN = 3 THEN MEASURE ELSE 0 END ) AS MEASURE3,
SUM( CASE WHEN RN = 4 THEN MEASURE ELSE 0 END ) AS MEASURE4
FROM (
SELECT ROW_NUMBER() OVER ( PARTITION BY MONTH ORDER BY LOCATION, STATUS DESC ) AS RN, *
FROM YourTableName -- Enter your table name here
) AS YT
GROUP BY MONTH



PIVOT clause is available in SQL Server 2005 and higher versions
You can find the your SQL Server Version by using below query

SELECT @@VERSION




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
The short answer is with difficulty

As kingston said PIVOT is available from SQL2005 onwards so there is no point if you are on 2000 w00t

The first challenge is that PIVOT requires the column headers to be defined as part of the query so you can't build them if you don't know what the end dataset looks like. E.g. If someone add records for 'MILAN' then you would need to change the query. The only solution where the column names are unknown is to fetch them first and then build a dynamic SQL statement and execute is using spExec which requires the correct permission levels, runs the risk of the dynamic sql being broken (e.g. a city name like O'Fallon in Illinois would introduce a rogue single quote which would break the sql unless you cleaned every entry)

There are loads of posts on using pivot with dynamic SQL.

Your second challenge will be that your months are text rather than numeric so you default data will be

APR
AUG
DEC
FEB
JAN
JUL
JUN
MAR
MAY
NOV
OCT
SEP

Unless you change the data to numeric or introduce temp table with the text values and the sort order.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8586 Visits: 18161
Take a look at this articles and return if you still have questions ;-)
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
axes2000
axes2000
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 34
Thanks a bunch,
it works fine!

kowalsky
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search