By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

combining rows into a single row output

P: 1
Hi,
for the purpose of this question i have 2 tables:

Table1 with fields ID, Name, NoteID
Table2 with fields NoteID, Note_Text

for every 1 record in Table1 there are many in Table2. If i use a standard JOIN i get multiple rows in a recordset but what i want is 1 Row with the field "Notes" that would comtain all the Note_text entries for the record in Table1.

example:
ID, Name, Notes
------------------------
1, Bob, Test note1, Test note2, Test note3
2, Carl, <null>
3, paul, note for paul

can anyone help please?
Apr 20 '07 #1
Share this Question
Share on Google+
1 Reply


P: 9
-- Try this:

SET NOCOUNT ON

DECLARE @ID varchar (50)
DECLARE @Name varchar (200)
DECLARE @StringText varchar(8000)


Declare curBuildString cursor
static for
select distinct id, Name from Table1

OPEN curBuildString
FETCH NEXT FROM curBuildString
INTO @ID, @Name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @StringText = @id + ', ' + rtrim(@name) +', '
SELECT Table2.notetext into #t1
FROM Table1
INNER JOIN Table2 ON Table1.noteid = Table2.noteid
WHERE Table1.ID = @ID

WHILE (Select top 1 notetext from #t1) is not null
BEGIN
select @StringText = @StringText + rtrim((select top 1* from #t1)) +', '
DELETE FROM #t1 WHERE #t1.notetext = (SELECT TOP 1 notetext FROM #t1)
END

print left(@StringText, len(@StringText) -1)
DROP TABLE #t1
FETCH NEXT FROM curBuildString
INTO @ID, @Name
END

CLOSE curBuildString
DEALLOCATE curBuildString
SET NOCOUNT OFF
GO
Apr 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.