Post reply

Need help with query

  • September 16, 2009 at 4:21 pm

    #99871

    I have a table that has data in the format:

    col1col2

    1 A

    1B

    1C

    2B

    2D

    3G

    4F

    4T

    I need help in writing a query that returns following results:

    col1 col2

    1 A,B,C

    2 B,D

    3 G

    4 F,T

    Here's the script to create and populate the test table:

    CREATE TABLE tab_TEST (col1 int, col2 varchar(10))

    INSERT INTO tab_test

    SELECT 1,'A'

    UNION

    SELECT 1,'B'

    UNION

    SELECT 1,'C'

    UNION

    SELECT 2, 'B'

    UNION

    SELECT 2,'D'

    UNION

    SELECT 3, 'G'

    UNION

    SELECT 4,'F'

    UNION

    SELECT 4,'T'

    Thanks!

  • September 16, 2009 at 4:44 pm

    #1054750

    Please see http://www.sqlservercentral.com/Forums/FindPost679589.aspx.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 16, 2009 at 5:12 pm

    #1054757

    Thanks Lutz!

  • September 17, 2009 at 12:10 am

    #1054811

    Another valuable resource is

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

  • September 18, 2009 at 11:23 am

    #1055759

    Could you use the PIVOT operator? Once I ran your SQL to create the table, I used the code below to achieve the expected results.

    Select COL1,

    CASE WHEN [A] = 1 Then 'A,' ELSE '' End +

    CASE WHEN = 1 Then 'B,' ELSE '' End +

    CASE WHEN [C] = 1 Then 'C,' ELSE '' End +

    CASE WHEN [D] = 1 Then 'D,' ELSE '' End +

    CASE WHEN [F] = 1 Then 'F,' ELSE '' End +

    CASE WHEN [G] = 1 Then 'G,' ELSE '' End +

    CASE WHEN [T] = 1 Then 'T,' ELSE '' End AS COL2

    FROM

    (

    Select COL1, COL2

    FROM tab_Test

    ) As Table1

    PIVOT

    (

    Count(COL2) FOR COL2 IN ([A],,[C],[D],[F],[G],[T])

    ) AS PVT

  • September 18, 2009 at 12:00 pm

    #1055776

    Gkerscher, I can use the pivot operator too but the problem is that my col2 values are not limited to [A],,[C],[D],[F],[G],[T]. I already have around 500 different values in there and more values can be added anytime.

    The values I provided were only an example.

  • September 18, 2009 at 12:49 pm

    #1055822

    KB – 2000, The only way I have found to have a “Dynamic” pivot list is to use a single variable in place of [A],,[C],[D],[F],[G],[T] and then use Exec(@SQL) to execute the select statement, but after reviewing the link posted by dmoldovan, that may be the way to go.

  • September 19, 2009 at 10:49 am

    #1055967

    Solution using XML to concatenate strings

    ;With T1 as

    (

    Select

    Col1,

    Col2=Max(cast(b.x as varchar(max)))

    From tab_Test a

    Cross apply(select x=(select col2+',' from tab_Test b where a.col1=b.col1 for XML Path(''),Type)) b

    group by col1

    )

    Select

    Col1,col2=left(col2,len(col2)-1) from T1

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

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