Post reply

pivot query

  • June 18, 2013 at 7:27 pm

    #278821

    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

  • June 19, 2013 at 1:22 am

    #1625300

    This should give you a start

    SELECTMONTH,

    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(

    SELECTROW_NUMBER() OVER ( PARTITION BY MONTH ORDER BY LOCATION, STATUS DESC ) AS RN, *

    FROMYourTableName -- 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/

  • June 19, 2013 at 6:28 am

    #1625436

    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.

  • June 19, 2013 at 8:46 am

    #1625519

    Take a look at this articles and return if you still have questions 😉

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

    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

  • June 19, 2013 at 9:59 am

    #1625559

    Thanks a bunch,

    it works fine!

    kowalsky

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

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