Rows into column - 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)

Rows into column

Author

Topic

Mickey13
Starting Member

4 Posts

Posted - 2004-11-17 : 07:30:26

Is it somehow possible to convert rows into a column in a single query?

tblObject:
ID Name
1 Object 1
2 Object 2
3 Object 3

tblValue:
ObjectID Name
1 Value 1
1 Value 2
1 Value 3
2 Value 1
3 Value 1

I need an output like:
ID Name Values
1 Object 1 Value1, Value 2, Value 3
2 Object 2 Value1
3 Object 3 Value1

I could do some cursor-tricks but is it possible to do this in a single query?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 07:36:25

well u can write a function which return a CSV string and call that...


create function dbo.ToCSV(@ObjectId int)
returns varchar(1000)
as
begin
	Declare @CSVList varchar(1000)
	SELECT @CSVList = COALESCE(@ColumnList + ', ', '') + Name
	FROM tblValue
	WHERE ObjectId = @ObjectId 
	
	return @CSVList
end 

select *, dbo.ToCsv(ObjectId) from tblObject

Go with the flow & have fun! Else fight the flow

Mickey13
Starting Member

4 Posts

Posted - 2004-11-17 : 07:57:27

That is clever. I didn't know the string-assignment would work 'setbased'. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources