How to show row values in columns wise.

Question

Post reply

How to show row values in columns wise.

Inesh

SSC Rookie

Points: 29
More actions
September 11, 2007 at 3:45 am

#70669

In my table, columnname are and . where records are
GroupId TestName
1 AAA
1 BBB
1 CCC
2 DDD
2 EEE
3 FFF
I need the query this table result should be displayed has
1 AAA, BBB, CCCC
2 DDD, EEE
3 FFF
Can i Know how to get this type of result. what will be the query for that ?
thanks

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI there,

What version of SQL you using?

If you using 2005 you could use the pivot functionality if you using 2000 you could use case statements

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

SELECT DISTINCT
        t1.GroupID
      , STUFF(( SELECT DISTINCT TOP 100 PERCENT
                        ',' + t2.TestName
                FROM    table1 AS t2
                WHERE   t2.GroupID = t1.GroupID
                ORDER BY ',' + t2.TestName
              FOR
                XML PATH('')
              ), 1, 1, '') AS CODES
FROM    table1 AS t1
ORDER BY t1.GroupID

(assuming the table is called table1)

The result is:

GroupID     CODES
----------- ---------------------------
1           AAA,BBB,CCC
2           DDD,EEE
3           FFF

(3 row(s) affected)

You may also want to look at the thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=395500

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Inesh SSC Rookie Points: 29 More actions · Answer 3

Hi Andras,

Thanks your so much. it works fine they i want.

Greetings

Srini