concatenate

Question

concatenate

CooLDBA

Hall of Fame

Points: 3301
More actions
January 12, 2010 at 3:25 pm

#137483

ID Key New field (Result)
11307611878611878
11343609037609037, 609062, 611827, 612433
11343609062609037, 609062, 611827, 612433
11343611827609037, 609062, 611827, 612433
11343612433609037, 609062, 611827, 612433
11364602314602314
11467625039625039, 616913, 616942
11467616913625039, 616913, 616942
11467616942625039, 616913, 616942
I need help.
So I need to group the id if it is the same then concatenate the keys with comma delimited in the new field, do you know how? sample is in the new field (result)

Viewing 7 posts - 1 through 6 (of 6 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

The following should at least help you to get you started (please note how I set up sample data in a ready to use format...)

DECLARE @t TABLE (ID INT ,Key_ char(6))

INSERT INTO @t

SELECT 11307 ,'611878' UNION ALL -- 611878

SELECT 11343 ,'609037' UNION ALL -- 609037, 609062, 611827, 612433

SELECT 11343 ,'609062' UNION ALL -- 609037, 609062, 611827, 612433

SELECT 11343 ,'611827' UNION ALL -- 609037, 609062, 611827, 612433

SELECT 11343 ,'612433' UNION ALL -- 609037, 609062, 611827, 612433

SELECT 11364 ,'602314' UNION ALL -- 602314

SELECT 11467 ,'625039' UNION ALL -- 625039, 616913, 616942

SELECT 11467 ,'616913' UNION ALL -- 625039, 616913, 616942

SELECT 11467 ,'616942' -- 625039, 616913, 616942

SELECT

id,

stuff(( SELECT ', ' + Key_ FROM @t t2 WHERE t2.id = t1.id FOR XML path('')),1,2,'')

FROM

@t t1

GROUP BY

id

/* result set

id(No column name)

11307611878

11343609037, 609062, 611827, 612433

11364602314

11467625039, 616913, 616942

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jcdyntek Ten Centuries Points: 1153 More actions · Answer 2

here you go

table testids has two fields

id,newfield

WITH CTE ( ID, List, field, length )

AS ( SELECT ID, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0

FROM testids

GROUP BY ID

UNION ALL

SELECT p.ID, CAST( list +

CASE WHEN length = 0 THEN '' ELSE ', ' END + cast(p.newfield as varchar(10)) AS VARCHAR(8000) ),

CAST( newfield AS VARCHAR(8000)), length + 1

FROM CTE c

INNER JOIN testids p

ON c.ID = p.ID

WHERE cast(p.newfield as varchar(10)) > c.field )

SELECT ID, list

FROM ( SELECT ID, list,

RANK() OVER ( PARTITION BY ID ORDER BY length DESC )

FROM CTE ) D ( ID, list, rank )

WHERE rank = 1 ;

11307611878

11343609037, 609062, 611827, 612433

11364602314

11467616913, 625039

Paul White SSC Guru Points: 150467 More actions · Answer 3

Perfect Lutz!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 4

Paul White (1/12/2010)
Perfect Lutz!

Thank you, Paul! :blush:

It's starting to pay off hanging around here for a while.... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

CooLDBA Hall of Fame Points: 3301 More actions · Answer 5

CooLDBA

Hall of Fame

Points: 3301

January 13, 2010 at 9:10 am

#1103130

I end up created a function instead.

Paul White SSC Guru Points: 150467 More actions · Answer 6

CooLDBA (1/13/2010)
I end up created a function instead.

Did you? Would you care to share it?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi