Appending Rows into a Single Cell

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)

Appending Rows into a Single Cell

Author

Topic

Yukke
Starting Member

5 Posts

Posted - 2006-07-27 : 10:46:41

Hello everyone,

I am trying to get a table that looks something like this:


City    State    Server    Type
Chicago  IL        Svr1      Data
Chicago  IL        Svr2      Data
Chicago  IL        Svr3      Backup
Chicago  IL        Svr4      Backup
Atlanta  GA        Svr1      Data
Atlanta  GA        Svr2      Data

in to a table that looks like this:


City    State    Server    Type
Chicago  IL       Svr1,Svr2  Data
Chicago  IL       Svr3,Svr4  Backup
Atlanta  GA       Svr1,Svr2  Data

How would I go about doing this?

Thanks for your help

airjrdn
Starting Member

35 Posts

Posted - 2006-07-27 : 11:14:54

create function dbo.fnGetServers (@City varchar(25), @State varchar(25), @Type varchar(25))
returns varchar(1000)
as
begin
declare @NewSvrCol varchar(1000)
select @NewSvrCol = ''
select @NewSvrCol = @NewSvrCol + Server + ',' from tmpOrig where city = @City and State = @State and Type = @Type
select @NewSvrCol = left(@NewSvrCol, len(@NewSvrCol)-1)
return(@NewSvrCol)
end

select distinct City, State, dbo.fnGetServers(City, State, Type), Type
from tmpOrig

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 11:29:04

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-27 : 11:35:00

first line of the link says it all:
"Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side."

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 12:05:45

Thats why I suggest that link everytime

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-27 : 12:37:18

Have a look here and see if it helps...but, what he said

http://weblogs.sqlteam.com/brettk/archive/2005/01/05/3946.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Yukke
Starting Member

5 Posts

Posted - 2006-07-27 : 16:17:38

Thanks for all your help

I made some modifications and am trying to display the table in the following format:

City    State  DataSvrs    BackupSvrs
Chicago  IL        Svr1,Svr2    Svr3,Svr4  
Atlanta  GA        Svr1,Svr2    NULL

Any suggestions?
Will I have to create a temp table and do a outer join?

*UPDATE*
I've made some progress but am still having a slight problem...
I am using this query:

ALTER       VIEW v_USAtlasAuthServers
as
select distinct u.City, u.State, 
CASE  u.Type WHEN 'Data' then dbo.fnGetServers(City, State, Type) ELSE null END as Data_Servers,
CASE  u.Type WHEN 'Backup' then dbo.fnGetServers(City, State, Type) ELSE null END as Backup_Servers 
from serverops.dbo.v_userviews u

where u.tree='tree1' and u.country='united states' and u.type in ('Data Server','Backup Server')
group by u.city, u.state,  u.type

which gives me the following table:

City    State  Data_Servers    Backup_Servers
Chicago  IL        Svr1,Svr2         NULL 
Chicago  IL        NULL            Svr3,Svr4 
Atlanta  GA        Svr1,Svr2         NULL

Which is not right as I want Chicago, IL to be in one row...any idea on how to fix this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources