sql query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sql query question

Author

Topic

jgn1013
Starting Member

24 Posts

Posted - 2008-06-12 : 13:50:04

I have a table like this
ttm tname tter
jj john 1
jj john 2
jj john 3
dd david 1
dd david 17

i would like the query to look like this: (1,2,3 & 1,17 should be seperate rows)
ttm| tname| tter1|tter2|tter3
jj john 1 2 3
dd david 1 17

is this possible?
TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 14:02:53

Will you be certain about no of tter values coming per name?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 14:26:03

I some how liked the peso's way of doing it.


Declare @Table Table 
(
	ttm varchar(10),
	tname varchar(20),
	tter int 
)
Insert @Table 
Select 'jj','john', 1 Union all 
Select 'jj','john', 2 Union All 
Select 'jj', 'john', 3 Union All 
Select 'dd','david', 1 Union All
Select 'dd','david', 17

SELECT DISTINCT	ttm,tname,
		STUFF((SELECT ',' + cast(t2.tter as varchar(20)) FROM @Table AS t2 WHERE t2.ttm = t1.ttm and t2.tname = t1.tname FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Table AS t1
ORDER By 3 desc

Referenced : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293&whichpage=2

Chirag

http://www.chirikworld.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources