jfreeman2010
asked on
T-SQL row concatenation
Hi need help on t-sql:
SELECT1:
SELECT ID,
NOTE
FROM TEST_TB3
RESULT:
ID NOTE
112200 TEST NOTE1
112200 TEST NOTE2
112200 TEST NOTE3
112201 TEST NOTE4
112201 TEST NOTE5
112202 TEST NOTE6
SELECT2:
SELECT T1.NAME,
T2.AGO,
NOTES = (
SELECT T3.NOTE
FROM TEST_TB3 T3
WHERE T3.ID = T1.ID
)
FROM TEST_TB1 T1,
TEST_TB2 T2
WHERE T1.ID = T2.ID
the above code didn't work becuase the subquery reture move then 1 record.
LOOKING FOR RESULT:
NAME AGE NOTES
JOHN DOE 25 NOTE1, NOTE2, NOTE3
thank you
SELECT1:
SELECT ID,
NOTE
FROM TEST_TB3
RESULT:
ID NOTE
112200 TEST NOTE1
112200 TEST NOTE2
112200 TEST NOTE3
112201 TEST NOTE4
112201 TEST NOTE5
112202 TEST NOTE6
SELECT2:
SELECT T1.NAME,
T2.AGO,
NOTES = (
SELECT T3.NOTE
FROM TEST_TB3 T3
WHERE T3.ID = T1.ID
)
FROM TEST_TB1 T1,
TEST_TB2 T2
WHERE T1.ID = T2.ID
the above code didn't work becuase the subquery reture move then 1 record.
LOOKING FOR RESULT:
NAME AGE NOTES
JOHN DOE 25 NOTE1, NOTE2, NOTE3
thank you
ASKER
the notes value need to be concatenate from the subquery.
try the below:
select t1.Name,
T2.AGO,
Notes = stuff((select ',' + T3.Note from Test_TB3 for xml path('')), 1, 1, '')
from test_tb1 T1
inner join Test_tb2 T2 on T1.ID = T2.ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops, missed something in my query
select t1.Name,
T2.AGO,
Notes = stuff((select ',' + T3.Note from Test_TB3 WHERE T3.ID = T1.ID for xml path('')), 1, 1, '')
from test_tb1 T1
inner join Test_tb2 T2 on T1.ID = T2.ID
oh, well. so many typos
select t1.Name,
T2.AGO,
Notes = stuff((select ',' + T3.Note from Test_TB3 T3 WHERE T3.ID = T1.ID for xml path('')), 1, 1, '')
from test_tb1 T1
inner join Test_tb2 T2 on T1.ID = T2.ID
ASKER
I try the following, error in 'AND':
SELECT T1.NAME,
T2.AGO,
NOTES = stuff(( SELECT T3.NOTE
FROM TEST_TB3 T3
WHERE T3.ID = T1.ID
AND DELETED IS NULL
for xml path ('')),1,1,''
)
FROM TEST_TB1 T1,
TEST_TB2 T2
WHERE T1.ID = T2.ID
thanks for helping!!
SELECT T1.NAME,
T2.AGO,
NOTES = stuff(( SELECT T3.NOTE
FROM TEST_TB3 T3
WHERE T3.ID = T1.ID
AND DELETED IS NULL
for xml path ('')),1,1,''
)
FROM TEST_TB1 T1,
TEST_TB2 T2
WHERE T1.ID = T2.ID
thanks for helping!!
you missed the ',' there
and DELETED is a reserved word so you need to use brackets
and DELETED is a reserved word so you need to use brackets
SELECT T1.NAME,
T2.AGO,
NOTES = stuff(( SELECT ',' + T3.NOTE
FROM TEST_TB3 T3
WHERE T3.ID = T1.ID
AND [DELETED] IS NULL
for xml path ('')),1,1,''
)
FROM TEST_TB1 T1,
TEST_TB2 T2
WHERE T1.ID = T2.ID
ASKER
Hi ralmada,
it did not like the + sign, error 'the data types varchar and text are incompatible in the add operator.
it did not like the + sign, error 'the data types varchar and text are incompatible in the add operator.
ASKER
Hi CGLuttell,
your code works. thanks
your code works. thanks
It has a function with a cursor, but it works.
create FUNCTION [dbo].[fn_GetNotes] ( @noteid INT )
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @Notes VARCHAR(2000)
, @note VARCHAR(100)
SELECT @notes = ''
DECLARE noteCursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT COALESCE(note, '')
FROM dbo.test_tb3
WHERE id = @noteid
OPEN noteCursor
FETCH NEXT FROM noteCursor INTO @note
WHILE @@FETCH_STATUS = 0
BEGIN
SET @notes = @notes + @note + ', '
FETCH NEXT FROM noteCursor INTO @note
END
IF LEN(@notes) > 2
SET @notes = LEFT(@notes, LEN(@notes) - 2)
CLOSE noteCursor
DEALLOCATE noteCursor
RETURN @notes
END
SELECT T1.name
, T2.ago
, dbo.fn_GetNotes(T1.ID) AS NOTES
FROM test_tb1 AS T1
INNER JOIN test_tb2 AS T2
ON T1.ID = T2.ID
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the table was build long time ago, so I can only working with the data. thanks, it woks.
>>Hi CGLuttell,
your code works. thanks <<
Are you sure you've tried it correctly?
His suggestion will add lots of extra commas there, so starting from this:
ID NOTE
112200 TEST NOTE1
112200 TEST NOTE2
you will end up in:
John DOE 25 Test,Note1,Test,Note2, <<--- additional commas there !!!
your code works. thanks <<
Are you sure you've tried it correctly?
His suggestion will add lots of extra commas there, so starting from this:
ID NOTE
112200 TEST NOTE1
112200 TEST NOTE2
you will end up in:
John DOE 25 Test,Note1,Test,Note2, <<--- additional commas there !!!
ASKER
thank you all!!!
ASKER
I will check both code more, I have a long and diff query and this just part of it, so will need more time to make sure it works. THANK YOU!!!!
Glad to help.
ralmada is right that my quick post will replace all spaces with a comma including the embeded ones, so check it out first. I just grabed an example I had used before and replaced the parts with your query example. I have also used the code like ralmada sugested where needed with complex strings, either will work.
good luck to you,
Chris
ralmada is right that my quick post will replace all spaces with a comma including the embeded ones, so check it out first. I just grabed an example I had used before and replaced the parts with your query example. I have also used the code like ralmada sugested where needed with complex strings, either will work.
good luck to you,
Chris
SELECT T1.NAME,
T2.AGO,
NOTES IN (
SELECT T3.NOTE
FROM TEST_TB3 T3
WHERE T3.ID = T1.ID
)
FROM TEST_TB1 T1,
TEST_TB2 T2
WHERE T1.ID = T2.ID