CONCATINATE COLUMNS

Question

Post reply

CONCATINATE COLUMNS

Ram:)

SSCrazy

Points: 2246
More actions
September 28, 2010 at 4:11 pm

#224447

Hi Everyone,
I have some performance issue while concatinating columns.
Please help me on best way to do this.
Sample code as below.
DECLARE @test-2 TABLE
(ID INT,
NAM VARCHAR(50))
INSERT INTO @test-2
SELECT 1,'RAM'
UNION ALL
SELECT 1,'PRASAD'
UNION ALL
SELECT 2,'SQL'
UNION ALL
SELECT 2,'SERVER'
UNION ALL
SELECT 2,'CENTRAL'
UNION ALL
SELECT 3,'FAMOUS'
UNION ALL
SELECT 3,'BOOK'
SELECT * FROM @test-2
Result should be:
ID RESULT
1 RAM PRASAD
2 SQL SERVER CENTRAL
3 FAMOUS BOOK
Regards
Ram..
🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

touchmeknot SSCommitted Points: 1769 More actions · Answer 1

DECLARE @test-2 TABLE

(ID INT,

NAM VARCHAR(50))

DECLARE @RESULT TABLE

(ID INT,

RESULT VARCHAR(50))

INSERT INTO @test-2

SELECT 1,'RAM'

UNION ALL

SELECT 1,'PRASAD'

UNION ALL

SELECT 2,'SQL'

UNION ALL

SELECT 2,'SERVER'

UNION ALL

SELECT 2,'CENTRAL'

UNION ALL

SELECT 3,'FAMOUS'

UNION ALL

SELECT 3,'BOOK'

DECLARE @Id int, @i int

DECLARE @Name varchar(100)

SET @i = 1

SELECT * FROM @test-2

SELECT @Id = MAX(Id) FROM @test-2

WHILE(@i <= @Id)

BEGIN

SET @Name = ''

SELECT @Name = COALESCE(@Name + NAM + ' ','')

FROM @test-2

WHERE ID = @i

INSERT INTO @RESULT VALUES (@i,@Name)

SET @i = @i + 1

END

SELECT * FROM @RESULT

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Using SQL Server 200, no WHILE loop required:

DECLARE @test-2 TABLE

(ID INT,

NAM VARCHAR(50));

INSERT INTO @test-2

SELECT 1,'RAM'

UNION ALL

SELECT 1,'PRASAD'

UNION ALL

SELECT 2,'SQL'

UNION ALL

SELECT 2,'SERVER'

UNION ALL

SELECT 2,'CENTRAL'

UNION ALL

SELECT 3,'FAMOUS'

UNION ALL

SELECT 3,'BOOK';

SELECT * FROM @test-2;

select distinct

o.ID,

(STUFF((select ', ' + i.NAM from @test-2 i where o.ID = i.ID for xml path('')),1,2,'')) as Result

from

@test-2 o;

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 3

Lynn Pettis (9/28/2010)
Using SQL Server 200, no WHILE loop required:
DECLARE @test-2 TABLE
(ID INT,
NAM VARCHAR(50));
INSERT INTO @test-2
SELECT 1,'RAM'
UNION ALL
SELECT 1,'PRASAD'
UNION ALL
SELECT 2,'SQL'
UNION ALL
SELECT 2,'SERVER'
UNION ALL
SELECT 2,'CENTRAL'
UNION ALL
SELECT 3,'FAMOUS'
UNION ALL
SELECT 3,'BOOK';
SELECT * FROM @test-2;
select distinct
o.ID,
(STUFF((select ', ' + i.NAM from @test-2 i where o.ID = i.ID for xml path('')),1,2,'')) as Result
from
@test-2 o;

That's extremely interesting and I am learning something here! Now, I was wondering how you would do it without the ',', as commenting that out shows the XML tags <NAM> </NAM> around the words. I myself would have done it with a while loop so this is definitely an eye opener!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Remove the comma leaving the space and change the STUFF parameters 1,2 to 1,1 and see what happens.

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 5

Very very interesting! Thank you! I now have some research to do to understand that code! 😛

I honestly have never seen or used those commands before...

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 6

A CROSS APPLY variation

;WITH cte AS

(

SELECT DISTINCT ID

FROM @test-2

)

SELECT cte.ID, Z.RESULT

FROM cte

CROSS APPLY

(

SELECT

STUFF((SELECT SPACE(1) + NAM

FROM @test-2 AS TEST

WHERE TEST.ID = cte.ID

FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, SPACE(0))

) AS Z (RESULT)

drew.allen SSC Guru Points: 76996 More actions · Answer 7

Lynn Pettis (9/29/2010)
Remove the comma leaving the space and change the STUFF parameters 1,2 to 1,1 and see what happens.

Actually, if you remove the comma, you can remove the STUFF by rearranging the elements (though you may need to throw in an RTrim).

select distinct

o.ID,

(select i.NAM + ' ' from @test-2 i where o.ID = i.ID for xml path('')) as Result

from

@test-2 o;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

touchmeknot SSCommitted Points: 1769 More actions · Answer 8

Interesting answers by Lynn , Drew and Steve.

Its nice to know of that solution. May I ask for some links/resources to learn XML ?

Ram:) SSCrazy Points: 2246 More actions · Answer 9

Thanks Everybody..

All these suggestions are good..

But It is taking more time ,suppose if we have around 1,00,000 rows.

Even i am trying to get better solutions for that..

Regards

Ram..

🙂

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 10

Ram:) (9/30/2010)
Thanks Everybody..
All these suggestions are good..
But It is taking more time ,suppose if we have around 1,00,000 rows.
Even i am trying to get better solutions for that..
Regards
Ram..

Have tested CROSS APPLY query on million row temporary table rather than a table variable and it takes about 1 second

How fast do you want it to be?

;WITH cte AS

(

SELECT DISTINCT ID

FROM #TEST

)

SELECT cte.ID, Z.RESULT

FROM cte

CROSS APPLY

(

SELECT

STUFF((SELECT SPACE(1) + NAM

FROM #TEST

WHERE #TEST.ID = cte.ID

FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, SPACE(0))

) AS Z (RESULT)

touchmeknot SSCommitted Points: 1769 More actions · Answer 11

I agree with Steve. Performance is definitely good with CTE's.

Check/Create indexes, that should help.

EDIT:

Also don't use Table variable if you have to process so many rows. That might be the reason.

Ram:) SSCrazy Points: 2246 More actions · Answer 12

Hey Steve,

Thank you very much.. I tested all of my production scenorios.. Working really good.. Thanks again

Ram...

🙂

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 13

steve-893342

SSCarpal Tunnel

Points: 4439

October 1, 2010 at 3:57 pm

#1230598

Thanks for the feedback folks. Much appreciated:-)