Concatenate Rows into Column and Where

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 2005 Forums

Transact-SQL (2005)

Concatenate Rows into Column and Where

Author

Topic

SuperGhost
Starting Member

12 Posts

Posted - 2007-10-23 : 19:18:13

I have a 1 to many relationship that I am trying to query. I would like to query the 1 but also add a column with the comma delimited values in the many table.

I also need to use a Where clause to select only the rows which contain a specific number.

Something along the lines of:

SELECT
m.one, m.two, (Comma Delimited List from Table 2) AS list
FROM
MasterTable m
WHERE
(Comma Delimited List from Table 2 contains '1255')

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-23 : 19:31:52

Hi superghost,
the simplest way is to join the 2 tables and format the columns of the second table in the select :

SELECT
m.one,
m.two,
n.one + ',' + n.two + ',' + n.three as [CommaDelimitedList]
FROM
mastertable m
INNER JOIN childtable n on m.key = n.foreignkey
WHERE (n.one + ',' + n.two + ',' + n.three) contains '1255'

But I think there is a better way, you can first create a temp table computing the calculated column (commadelimitedlist) and then join it with mastertable.
You can create a classic #temp table :

DECLARE @temp TABLE
( parentid int, commalist nvarchar(100))

INSERT INTO @temp
SELECT
n.parentid,
n.one + ',' + n.two + ',' + n.three as [commalist]
from childstable n

SELECT
m.one,
m.two,
t.commalist
FROM mastertable m
INNER join @temp t on m.id = t.foreignkey
WHERE commalist contains ('1255')

The second way is more elegant and easy to read and give you more control. (I don't know if is more faster than the first way).

SuperGhost
Starting Member

12 Posts

Posted - 2007-10-23 : 19:56:24

Hey Bob,

Thanks for the quick reply. I apologize but I probably confused you.

I don't need the fields m.one and m.two concatenated... rather I need m.one, m.two, and a new field [three] which contains a comma delimited list of rows from table 2.

So imagine table 2 is:

SELECT t2.refid FROM SecondTable t2 WHERE t2.FK_TBL1_ID = 1255

I would get multiple rows:

refid1
refid2
refid5...

So I want to concatenate the results of table 2 into my first query so it looks like:

refid1, refid2, refid5

So in my query I would have:

m.one / m.two / (concatenated column)
---------------------------------------
15 25 "refid1, refid2, refid5"

Then I need to take it one step further and I need my WHERE clause to filter the concatenated column to find all rows with "refid5."

So in the end I would have:

15 25 "refid1, refid2, refid5"
15 25 "refid5"
15 25 "refid2, refid5"

but I would not get:

15 25 "refid0, refid8"

I hope that clears it up! Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 21:18:27

see this link http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx
write a UDF to concat the refid into comma separated and use it as such

SELECT t1.TBL1_ID, dbo.the_concat_function(t1.TBL1_ID)
FROM   FirstTable t1
       INNER JOIN 
       (
           SELECT DISTINCT t2.FK_TBL1_ID
           FROM   SecondTable t2 
           WHERE  t2.refid = 'refid5'
       ) d ON t1.TBL1_ID = d.FK_TBL1_ID

KH
[spoiler]Time is always against us[/spoiler]

SuperGhost
Starting Member

12 Posts

Posted - 2007-10-23 : 21:32:05

Excellent khtan,

I ended up doing a little research and found a super fast FOR XML PATH implementation for concatenation.

However I still want to include all rows in TABLE 2 within my concatenated column. But I want to add a WHERE clause in the outer SELECT to filter the rows.

For example:


SELECT t1.TBL1_ID, dbo.the_concat_function(t1.TBL1_ID)
FROM   FirstTable t1
       INNER JOIN 
       (
           SELECT DISTINCT t2.FK_TBL1_ID
           FROM   SecondTable t2 
       ) d ON t1.TBL1_ID = d.FK_TBL1_ID
WHERE (generated column contains 'refid5')

Do I have to repeat the SELECT statement in my WHERE clause like so...? Whats the best method for this?


SELECT t1.TBL1_ID, dbo.the_concat_function(t1.TBL1_ID)
FROM   FirstTable t1
       INNER JOIN 
       (
           SELECT DISTINCT t2.FK_TBL1_ID
           FROM   SecondTable t2 
       ) d ON t1.TBL1_ID = d.FK_TBL1_ID
WHERE
       EXISTS 
       (
           SELECT 1
           FROM   SecondTable t2
           WHERE  t2.refid = 'refid5' AND t2.TBL1_ID = t1.TBL1_ID
       )

Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 21:36:11

you don't have to. The derived table [d] in my query only filtered out the ID that contains refid5


       (
           SELECT DISTINCT t2.FK_TBL1_ID
           FROM   SecondTable t2 
           WHERE  t2.refid = 'refid5'
       ) d

KH
[spoiler]Time is always against us[/spoiler]

SuperGhost
Starting Member

12 Posts

Posted - 2007-10-23 : 21:44:07

Ah... Genius! I totally missed that it was an INNER JOIN.

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources