SQL Rookie question - 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)

SQL Rookie question

Author

Topic

Roy Slade
Starting Member

7 Posts

Posted - 2004-02-09 : 16:10:17

I have a table with multiple entries for comments. Each entry is a unique row. I need to combine all of the comments into one row. For example:

Name Comment
Fred Nice Guy
Fred Brings Donuts
Joe Hard worker
Joe Stays late
Joe Never leaves early

What I want is:
Name Comment
Fred Nice Guy Brings Donuts
Joe Hard Worker Stays Late Never leaves early

I've tried cursors, updates, and a long list of select statements with no luck. Thanks in advance for any and all help.

Roy Slade
Starting Member

7 Posts

Posted - 2004-02-09 : 16:24:31

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-09 : 16:42:36

Understand that the order of data in the database does NOT gaurentee the way the data will come out the same way every time...you need to ORDER BY something...


USE Northwind

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 varchar(10), Col3 varchar(25))
GO

INSERT INTO myTable99(Col2,Col3)
SELECT 'Fred','Nice Guy' UNION ALL
SELECT 'Fred','Brings Donuts' UNION ALL
SELECT 'Joe', 'Hard worker' UNION ALL
SELECT 'Joe', 'Stays late' UNION ALL
SELECT 'Joe', 'Never leaves early'
GO

CREATE TABLE #myTable00(Col2 varchar(10), NewLine varchar(8000))

DECLARE @Col2 varchar(10), @NewLine varchar(8000)

SELECT @NewLine = ''

DECLARE myCursor99 CURSOR
FOR
SELECT DISTINCT Col2 FROM myTable99

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @Col2

WHILE @@FETCH_STATUS = 0
  BEGIN
	SELECT @NewLine = @NewLine + Col3 + ' ' FROM myTable99 WHERE Col2 = @Col2 Order by Col1
 	INSERT INTO #myTable00(Col2, NewLine) SELECT @Col2, @NewLine
	SELECT @NewLine = ''
	FETCH NEXT FROM myCursor99 INTO @Col2
  END

CLOSE myCursor99
DEALLOCATE myCursor99


SELECT * FROM #myTable00
GO

DROP TABLE #myTable00
DROP TABLE myTable99
GO

Brett

8-)

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-02-09 : 17:04:12

You might be able to take this process (http://www.sqlteam.com/item.asp?ItemID=11021) for converting multiple rows into a CSV and just get rid of the comma and space. As Brett said, you'll need to make sure you have some sort of line number for ordering by but that shouldn't be a big deal.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-09 : 17:09:55

if you have sql 2000, check out the comments for a great UDF solution to this problem. It's quite efficient as well.

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-10 : 07:20:42

I spent a whole afternoon on that stupid article and now every time it gets referenced there has to be a PS pointing to byrmol's "better" solution

... bummer.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-10 : 09:06:17

I'm sorry man, but that's my FAVORITE UDF of all time ! a true hall of famer ...

your article is great, though ... i've used that UPDATE technique more than once since I learned it hear at SqlTeam !! (though it still frightens me !) And if you don't have sql2000, that's the way to go.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-10 : 09:49:28

Wow...what a history lesson

What a noob...only here 1yr 1mo

Cursors falling by the wayside...

Brett

8-)

Roy Slade
Starting Member

7 Posts

Posted - 2004-02-10 : 13:26:54

My thanks to all. Byrmol's techmique works great (after I spotted the typo