Post reply

Coalesce in query

  • August 19, 2014 at 3:27 pm

    #289933

    Hi everyone,

    I need help, i want to obtain two columns in a query but i don´t know how to.

    ex.

    col1 col2

    1 A

    1 B

    2 A

    1 C

    I need something like this.

    col1 col2

    1 A, B, C

    2 A

    Col1 = Table Z

    Col2 = Table Y

    I hope somebody can help me.

    thaks.

  • August 19, 2014 at 5:10 pm

    #1739428

    The comments in the code will prove helpful...

    --===== Create and populate a test table on the fly.

    -- This is NOT a part of the solution. It's just test data.

    SELECT d.Col1, d.Col2

    INTO #TestTable

    FROM (

    SELECT 1,'A' UNION ALL

    SELECT 1,'B' UNION ALL

    SELECT 2,'A' UNION ALL

    SELECT 1,'C'

    ) d (Col1, Col2)

    ;

    --===== Solve the problem. See the following URL for how it works with a extra speed kicker.

    -- http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    -- This uses XML to do the concatenation.

    SELECT t1.Col1,

    STUFF( --STUFF is used to get rid of the leading comma and space produced here.

    (SELECT ', ' + t2.Col2

    FROM #TestTable t2

    WHERE t1.Col1 = t2.Col1

    ORDER BY t2.Col2

    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')

    ,1,2,'') --The operands of the "STUFF" function

    FROM #TestTable t1

    GROUP BY t1.Col1

    ORDER BY Col1

    ;

    --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 19, 2014 at 9:24 pm

    #1739457

    CELKO (8/19/2014)

    Please read any book on RDBMS, so you will learn what a normal form is and why a good programer will never do anything like this in RDBMS.

    Not quite right. I agree that it would be a mortal sin to store such data but it's no worse than creating XML. Folks use this kind of thing to create output for spreadsheets and other things when there's no front-end available.

    --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 20, 2014 at 1:13 pm

    #1739699

    I appreciate your all your answers, I've already solved the problem, just to clarify something, I do not have the data stored in this way, it is the result of a query to the database.

    thanks anyway

  • August 20, 2014 at 1:37 pm

    #1739704

    DIAL (8/20/2014)

    I appreciate your all your answers, I've already solved the problem, just to clarify something, I do not have the data stored in this way, it is the result of a query to the database.

    thanks anyway

    Thanks for the feedback. To complete the circle, what method did you end up using? Any chance of seeing the code?

    --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 21, 2014 at 4:03 pm

    #1740046

    To show the user the information as requested in the front end one pivot table so presented as requested is generated, clarified that it is with programming code. Turned out to be much simpler and we take the problem off.

    Thanks.

  • August 28, 2014 at 3:29 pm

    #1741753

    the solution was not efficient, for that reason, I put the script here with the solution, thanks for your help and I hope this works for someone in the future.

    SELECT ID, Material, Stuff(

    (SELECT ', ' + T2.USO

    FROM (SELECT DFI_IdMaterial ID, USO_NombreUso USO

    FROM FichaDetalle

    INNER JOIN MaterialUsos ON DFI_IdUsoMaterial = USO_IdUsoMaterial

    WHERE DFI_IdFicha = 28) As T2

    WHERE T2.ID = T1.ID

    ORDER BY 1

    For Xml Path(''), type

    ).value('.', 'nvarchar(max)'), 1, 2, '') Uso

    FROM (SELECT DFI_IdMaterial ID, MAT_NombreMaterial Material

    FROM FichaDetalle

    INNER JOIN FichaTecnicaImplementaDetalle ON FTM_IdMaterial = DFI_IdMaterial

    INNER JOIN MaterialUsos ON DFI_IdUsoMaterial = USO_IdUsoMaterial

    INNER JOIN Material ON MAT_ID = DFI_IdMaterial

    WHERE DFI_IdFicha = 28) As T1

    GROUP BY T1.Id, Material

Viewing 7 posts - 1 through 6 (of 6 total)

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