Post reply

Query help needed - Like a Pivot with generic headings?

  • April 13, 2013 at 1:45 pm

    #276453

    CREATE TABLE [dbo].[mike_inventory](

    [itemid] [int] NULL,

    [location] [varchar](50) NULL

    insert into mike_inventory values (10131,'Row A')

    insert into mike_inventory values (10131,'Row B')

    insert into mike_inventory values (10132,'Row B')

    insert into mike_inventory values (10132,'Row C')

    insert into mike_inventory values (10133,'Row A')

    insert into mike_inventory values (10133,'Row D')

    insert into mike_inventory values (10134,'Row A')

    insert into mike_inventory values (10134,'Row E')

    I want my output to look like this

    Itemid Location1 Location2 Location3

    --------------------------------------------------------------------------------------------

    10131 ROW A ROW B

    10132 ROW B ROW C

    10133 ROW A ROW D

    10134 ROW A ROW E

    Is this possible?

  • April 13, 2013 at 2:47 pm

    #1606263

    Maybe something along these lines will help

    ;WITH cte AS

    (

    SELECT itemid ,

    location ,

    ROW_NUMBER()OVER( PARTITION BY itemid ORDER BY itemid) RN

    FROM mike_inventory

    )

    SELECT itemid,

    MAX(CASE WHEN rn = 1 THEN LOCATION ELSE NULL END) AS LOC1,

    MAX(CASE WHEN RN = 2 THEN LOCATION ELSE NULL END) AS LOC2

    FROM CTE

    GROUP BY itemid

    ORDER BY itemid

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • April 13, 2013 at 10:20 pm

    #1606285

    To see how the query that JLS wrote works, please see the following article.

    http://www.sqlservercentral.com/Forums/Topic935472-392-1.aspx

    To learn how to do the same in a dynamic condition where the column names aren't always known at design time, please see the following article.

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 15, 2013 at 10:52 am

    #1606548

    Thanks JLS , thats exactly what i wanted.

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

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