blogs.conchango.com

I'm sure there are a handful of (probably more efficient) alternatives to the below but here is a first pass.

Cheers,

Nick

with ConcatNamesCTE (id, [name], rn)

as

(

select

 id, [name], rn

from

 (select id, [name],

row_number() over(partition by id order by id) as rn

 from t1) a

where rn = 1

union all

select

 b.id, cn.[name] + ',' + b.[name], b.rn

from

 (select id, [name],

row_number() over(partition by id order by id) as rn

 from t1) b

 inner join ConcatNamesCTE cn

 on cn.id = b.id

 and cn.rn + 1 = b.rn

)

select

 d.id, d.[name]

from

 (select max(rn) as rn, id

from ConcatNamesCTE

group by id) c

 inner join ConcatNamesCTE d

on d.id = c.id

and d.rn = c.rn

order by id asc

--hope the tab formatting looks better posted than it did in the comments field!

select res.id, max(res.name) as [name]

from

(

select c.id,

case when PATINDEX('%' + d.name + '%', c.name) = 0 and c.id = d.id

then c.name + ', ' + d.name

else c.name

end as [name]

from

( select a.id, min(a.name) as [name]

from

( select y.id,

case when PATINDEX('%' + z.name + '%', y.name) = 0 and y.id = z.id

then y.name + ', ' + z.name

end as [name]

from t1 y

inner join t1 z on y.id = z.id

) a

group by a.id

) c

inner join t1 d on c.id = d.id

) res

group by res.id

select distinct

  id

,  stuff(

  (

     select ',' + name as [text()]

     from t1 b

     where

           b.id = a.id

     for xml path('')

  )

,  1

,  1

,  ''

) as name_csv

from t1 a

order by 1

In response to Jamie Thomson's T-SQL poser , here's one very efficient method of concatenation

Hi David,

I think using the Stuff function is a much cleaner solution than using the Substring function.  But you are correct, the XML PATH does have some side effects!

And I did miss the order by name in my solution!

Jamie

CREATE TABLE #n (id int, name varchar(50))

INSERT #n SELECT 1, 'Jamie'

INSERT #n SELECT 1, 'Joe'

INSERT #n SELECT 1, 'John'

INSERT #n SELECT 1, 'Jamie'

INSERT #n SELECT 2, 'Sai'

INSERT #n SELECT 2, 'Sam'

INSERT #n SELECT 3, 'Roger'

INSERT #n SELECT 4, 'Walter'

INSERT #n SELECT 4, 'Teddy'

WITH n1 AS (

SELECT id, CONVERT(varchar(500), MIN(name)) name

FROM #n

GROUP BY id

UNION ALL

SELECT a.id, CONVERT(varchar(500), a.name + ',' + b.name) name

FROM #n a JOIN n1 b ON a.id = b.id AND a.name > b.name

)

SELECT id, MAX(name)

FROM n1

GROUP BY id

Sorry, I copied my temp table version in there.  Here's the correct version:

WITH t2 AS (

SELECT id, MIN(name) name

FROM t1

GROUP BY id

UNION ALL

SELECT a.id, a.name + ',' + b.name name

FROM t1 a JOIN t2 b ON a.id = b.id AND a.name > b.name

)

SELECT id, MAX(name)

FROM t2

GROUP BY id

Mine is similar to JamieH's, but I prefer to use 'group by' instead of 'distinct', as 'distinct' will calculate the CSV for every row (and then check them for uniqueness), whereas 'group by' will start by finding the distinct ids first, and then only create the CSVs for the unique ids.

If you duplicate the rows in the tables a few times, you'll see the effect quite clearly. Execution plans will also demonstrate it here. I might write a blog entry on this myself...

select

id,

stuff((

select ',' + t.[name]

from t1 t

where t.id = t1.id

order by t.[name]

for xml path('')

),1,1,'') as name_csv

from t1

group by id

;

Jamie Thomson (SQL Server MVP from the UK) threw out a challenge recently, and it was interesting to

Jamie

Deja Vu????

http://www.sqlteam.com/item.asp?ItemID=256

But as I said, you can write Procedural Logic in a View.. Just use a UDF!!!!!

Paul

Hmm... I'm prefer to realize such things as CLR aggregates. Any better solution?

$query = "SELECT content_id from tbl_content ORDER BY limit 1 ";

i have to select all id from one table and the id can display in the same order in the another table in database

for ex:content_id(1234567)

nexttable:menu_id(1234567)content_id(1234567)get from content_id and link to display same as

how to select a complete id in one table and equally link to another in the another table in the same databade