SQL Accumulate detail data

[ksaab2002]

Hi,

I have data that looks like:

ticket code
12345 a
12345 b
12345 c
45567 a
45567 b

How do I write a query to make it look like

ticket codes
12345 a,b,c
45567 a,b

thanks!

 

[markros]

Please read these two great links

http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html

PluralSight Learning Library

 

[ksaab2002]

Awesome - light dawns! thanks!

 

[markros]

And just in case also

http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column_Ordered

it has a link to another blog on this topic by Brad Schulz which is also an eye opener.

PluralSight Learning Library

 

[gmmastros]

If you are planning on using the UDF/ConcatenateCols method, beware... there is a bug in the code. You should add another condition to the where clause.

[tt]AND Col Is not NULL[/tt]

If you run the function without this where clause, you can get the wrong data (missing values). What's worse is that it will appear as though it is working. The worst kind of bug to have, in my opinion.

The "For XML Path" method does not have this problem. But, the UDF method is the only one that works with SQL2000.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should clarify that the bug is with the code at jahaines.blogspt.com. The code at wiki.lessthandot.com has the additional where clause.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Do you mean it doesn't have coalesce? Let me re-check the blog and I guess Adam needs to know.

PluralSight Learning Library

 

[markros]

You're right, there is a bug there.

PluralSight Learning Library

 

[gmmastros]

The original test data was....

[tt]
INSERT INTO t VALUES (1,'a');
INSERT INTO t VALUES (1,'b');
INSERT INTO t VALUES (1,'c');
INSERT INTO t VALUES (1,'d');
INSERT INTO t VALUES (2,'e');
INSERT INTO t VALUES (2,'f');
INSERT INTO t VALUES (3,'g');
INSERT INTO t VALUES (4,'h');
[/tt]

You get this:

[tt][blue]
id Cols
----------- -----------------
1 a,b,c,d
2 e,f
3 g
4 h
[/blue][/tt]

If you add...

[tt]
INSERT INTO t VALUES (1,NULL);
INSERT INTO t VALUES (1,'X');
INSERT INTO t VALUES (1,'Y');
[/tt]

You now get...

[tt][blue]
id Cols
----------- -----------------
1 X,Y
2 e,f
3 g
4 h
[/blue][/tt]

From id = 1, the a,b,c,d is missing. The problem is with the function and is easily fixed.

ALTER FUNCTION [dbo].[ConcatenateCols](@Id INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @RtnStr VARCHAR(MAX)

	SELECT @RtnStr = COALESCE(@RtnStr + ',','') + col
	FROM dbo.t
	WHERE id = @Id
              [!]And col > ''[/!]
	
	RETURN @RtnStr
END

By adding "And col > ''" you are filtering out the nulls and the empty strings. This problem is pretty well explained here: thread183-1159740


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

How did you find this thread? Just curious.

PluralSight Learning Library

 

[gmmastros]

I had it in my thread archive.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ksaab2002]

Thank you both, I was able to build it out, tested and works fine...

 

[markros]

Take a look also here

http://www.sqlmag.com/article/sql-server/multi-row-variable-assignment-and-order-by.aspx

(The UDF approach does not guarantee the correct order).

PluralSight Learning Library

 

[AdamHaines]

Hi All,

Thanks for pointing out the bug in the code. I have since modified the blog post to account for empty or unknown data,

http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

Thanks,
Adam Haines

 

[markros]

Thanks, Adam. It didn't take too long

PluralSight Learning Library