Column to row - 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

SQL Server Development (2000)

Column to row

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-19 : 13:17:53

How can insert contents of a column in a table into a row? Like this:

Column1
--------
Data1
Data2
Data3

The result I want : "Data1,Data2,Data3"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-19 : 13:22:22

If you search SQL Team for "cross tab" you'll get several links, including this one:

http://www.sqlteam.com/item.asp?ItemID=2955

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-19 : 20:47:24

Thanks robvolk. I searched but couldn't find anything good for me.

I tried to do it with a loop. First of all the layout of the database:


[MODERATORS]                      [USERS]
-------------                  -------------
MOD_ID             |--FK-----> U_ID
MOD_User     <-----|           U_Nick
MOD_ForumID                    ....


USERS TABLE:
 U_ID   U_Nick
----- --------
    1    Nick1
    2    Nick2
.....


MODERATORS TABLE:
MOD_ID  MOD_User MOD_Forum (<=FK to F_ID identity row in FORUMS table)
------- -------- ---------
      1        1         1
      2        2         1

(The MODERATORS table has only 2 rows and I'm pretty sure it won't exceed 5 rows)

So what I want to see is this:


  |(No column name)
------------------
1 | Nick1,Nick2

I tried the following code but it returned NULL as result:

declare @rowcount int, @forum int, @counter int
declare @mod varchar(1000), @modid int, @moderatorler varchar(1000)

--set temporary.it will be passed as parameter later..
set @forum=1

set @counter=1

select @rowcount=count(MOD_ID) from dbo.[MODERATORLER]
set @rowcount=@rowcount+1

while @counter<@rowcount
begin
select @modid=MOD_Uye from dbo.[MODERATORLER] where MOD_Forum=@forum
select @mod=U_TakmaAd from dbo.[UYELER] where U_ID=@modid
set @moderatorler=@moderatorler+','+@mod
set @counter=@counter+1
end
select @moderatorler

I wonder why it's returning NULL...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-20 : 10:23:55

Check through this thread:

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

There's a link or two in there that has something you might use.

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-20 : 18:09:10

Thanks robvolk. But I already red those topics while searching for "cross tab". All of the examples in the topics are using temporary table and I don't want to use them just for a few rows. I'll be glad if you could check my code and maybe tell me why it's returning NULL..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-20 : 23:27:45

<rant>
I do not understand your aversion to using temporary tables. I don't see anything in your requirements that would recommend against using them, based on the functionality you're looking for. The techniques pointed out not only work, but work very well. No disrespect intended, but your code is not working right now, what harm could it do to use the temp table approach? Considering that it's about 85% identical to the solution provided in the links.
</rant>

Please don't take this personally, it just frustrates me that people don't even seem to try a solution that's offered. I think you should look at using them (temp tables); it's another technique and you are better off learning about it anyway. You'll at least have enough experience with them to determine on your own if they provide a workable solution, or if their actual performance requires another approach. Categorically dismissing them is unreasonable.

OK, I've taken a deep breath, thanks. As far as the NULL return is concerned, you'll need to set your variable @moderatorler to an empty string before you start appending to it:

set @moderatorler=''
while @counter<@rowcount
begin
select @modid=MOD_Uye from dbo.[MODERATORLER] where MOD_Forum=@forum
select @mod=U_TakmaAd from dbo.[UYELER] where U_ID=@modid
set @moderatorler=@moderatorler+','+@mod
set @counter=@counter+1
end
select @moderatorler

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-22 : 18:44:54

Thanks a lot for the help robvolk.

I want to avoid temporary tables because I think it will hurt the performance unnecessarily for such a small operation. There are only three or four rows max and I think a small loop will perform better than temporary table.

I understand your frustration, I would feel just like you if I was in your situation. I always try get the most out of the help, I'm never a type that wants you to write the script for them. I hate this. It's true I didn't tried the examples on the pages I've found but the reason I didn't try was all of them had temporary tables. But I examined the examples very carefully, actually I got the loop idea from one of them.

I got the script running now, thanks a lot again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources