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: 442447 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: 442447 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442447

September 29, 2010 at 12:19 pm

#1229260

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

g33kspeak

Mr or Mrs. 500

Points: 531

September 29, 2010 at 12:24 pm

#1229262

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: 76776 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

touchmeknot

SSCommitted

Points: 1769

September 30, 2010 at 10:25 am

#1229723

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

Ram:)

SSCrazy

Points: 2246

September 30, 2010 at 11:25 am

#1229760

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)