Collecting a number of text fields together using group by

Question

Collecting a number of text fields together using group by

Johnny D

Ten Centuries

Points: 1030
More actions
July 10, 2009 at 9:33 am

#132951

Hi,
Thanks in advance.
I have an id column and dexcription column, for example:-
id Description
1 Toys
1 Bikes
1 Marbles
2 Clothes
2 Helmets
3 Telescope
3 Paint
I would like to group them by the id into another table so that it will then be like the following:-
id Description
1 Toys Bikes Marbles
2 Clothes Helmets
3 Telescope Paint
I can't seem to figure out a way to do it because of the text element. Any ideas on how I can overcome this in sql. I am using MSSQL 2005.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

You could probably do it with a Pivot table. It might not present you the results the same way you want to see them though. EG:

DECLARE @TempTable TABLE

(

ID INT,

[Description] VARCHAR(100)

)

INSERT INTO @TempTable (ID, [Description])

SELECT 1, 'Toys'

UNION

SELECT 1, 'Bikes'

UNION

SELECT 1, 'Marbles'

UNION

SELECT 2, 'Clothes'

UNION

SELECT 2, 'Helmets'

UNION

SELECT 3, 'Telescope'

UNION

SELECT 3, 'Paint'

SELECT

ID,

[Toys] AS [Toys],

[Bikes] AS [Bikes],

[Marbles] AS [Marbles],

[Clothes] AS [Clothes],

[Helmets] AS [Helmets],

[Telescope] AS [Telescope],

[Paint] AS [Paint]

FROM

(

SELECT

ID,

[Description]

FROM @TempTable

) RS

PIVOT

(

MAX([Description])

FOR

[Description] IN ([Toys],[Bikes],[Marbles],[Clothes],[Helmets],[Telescope],[Paint])

) AS pvt

Results in:

IDToysBikesMarblesClothesHelmetsTelescopePaint

1ToysBikesMarblesNULLNULLNULLNULL

2NULLNULLNULLClothesHelmetsNULLNULL

3NULLNULLNULLNULLNULLTelescopePaint

ManicStar SSCoach Points: 17992 More actions · Answer 2

select Id,

substring((SELECT ( ', ' + Description )

FROM #Temp n1

where n1.id = #temp.id

ORDER BY n1.id

FOR XML PATH( '' )

), 3, 8000 )

FROM #temp

GROUP BY ID

#temp is the table I inserted all your sample rows into.

Johnny D Ten Centuries Points: 1030 More actions · Answer 3

Hi Guys,

Thanks for your reply and the queries provided but I don't think I was clear in my explanation on the table data.

The description field may be text of any kind not neccessarily the data I have put within ie. Toys, Bikes etc. Hence doing a union on fields with this data in it will not work.

Is there a way of grouping for any number of the same id column and collating the text together?

ManicStar SSCoach Points: 17992 More actions · Answer 4

If you insert all your sample data into #temp, then run my query, how does it not do what you are asking?

create table #temp

(

ID INT,

[Description] VARCHAR(100)

)

INSERT INTO #temp (ID, [Description])

SELECT 1, 'Toys'

UNION

SELECT 1, 'Bikes'

UNION

SELECT 1, 'Marbles'

UNION

SELECT 2, 'Clothes'

UNION

SELECT 2, 'Helmets'

UNION

SELECT 3, 'Telescope'

UNION

SELECT 3, 'Paint'

select Id,

substring((SELECT ( ' ' + [Description] )

FROM #temp n1

where n1.id = #temp.id

ORDER BY [Description] DESC

FOR XML PATH( '' )

), 3, 8000 )

FROM #temp

GROUP BY ID

Returns:

1Toys Marbles Bikes

2Helmets Clothes

3Telescope Paint

Johnny D Ten Centuries Points: 1030 More actions · Answer 5

Thanks ManicStar, this does the job, just what i needed..

Thanks. you've saved me some serious time.

ManicStar SSCoach Points: 17992 More actions · Answer 6

No Problem. This code is an implementation of a tip in this article:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Uh huh... the real question would be, "Why does this type of denormalization need to be done?" 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ManicStar SSCoach Points: 17992 More actions · Answer 8

Well, for one, I have had many report requests of the form 'pull all the notes for an order and put them in ONE column on a spreadsheet seperated by commas'.

:hehe:

John Hopkins SSC Enthusiast Points: 162 More actions · Answer 9

I get requests like that regularly, and use the same technique. Output is Excel or Crystal Reports. The only reason I'd drop it into a table is as an intermediate step to workaround the performance issues inherent in running queries against a (copy of) an OLTP database. It isn't really all that big, but it sure is slow ...

Regards,

John Hopkins