Pivot Question

  • Having some issues trying to figure out how to get the results that I want from the following set of data.

    DECLARE @demo

    TABLE (

    Region char(10),

    store_num INTEGER,

    store_descchar(10)

    );

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1376,'Store 1376');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1378,'Store 1378');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1379,'Store 1379');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1380,'Store 1380');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1383,'Store 1383');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1229,'Store 1229');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1373,'Store 1373');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 4',4817,'Store 4817');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3701,'Store 3701');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 4',4505,'Store 4505');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2461,'Store 2461');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2458,'Store 2458');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2453,'Store 2453');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2448,'Store 2448');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2454,'Store 2454');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2449,'Store 2449');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2651,'Store 2651');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2466,'Store 2466');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1128,'Store 1128');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3403,'Store 3403');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3404,'Store 3404');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2447,'Store 2447');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2411,'Store 2411');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2455,'Store 2455');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 4',4825,'Store 4825');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2402,'Store 2402');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2433,'Store 2433');

    INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3422,'Store 3422');

    select pvt.[Region 1],pvt.[Region 2],pvt.[Region 3],pvt.[Region 4]

    from @demo

    Pivot (

    min(store_desc)

    for region in ([region 1],[Region 2],[Region 3],[Region 4])) as pvt

    This returns something like this:

    Region 1Region 2Region 3Region 4

    Store 1128NULLNULLNULL

    Store 1229NULLNULLNULL

    Store 1373NULLNULLNULL

    Store 1376NULLNULLNULL

    Store 1378NULLNULLNULL

    Store 1379NULLNULLNULL

    Store 1380NULLNULLNULL

    Store 1383NULLNULLNULL

    NULLStore 2402NULLNULL

    NULLStore 2411NULLNULL

    NULLStore 2433NULLNULL

    NULLStore 2447NULLNULL

    NULLStore 2448NULLNULL

    NULLStore 2449NULLNULL

    NULLStore 2453NULLNULL

    NULLStore 2454NULLNULL

    NULLStore 2455NULLNULL

    NULLStore 2458NULLNULL

    NULLStore 2461NULLNULL

    NULLStore 2466NULLNULL

    NULLStore 2651NULLNULL

    NULLNULLStore 3403NULL

    NULLNULLStore 3404NULL

    NULLNULLStore 3422NULL

    NULLNULLStore 3701NULL

    NULLNULLNULLStore 4505

    NULLNULLNULLStore 4817

    NULLNULLNULLStore 4825

    I would like to return something more like this:

    Region 1Region 2Region 3Region 4

    Store 1128Store 2402Store 3403Store 4505

    Store 1229Store 2411Store 3404Store 4817

    Store 1373Store 2433Store 3422Store 4825

    Store 1376Store 2447Store 3701

    Store 1378Store 2448

    Store 1379Store 2449

    Store 1380Store 2453

    Store 1383Store 2454

    Store 2455

    Store 2458

    Store 2461

    Store 2466

    Store 2651

    Any thoughts?

  • Something like this? (The trick is to assign a number to each row per Region and use this new column to group the data, thus eliminating the null values).

    SELECT

    MAX(ISNULL(pvt.[Region 1],'')) AS [Region 1],

    MAX(ISNULL(pvt.[Region 2],'')) AS [Region 2],

    MAX(ISNULL(pvt.[Region 3],'')) AS [Region 3],

    MAX(ISNULL(pvt.[Region 4],'')) AS [Region 4]

    FROM

    (SELECT ROW_NUMBER() OVER(PARTITION BY Region ORDER BY store_num ) AS ROW,* FROM @demo

    )p

    PIVOT (

    MAX(store_desc)

    FOR region IN ([region 1],[Region 2],[Region 3],[Region 4])

    ) AS pvt

    GROUP BY ROW



    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]

  • That's exactly what I was looking for!

    Thank You.

  • Based on your description and ready to use sample data it was really easy to work on and to match your desired result.

    This is one of the very best formats for asking a question that I've seen for quite some time!!

    Excellent job on your side! Very well done!

    Oh, before I forget: Glad I could help. 😉



    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]

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

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