Post reply

Create Flat Record From Multiple Results (TSQL)

  • August 7, 2014 at 5:11 am

    #310015

    Good Morning,

    Sorry I am quite new to TSQL Query and require a little help if possible please.

    I have obtained my results from a query from joining various tables and the results I have returned are what I expect. What I need help is returning the results so that where a single record (reference) that has multiple lines of records, is converted into a one line (flat file) of results e.g. the attached image:

    (Sorry for the bad table, not sure how to display it in a friendly format for this forum)

    USER_ID RECEIPT_ID ITEM_DES

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

    1 111 abc

    1 111 def

    1 111 ghi

    2 222 jkl

    2 222 mno

    3 333 pqr

    So that it then looks like this:

    USER_ID RECEIPT_ID ITEM_DES ITEM_DES1 ITEM_DES2

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

    1 111 abc def ghi

    2 222 jkl jkl NULL

    3 333 pqr NULL NULL

    (Image attached of the above in a nicer format)

    Is this possible? (sorry if this is a very simple query).

    Thanks in advance.

  • August 7, 2014 at 5:37 am

    #1735921

    TSQL_Newbie (8/7/2014)

    Good Morning,

    Sorry I am quite new to TSQL Query and require a little help if possible please.

    I have obtained my results from a query from joining various tables and the results I have returned are what I expect. What I need help is returning the results so that where a single record (reference) that has multiple lines of records, is converted into a one line (flat file) of results e.g. the attached image:

    (Sorry for the bad table, not sure how to display it in a friendly format for this forum)

    USER_ID RECEIPT_ID ITEM_DES

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

    1 111 abc

    1 111 def

    1 111 ghi

    2 222 jkl

    2 222 mno

    3 333 pqr

    So that it then looks like this:

    USER_ID RECEIPT_ID ITEM_DES ITEM_DES1 ITEM_DES2

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

    1 111 abc def ghi

    2 222 jkl jkl NULL

    3 333 pqr NULL NULL

    (Image attached of the above in a nicer format)

    Is this possible? (sorry if this is a very simple query).

    Thanks in advance.

    Do you know ahead of time what the maximum number of descriptions will be?

    --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)

  • August 7, 2014 at 6:05 am

    #1735932

    I would say no more than 15 (fingers crossed), I currently have a max of 10 so a little buffer should be good.

    Regards

  • August 7, 2014 at 10:18 am

    #1736078

    Have a look at Jeff's article:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

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

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