Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: Concatenate Column Value from Multiple Rows into a Single Column

    Hello,

    I need to concatenate a column from multiple rows into a single column in a new table.

    How can I do this?

    SID NAME PGROUP
    ------------------------------------------------------------
    34676 02 CLOTHING ACTIVE
    34676 02 CLOTHING DANCE
    34676 02 CLOTHING LWR


    Need to have

    SID NAME PGROUP
    ------------------------------------------------------------
    34676 02 CLOTHING ACTIVE , DANCE, LWR


    THANK YOU

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I usually do that in the display layer.

  3. #3
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Display layer is the best option. If you really wanna do this in SQL, here's one way. (I'm sure there's a better way, but this is the first one that comes to mind.)
    Code:
     
    -- Create the temp table...
    CREATE TABLE [#CastTest] (
     [Col1] nvarchar(10) NOT NULL,
     [Col2] nvarchar(10) NOT NULL,
     [Col3] nvarchar(10) NOT NULL
     )
    
    --Insert sample data...
    INSERT INTO [#CastTest]([Col1],[Col2],[Col3])
    SELECT 'One','Two','Three' UNION ALL
    SELECT 'Tom','Dick','Harry' UNION ALL
    SELECT 'Ad Rock','MCA','Mike D'
    
    --Output, with CAST, comma separated.
    SELECT Col1, Col2, Col3, 
    CAST(Col1 + ', ' + Col2 + ', ' + Col3 AS nvarchar(35)) AS Col4
    FROM [#CastTest]
    
    --Clean up.
    DROP TABLE [#CastTest]
    -D.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a user-defined function based on this algorithm: http://sqlblindman.googlepages.com/c...limitedstrings
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    in 2005 you can use FOR XML PATH to do this as well. kinda cute.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by blindman
    Create a user-defined function based on this algorithm: http://sqlblindman.googlepages.com/c...limitedstrings
    Nice Trick!

  7. #7
    Join Date
    May 2003
    Location
    Sydney AU
    Posts
    15

    Cool

    Quote Originally Posted by blindman
    Create a user-defined function based on this algorithm: http://sqlblindman.googlepages.com/c...limitedstrings
    Nice one, thanks.

  8. #8
    Join Date
    Jun 2011
    Location
    Noida,kanpur (India)
    Posts
    2

    Thumbs down Solution

    Quote Originally Posted by ypal View Post
    Hello,

    I need to concatenate a column from multiple rows into a single column in a new table.

    How can I do this?

    SID NAME PGROUP
    ------------------------------------------------------------
    34676 02 CLOTHING ACTIVE
    34676 02 CLOTHING DANCE
    34676 02 CLOTHING LWR


    Need to have

    SID NAME PGROUP
    ------------------------------------------------------------
    34676 02 CLOTHING ACTIVE , DANCE, LWR


    THANK YOU
    Solution::
    SELECT
    [SID],NAME ,REPLACE(RTRIM((SELECT [PGROUP] + ' ' FROM [AMRMIS_COPY_PKS].[dbo].[T1] WHERE (SID = Results.SID ) FOR XML PATH (''))),' ',', ') AS NameValues
    FROM [AMRMIS_COPY_PKS].[dbo].[T1] Results
    GROUP BY SID,NAME

  9. #9
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Both options presented here work, and I've used them quite a lot myself. Still, I'll fire off a warning: Please do evaluate the need for concatenating strings this way. Both function calls and (especially) XML functionality are pretty expensive, and if used carelessly it can add a serious amount of load on your SQL Server.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  10. #10
    Join Date
    Jun 2011
    Posts
    6

    This is the solution od your problem

    CREATE TABLE #test
    (
    id INT,
    categoryid INT,
    category VARCHAR(50),
    Status VARCHAR(50)
    )

    INSERT INTO #test(id,categoryid,category,[Status])
    SELECT 34676 ,02 ,'CLOTHING' ,'ACTIVE'
    UNION
    SELECT 34676 ,02 ,'CLOTHING' ,'DANCE'
    UNION
    SELECT 34676 ,02 ,'CLOTHING' ,'LWR'


    SELECT DISTINCT id,categoryid,category ,
    (
    SELECT ',' + status as 'data()'
    FROM #test
    FOR XML PATH('')

    )
    FROM #test

    DROP TABLE #test

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Great. Now that is the THIRD time the same solution has been suggested for a three year old thread.
    Let's read prior posts before replying, people.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •