convert many rows to one - 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

General SQL Server Forums

New to SQL Server Programming

convert many rows to one

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-09-13 : 19:20:48

Hello,

I' got this SQL:




select * from 

	data A2 

WHERE


A2.ID ='536031'

AND

A2.AttrID = '6'

That code gives me the following result:

ID.........Version....AttrID.......group
536031......1.........6..............test
536031......2.........6..............test5
536031......3.........6..............test5
536031......3.........6..............test6

What I try to do. I want to get all groups of the last version. The Groups schould be in one column...the the result must be the follwoing:

ID.........group
536031...test5, test6

At first you can throught the table. With the function mx you can get the last number of the version and then I need to filter the data again with the right attrid...
Is my way correct or is there a better one?

Kind regdards,

Lara

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-13 : 20:04:58

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

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 22:02:34

[code]
;With CTE
AS
(
select * from

data A2

WHERE

A2.ID ='536031'

AND

A2.AttrID = '6'
)

SELECT ID,
STUFF((SELECT ',' + [group] FROM CTE WHERE ID = c.ID ORDER BY [Version] FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM CTE)c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources