Post reply

Group by column text in one row

  • July 12, 2010 at 7:53 am

    #223755

    create table test

    (

    ID int ,

    textID int ,

    Typetext varchar(100)

    )

    insert into test values (1,1,'Agree')

    insert into test values (2,1,'DisAgree')

    insert into test values (3,2,'Agree')

    insert into test values (4,2,'DisAgree')

    insert into test values (5,2,'NoOpenion')

    insert into test values (6,3,'Agree')

    insert into test values (7,3,'DisAgree')

    insert into test values (8,3,'Maybe')

    I want the something like below output

    [textID] [TypeText]

    1 Agree-DisAgree

    2 Agree-DisAgree-NoOpenion

    3 Agree-DisAgree-Maybe

  • July 12, 2010 at 9:46 am

    #1191550

    With a (textID,TypeText) as

    (Select t.textid,T2.Typetext from test t left join test t2 on t.ID=t2.ID where t2.typetext='Agree'),

    b (textID,TypeText) as (Select t.textid,T2.Typetext from test t left join test t2 on t.ID=t2.ID where t2.typetext='DisAgree'),

    c (textID,TypeText) as (Select t.textid,T2.Typetext from test t left join test t2 on t.ID=t2.ID where t2.typetext='NoOpenion'),

    d (textID,TypeText)as (Select t.textid,T2.Typetext from test t left join test t2 on t.ID=t2.ID where t2.typetext='Maybe')

    Select a.textid,ISNULL(a.typetext,'')+'-'+ISNULL(b.typetext,'')+'-'+ISNULL(c.typetext,'')+'-'+ISNULL(d.typetext,'') from a left join b on a.textid=b.textId

    left join c on a.textid=c.textid left join d on d.textid=a.textid

  • July 12, 2010 at 2:47 pm

    #1191709

    This approach might perform better:

    SELECT

    textID,

    STUFF((SELECT '-' + Typetext FROM test t2 WHERE t2.textID = t1.textID FOR XML PATH('')),1,1,'') AS [TypeText]

    FROM test t1

    GROUP BY textID

    If you need more information on how the FOR XML stuff works: I posted a few links a few days ago here.


    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]

  • July 12, 2010 at 10:44 pm

    #1191790

    Thanks LutzM

    It works , as per expectation

  • July 13, 2010 at 9:28 am

    #1192151

    vikrantkale18 (7/12/2010)

    Thanks LutzM

    It works , as per expectation

    Well, that's what usually happens if someone post ready to use sample data and expected results - exactly like you did! Great job 🙂


    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]

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

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