Link to home
Start Free TrialLog in
Avatar of jfreeman2010
jfreeman2010Flag for United States of America

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
Avatar of gplana
gplana
Flag of Spain image

change = to IN if your subquery returns more than 1 row:

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
Avatar of jfreeman2010

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

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

Open in new window

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!!
you missed the ',' there
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

Open in new window

Hi ralmada,

it did not like the + sign, error 'the data types varchar and text are incompatible in the add operator.
Hi CGLuttell,

your code works. thanks
Avatar of David Christal CISSP
David Christal CISSP

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

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 !!!
thank you all!!!
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