Post reply

Concat mutiple rows into one row

  • November 13, 2008 at 7:48 pm

    #383382

    Hi,

    Can you please assist me regarding my problem, heres the scenario,I want to summarize the list from Van No. rows ,below is the data,

    Date : Van No.

    1/1/2008 21

    1/2/2008 22

    1/3/2008 23

    1/4/2008 24

    1/5/2008 25

    1/6/2008 26

    How can I query this that I can make this result as :

    Date : Van No. Summary Van No.

    1/1/2008 21 21,22,23,24,25,26

    1/2/2008 22 21,22,23,24,25,26

    1/3/2008 23 21,22,23,24,25,26

    1/4/2008 24 21,22,23,24,25,26

    1/5/2008 25 21,22,23,24,25,26

    1/6/2008 26 21,22,23,24,25,26

    Thank you in advance.

    Regards,

    Clint

  • November 13, 2008 at 11:38 pm

    #898414

    See the following article for how to do the CSV concatenation along with some pitfalls to avoid...

    http://qa.sqlservercentral.com/articles/Test+Data/61572/

    To help us give you a better/tested answer, take a look at the link in my signature.

    --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)
    Intro to Tally Tables and Functions

  • November 14, 2008 at 12:06 am

    #898421

    hi Jeff,

    thank you very much for the link!

    Regards,

    Clint

  • November 14, 2008 at 12:26 am

    #898428

    Hi Jeff,

    For clarification, I have read you link, correct me If im wrong your using fnConcatTest to concat the query, Is there any way that we can concat this without the function since I cant create a function in my database cause im using ang SAP program and its prohibited to alter any database that the program involves.

    Thank you,

    Regards,

    Clint

  • November 14, 2008 at 11:18 am

    #898773

    Sure... you want all the rows of Summary Van No. to be the same?

    Whichever, can you make it easy on me by providing the CREATE TABLE statement and some code to populate it like I mentioned in the "Etiquette" article? Thanks.

    --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)
    Intro to Tally Tables and Functions

  • November 14, 2008 at 4:30 pm

    #898926

    Hi Jeff,

    Im sorry for that Jeff, Im just want to inform you first thats why im making sure before creating tables for sample. Thank you very much,

    Regards,

    Clint

  • November 14, 2008 at 4:57 pm

    #898932

    Got it... thanks, Clint.

    --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)
    Intro to Tally Tables and Functions

  • November 14, 2008 at 9:02 pm

    #898955

    hi jeff,

    This is the details,

    CREATE TABLE [Query_Concat] (

    [DocDate] DATETIME,

    [Shipping Line] NVARCHAR(30),

    [U_soBLNo] NVARCHAR(20)

    )

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'13')

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Sulpicio Lines Inc.', N'9')

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'4')

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'9')

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'35')

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'8')

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'7')

    GO

    Thank you very much in advance.

    Regards,

    Clint

  • November 17, 2008 at 7:46 am

    #899336

    Here's an example. Jeff may have some fine tuning to suggest for this.

    [font="Courier New"]

    ------------- CTE to concatenate U_soBLNo -----------------

    ;WITH ConcatB(DocDate, [Shipping Line],CB) AS(

    SELECT QC.DocDate,[Shipping Line],STUFF((SELECT

            ', ' + U_soBLNo

        FROM

            [Query_Concat] QC2

        WHERE QC.DocDate = QC2.DocDate AND QC.[Shipping Line] = QC2.[Shipping Line]

        FOR XML PATH ('')), 1,2,'')

        FROM [Query_Concat] QC

        GROUP BY DocDate, [Shipping Line])

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

    ------------ SELECT ---------------------------------------

    SELECT DISTINCT QC.DocDate, QC.[Shipping Line],CB

    FROM [Query_Concat] QC

       INNER JOIN ConcatB QC2 ON QC.DocDate = QC2.DocDate AND QC.[Shipping Line] = QC2.[Shipping Line]

    -----------------------------------------------------------[/font]

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • November 17, 2008 at 4:07 pm

    #899607

    Hi there,

    thank you very much for your help, I guess its complicated on my side due to very newbie of me, I have only learn basic SQL but again I relay appreciated it.

    Regards,

    Clint

  • November 17, 2008 at 5:22 pm

    #899633

    Clint,

    You still haven't answered my question... do you want all the rows of Summary Van No. to be the same?

    --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)
    Intro to Tally Tables and Functions

  • November 17, 2008 at 5:32 pm

    #899641

    Jeff,

    Yes Jeff please, sorry I haven't answered the question.

    regards,

    Clint

  • November 17, 2008 at 7:42 pm

    #899655

    Then, a simple mod to Seth's good code should do...

    --===== All of this is a duplicate of the test code you provided

    -- with an extra date and a couple of the lines commented out

    -- to prove that all U_soBLNo numbers will be returned.

    CREATE TABLE [Query_Concat] (

    [DocDate] DATETIME,

    [Shipping Line] NVARCHAR(30),

    [U_soBLNo] NVARCHAR(20)

    )

    GO

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Shipping Lines Inc.', N'13')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Sulpicio Lines Inc.', N'9')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Shipping Lines Inc.', N'4')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Shipping Lines Inc.', N'9')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Shipping Lines Inc.', N'35')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Shipping Lines Inc.', N'8')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080103', N'Shipping Lines Inc.', N'7')

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

    --INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    --VALUES ('20080104', N'Shipping Lines Inc.', N'13')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Sulpicio Lines Inc.', N'9')

    --INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    --VALUES ('20080104', N'Shipping Lines Inc.', N'4')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'9')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'35')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'8')

    INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

    VALUES ('20080104', N'Shipping Lines Inc.', N'7')

    --===== Code to return ALL U_soBLNo's for a given line regardless of date

    SELECT qc.DocDate,

    qc.[Shipping Line],

    STUFF((SELECT ', ' + U_soBLNo

    FROM (SELECT DISTINCT [Shipping Line], U_soBLNo FROM dbo.Query_Concat) qc2

    WHERE qc.[Shipping Line] = qc2.[Shipping Line]

    ORDER BY CAST(qc2.U_soBLNo AS INT)

    FOR XML PATH ('')), 1,2,'') AS U_soBLNo

    FROM dbo.Query_Concat qc

    GROUP BY qc.DocDate, qc.[Shipping Line]

    --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)
    Intro to Tally Tables and Functions

  • November 17, 2008 at 7:51 pm

    #899656

    Hi Jeff,

    Thank you very much for the help I really appreciated though I need to study the code for understanding.

    Regards,

    Climnt

  • November 17, 2008 at 7:51 pm

    #899657

    Hi Jeff,

    Thank you very much for the help I really appreciated though I need to study the code for understanding.

    Regards,

    Clint

Viewing 15 posts - 1 through 14 (of 14 total)

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