Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pivot query Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 7:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 11:30 AM
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
Post #1464930
Posted Wednesday, June 19, 2013 1:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1464973
Posted Wednesday, June 19, 2013 6:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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

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.

Post #1465128
Posted Wednesday, June 19, 2013 8:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 3,342, Visits: 7,225
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1465224
Posted Wednesday, June 19, 2013 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 11:30 AM
Points: 17, Visits: 34
Thanks a bunch,
it works fine!

kowalsky
Post #1465272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse