How to concatenate fields and group them?

Question

How to concatenate fields and group them?

cliff.gettings

SSC Enthusiast

Points: 110
More actions
November 30, 2011 at 7:43 pm

#252904

Hi Guys,
My first visit here, look like an impressive resource.
I tried a search first but couldn't find the answer I need.
The problem I have is trying to concatenate a field and group by another field. Let me give you an example..
The table in the database looks like: (over 6000 entries)
Server | Service
-------------------
sev001 | hosting
sev001 | email
sev001 | info
sev002 | info
Sev002 | email
Sev003 | print
Sev003 | File
Sev003 | hosting
Sev003 | misc
Sev003 | scan
Sev004 | email
Sev004 | misc
The output I want to archive is like this:
Sev001 | hosting, email, info
Sev002 | info, email
Sev003 | print, file, hosting, misc, scan
Sev004 | email, misc
etc..
A colleague mentioned something about 'cursor' and 'stored procedures' but doesn't know how, and I don't have the first clue where to start with those. I'm fairly new to SQL and have only been writing some basic select queries.
Can anyone help?
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 1

hi

this is not my work, but maybe it will turn the light on for you...it comes from here, and i keep it around for an example.

http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx

ps. i will bet you lunch you get a dozen variations on this theme <g>

best

drew

USE tempdb;

GO

CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));

INSERT t1 values (1,'Jamie');

INSERT t1 values (1,'Joe');

INSERT t1 values (1,'John');

INSERT t1 values (2,'Sai');

INSERT t1 values (2,'Sam');

GO

select

id,

stuff((

select ',' + t.[name]

from t1 t

where t.id = t1.id

order by t.[name]

for xml path('')

),1,1,'') as name_csv

from t1

group by id

;

cliff.gettings SSC Enthusiast Points: 110 More actions · Answer 2

Thanks Drew,

Unfortunately, I still unsure what to do.. you refer to a csv file? I don't have this in a csv file, these are in a table in SQL server 2008 R2.

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 3

try substituting your table name for t1 in the example, and your column names too, and then run the code.

hth

drew

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

cliff.gettings (11/30/2011)
Thanks Drew,
Unfortunately, I still unsure what to do.. you refer to a csv file? I don't have this in a csv file, these are in a table in SQL server 2008 R2.

Read drew's post again.

He has demonstrated the technique used to concatenate columns into strings. See below:

BEGIN TRAN

CREATE TABLE #yourTable ([Server] CHAR(6), [Service] VARCHAR(7))

INSERT INTO #yourTable

SELECT 'sev001', 'hosting'

UNION ALL SELECT 'sev001', 'email'

UNION ALL SELECT 'sev001', 'info'

UNION ALL SELECT 'sev002', 'info'

UNION ALL SELECT 'Sev002', 'email'

UNION ALL SELECT 'Sev003', 'print'

UNION ALL SELECT 'Sev003', 'File'

UNION ALL SELECT 'Sev003', 'hosting'

UNION ALL SELECT 'Sev003', 'misc'

UNION ALL SELECT 'Sev003', 'scan'

UNION ALL SELECT 'Sev004', 'email'

UNION ALL SELECT 'Sev004', 'misc'

SELECT [Server], STUFF((SELECT ', ' + [Service]

FROM #yourTable r2

WHERE r2.[Server] = r1.[Server]

ORDER BY [Server]

FOR XML PATH('')), 1, 2, '') AS fmsg

FROM #yourTable r1

GROUP BY [Server]

ROLLBACK

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/