one Rows in two Line in Result one line in one row

  • tutter

    SSC Enthusiast

    Points: 116

    Hello,

    i have often the Problem that i must the information from 2 line (1 row) i need in 1 line

    Example:

    Select car,model,engine,details from cars

    ( |= stands for row)

    Honda| Civic| 1.6| deluxe

    Honda| Civic| 1.6| basic

    VW| Golf| 1.5| basic

    VW| Golf| 1.5| extra

    Dodge| Viper| 8.4| performance

    Dodge| Viper| 8.4| last_edition

     

    Result: Row 4 but only 1 line

    Honda| Civic| 1.6| deluxe, basic

    VW| Golf| 1.5| basic, extra

    Dodge| Viper| 8.4| performance, last_edition

    Thanks for helping

  • Ken McKelvey

    SSCoach

    Points: 18297

    Using FOR XML PATH with .value is the normal approach for this. See just before 'Using Common Language Runtime' here:

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

  • Phil Parkin

    SSC Guru

    Points: 244602

    If you would like help creating a solution in code, please provide your data in consumable format (ie, one which can be cut & pasted into SSMS).

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 996676

    Since you're new, let me explain what Phil means by directing you to the first link in my signature line below.  It helps others help you both a lot better and a lot more quickly.

    Here's another way to provide such readily consumable data (I'm using what you posted as the example).

    --===== If the test table already exists, drop it to make reruns in SSMS easier.
    -- This is NOT a part of the solution to the provlem. This is how to post test data for the problem.
    DROP TABLE IF EXISTS #TestTable
    ;
    --===== Create and populate the test table on-the-fly.
    SELECT *
    INTO #TestTable
    FROM (VALUES
    ('Honda','Civic','1.6','deluxe')
    ,('Honda','Civic','1.6','basic')
    ,('VW','Golf','1.5','basic')
    ,('VW','Golf','1.5','extra')
    ,('Dodge','Viper','8.4','performance')
    ,('Dodge','Viper','8.4','last_edition')
    )v(car,model,engine,details)
    ;
    GO

    Once that's done and because you're using SQL Server 2017, we no longer need the XML trick to do this.  Instead, we can use the new STRING_AGG() function as follows:

     SELECT  car,model,engine
    ,details = STRING_AGG(details,', ' ) WITHIN GROUP (ORDER BY details)
    FROM #TestTable
    GROUP BY car,model,engine
    ORDER BY car,model,engine
    ;

    ... and that produces the following results...

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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