Combine Rows with Loop? - 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 2005 Forums

Transact-SQL (2005)

Combine Rows with Loop?

Author

Topic

pcs.stilleyb
Starting Member

4 Posts

Posted - 2007-12-14 : 08:09:11

Message Preview
Hi all,
I'm pretty new to SQL and I'm trying to write a script that will add columns to a row with a loop. For example; I have two tables, One is a "salesperson" table and the other is a "client" table. One salesperson can have multiple clients. I am currently using a script that brings back:

Salespersonid, Lname, Fname, Addr, City, State, Zip, ClientID
User1, Smith, Bob, #1, Happy St, City1, St1, 12345, Client1
User1, Smith, Bob, #1, Happy St, City1, St1, 12345, Client2
User2, Jones, Jim, #2, Happy St, City2, St2, 12346, Client3
User3, doe, Jane, #3, Happy St, City3, St3, 12347, Client4
and so on...

I'd like it to just add another column with the other clientids for that salesperson. Like this.

Salespersonid, Lname, Fname, Addr, City, State, Zip,ClientID, ClientID
User1, Smith, Bob, #1, Happy St, City1, St1, 12345, Client1, Client2
User2, Jones, Jim, #2, Happy St, City2, St2, 12346, Client3
User3, doe, Jane, #3, Happy St, City3, St3, 12347, Client4

There is no finite number of clients a salesperson can have. I was trying to make two temp tables. One with the SalespersonID and the number of clients they have so I can use the counter to add the appropriate number of columns. Then one with the finished product. I feel like I might need a third temp table, but I'm not sure.

SalesPerson table contains:
USERID,
LASTNAME,
FIRSTNAME,
ADDRESS,
CITY,
STATE,
ZIPCODE

Client table contains:
USERID,
CLIENTID

so far I have been working with this:

SELECT sp.USERID,
COUNT(sp.USERID) as Counter
INTO #tmpUsrCnt
FROM salesperson sp
GROUP BY sp.USERID
ORDER BY Counter DESC

--SELECT * FROM #tmpUsrCnt
--DROP TABLE #tmpUsrCnt

DECLARE @COUNT int
DECLARE @USERID varchar(20)

SET @COUNT = (SELECT TOP 1 Counter FROM #tmpUsrCnt)

SELECT sp.USERID,
sp.LASTNAME,
sp.FIRSTNAME,
sp.ADDRESS,
sp.CITY,
sp.STATE,
sp.ZIPCODE,
cl.CLIENTID
INTO #tmpTotal
FROM salesperson sp, clients cl
WHERE sp.USERID = uc.USERID

WHILE @COUNT > 0
BEGIN
SET @USERID = (SELECT TOP 1 USERID FROM salesperson)
SELECT cl.CLIENTID
FROM clients cl
WHERE cl.USERID = @USERID

SET @COUNT = @COUNT - 1
END

SELECT * FROM #tmpTotal

Any help you can give me will be appreciated. Thanks in advance for your time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:15:35

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

Failing to plan is Planning to fail

pcs.stilleyb
Starting Member

4 Posts

Posted - 2007-12-14 : 11:07:49

I appreciate the information. However, the article only seems to discuss advantages/disadvantages of using temp tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-14 : 12:12:44

Put the result of script onto a temp table #temp
CREATE TABLE #temp1
(
Salespersonid int,
Lname varchar(50),
Fname varchar(50),
Addr varchar(100),
City varchar(50),
State varchar(50),
Zip varchar(50),
ClientID int,
AltClientIDs varchar(500)
)
SELECT Salespersonid, Lname, Fname, Addr, City, State, Zip,MIN(ClientID) FROM #temp
GROUP BY Salespersonid, Lname, Fname, Addr, City, State, Zip

UPDATE t1
SET AltClientIDs=LEFT(cl.Clientlist,LEN(cl.Clientlist)-1)
FROM #temp1 t1
CROSS APPLY (SELECT ClientID +','as text()
FROM #temp1 t2 WHERE t2.Salespersonid=t1.Salespersonid AND t2.ClientID <> t1.ClientID
FOR XML PATH(''))cl (Clientlist)

this will give other ClientIDs values as a comma seperated value in a single column.This method is explained in link sent by madhivanan.

If you want to create new columns and insert values,then you might have to loop through items and create column for each.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-14 : 14:15:28

Since you are new I'll give the obligatory "That is bad design" speech. :)

Why do you want to do that? If you are just generating a report or something fine. But, are you actually going to store that data as a delimited string? If so, I would seriously consider rethinking that decision. It appears that your source data is already bad enough, are you sure you want to make it worse? If anything you should create a SalesPersonClient table that maps the SalesPerson to a Client. If you would like to know more any number of people on this forum can help out. And we would much rather see you go with a better design than come back in two days asking how to parse out the string so you can get all the Clients for a sales person. ;)

pcs.stilleyb
Starting Member

4 Posts

Posted - 2007-12-17 : 08:06:21

quote:
Originally posted by Lamprey

Since you are new I'll give the obligatory "That is bad design" speech. :)

Why do you want to do that? If you are just generating a report or something fine. But, are you actually going to store that data as a delimited string? If so, I would seriously consider rethinking that decision. It appears that your source data is already bad enough, are you sure you want to make it worse? If anything you should create a SalesPersonClient table that maps the SalesPerson to a Client. If you would like to know more any number of people on this forum can help out. And we would much rather see you go with a better design than come back in two days asking how to parse out the string so you can get all the Clients for a sales person. ;)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 08:09:30

http://www.datamodel.org/NormalizationRules.html

E 12°55'05.25"
N 56°04'39.16"

pcs.stilleyb
Starting Member

4 Posts

Posted - 2007-12-17 : 08:23:21

quote:
Originally posted by Lamprey

Since you are new I'll give the obligatory "That is bad design" speech. :)

Why do you want to do that? If you are just generating a report or something fine. But, are you actually going to store that data as a delimited string? If so, I would seriously consider rethinking that decision. It appears that your source data is already bad enough, are you sure you want to make it worse? If anything you should create a SalesPersonClient table that maps the SalesPerson to a Client. If you would like to know more any number of people on this forum can help out. And we would much rather see you go with a better design than come back in two days asking how to parse out the string so you can get all the Clients for a sales person. ;)

That last post was supposed to say...

Since I am new...I will take your advice and spend some time looking for a better way to get what I want. The database is pretty much garbage. I have only been here for a month and can say that whoever designed this thing had no clue. It looks like there is a ton of information on this forum and I'll use what I can. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources