Solved

T-SQL row concatenation

Posted on 2010-09-10
17
350 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:jfreeman2010
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 33649625
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
0
 

Author Comment

by:jfreeman2010
ID: 33649706
the notes value need to be concatenate from the subquery.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33649846
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

0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 total points
ID: 33649880
From a previous question/answer at http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24310874.html
Try this XML trick to concatinate those values.
SELECT  T1.NAME,

      T2.AGO,

      NOTES = REPLACE((

             SELECT T3.NOTE AS [data()]

             FROM TEST_TB3 T3

             WHERE T3.ID = T1.ID

             FOR XML PATH ('')), ' ', ',')

FROM       TEST_TB1 T1,

      TEST_TB2 T2

WHERE      T1.ID = T2.ID

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33649900
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

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33649909
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

0
 

Author Comment

by:jfreeman2010
ID: 33649953
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!!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33649972
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

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:jfreeman2010
ID: 33650097
Hi ralmada,

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

Author Comment

by:jfreeman2010
ID: 33650103
Hi CGLuttell,

your code works. thanks
0
 
LVL 5

Expert Comment

by:David Christal CISSP
ID: 33650105
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

0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 250 total points
ID: 33650125
oh my friend, you are using TEXT datatype. You should get rid of it! You should switch to varchar(max) instead!
TEXT datatype was good for SQL 2000, but not in SQL 2005. You should change your column datatype using alter table.
If you can't do this, then you can try it in the query, but again it's not good practise.

SELECT  T1.NAME,

      T2.AGO,

      NOTES = stuff(( SELECT ',' + cast(T3.NOTE as varchar(max))

             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

0
 

Author Comment

by:jfreeman2010
ID: 33650152
the table was build long time ago, so I can only working with the data. thanks, it woks.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33650156
>>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 !!!
0
 

Author Closing Comment

by:jfreeman2010
ID: 33650162
thank you all!!!
0
 

Author Comment

by:jfreeman2010
ID: 33650193
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!!!!
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 33650381
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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now