Concatenating Rows... 8

[cLFlaVA]

I'm curious if it's possible to do the following:

I have the following table:
[tt]
ID NAME
-----------------------
1 Bob
1 Chris
2 Harry
2 Joe
2 Tim
3 Tom
4 John
5 Larry
[/tt]

Is there any way at all to return this:

[tt]
ID NAMES
-------------------------------------
1 Bob, Chris
2 Harry, Joe, Tim
3 Tom
4 John
5 Larry
[/tt]

Thanks for your help.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[twifosp]

The easiest way is to create a user defined function. This is very resource intensive, so if you need to do this on a regular basis, I recommend altering your database to store the names in columns.

-- create this function first
create function dbo.udf_concat_rows
(
@id int
)
returns varchar(1000) as
begin
	
	declare @concat varchar(1000)
	select 	@id = coalesce(@concat,'')+ cast(name as varchar(500))+', '
	from 	yourtable
	where	id = @id
	return @concat
end -- end function

-- then run select statement like so:

select 	id,
	'Name' = dbo.udf_concat_rows(id)
from	yourtable

 

[cLFlaVA]

Thanks. I was looking for a way to do this with a straight SQL query - apparently that's not possible though...

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[twifosp]

If there is one, I don't know of it. It's probably possible with subqueries ect if you know how many rows and that, in your example, the last name is always after the first.

But also like in your example you don't have a last name for every first. This is where a subquery wouldn't be dynamic enough to get the next row and know whether it should append it to the first or not.

It's even worse if you have more than a set number of rows per ID, (ie usually 2 and for some you have 3 or 4). Writing a function to coalesce the text based on ID is the only sane way I've found. And believe me, for my application, it's so resource intensive that I've looked for all other methods... So if anyone has a better one, I would LOVE to be proven wrong

 

[jelf]

This may get you started

DECLARE @employeelist varchar(100)
SELECT @employeelist = ''
SELECT @employeelist = @employeelist + ',' + Cast(name as varchar(10))
FROM Table2 WHERE ID = 1
SELECT @employeelist = REPLACE(SUBSTRING(@employeelist, 2, 100), ' ', '')
print @employeelist


This produces a comma separated list for ID = 2 you need more to get what you want.

 

[dbomrrsm]

sililarly

DECLARE @cols varchar(8000)
SELECT @cols = ''
SELECT @cols = @cols + CAST([name] as varchar) + '|'
FROM master..sysdatabases
SELECT @cols

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Or

nigelrivett (IS/IT--Manageme) Aug 26, 2004
1. use a function to concatenate
see

http://www.nigelrivett.net/CSVStringFromTableEntries.html

2.
select id, case when count(*) > 2 then 'see index' else dbo.csvtbl(id) end
from tbl
group by id

just posted by nigelrivett in reply to a similar query

[blue]DBomrrsm[/blue]

 

[jelf]

Sorry but this uses a dreaded cursor to get your results, I'm sure someone will be able to do it without cursors.


DECLARE @ID AS int
DECLARE ID_C CURSOR FOR
SELECT DISTINCT ID FROM Table2

OPEN ID_C

FETCH NEXT FROM ID_C INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @employeelist varchar(100)
SELECT @employeelist = ''
SELECT @employeelist = @employeelist + ',' + Cast(name as varchar(10))
FROM Table2 WHERE ID = @ID
SELECT @employeelist = CAST(@ID as varchar(2)) + REPLACE(SUBSTRING(@employeelist, 1, 100), ' ', '')
PRINT @employeelist
FETCH NEXT FROM ID_C INTO @ID
END
CLOSE ID_C
DEALLOCATE ID_C


Regards


John

 

[Kris11]

twifosp,

It worked like a charm... star for you...

-Kris

 

[ESquared]

You can do this without cursors, without functions, all in a single operation, as long as you know the maximum number of elements per group. Here's a little example I worked up once (I'm so glad I've started saving these things!). It can handle up to ten items per group and ignores Nulls.

CREATE TABLE #t (
	Deal int,
	Competitor varchar(10))

INSERT INTO #t
	SELECT 1,'X' UNION
	SELECT 1,'Y' UNION
	SELECT 1,'Z' UNION
	SELECT 2,'X' UNION
	SELECT 3,'Y' UNION
	SELECT 4,'Y' UNION
	SELECT 4,'X' UNION
	SELECT 5,'W' UNION
	SELECT 5,NULL UNION
	SELECT 5,'V'

SELECT * FROM #t

SELECT
      Seq.Deal,
      SUBSTRING(
         ISNULL(Max(CASE Cnt WHEN 1 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 2 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 3 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 4 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 5 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 6 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 7 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 8 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 9 THEN Cmp END),'')
         + ISNULL(Max(CASE Cnt WHEN 10 THEN Cmp END),'')
      ,2,7999)
   FROM
      (SELECT
	         T1.Deal, Cmp = ','+ T1.Competitor, Cnt = Count(T2.Deal)
	   	FROM #t T1
				INNER JOIN #t T2 ON T1.Deal = T2.Deal AND T1.Competitor >= T2.Competitor
	      GROUP BY T1.Deal, T1.Competitor
      ) Seq
   GROUP BY Deal

DROP TABLE #t

I forget what I did to remove the warning message, "Warning: Null value is eliminated by an aggregate or other SET operation." This is not really a problem, but moving the aggregate functions inside the case statements or outside the isnull might eliminate it.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.