Comma Separated String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Comma Separated String

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2011-03-08 : 22:33:30

Hi all,

I am trying to form a comma separated string for my Primary keys. But not sure how to do so.

T_Staff_Profile
ID - Name - EmpID
1 - John - S123A
2 - John A. - S123A
3 - John Anderson - S123A
4 - Peter - S456B
5 - Peter M. - S456B
6 - Peter Mckoy - S456B
(I know the table design looks funny. It's a very old design and I'm doing this because of data migration).

The result I want to achieve is

EmpID - P.Keys
S123A - 1,2,3
S123B - 4,5,6

I tried using XML Path, but I can't separate it into 2 records.

SELECT STUFF((
Select ',' + CONVERT(varchar,ID,10)
from T_Staff_Profile
FOR XML PATH ('')
),1,1,'')

Any advise is very much appreciated.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-03-09 : 00:39:21

For each distinct EmpID: calculate the comma separated string.


SELECT E.EmpID,
       STUFF((SELECT ',' + CONVERT(VARCHAR, SP.ID, 10)
                FROM T_Staff_Profile AS SP
               WHERE SP.EmpID = E.EmpID
               ORDER BY SP.ID
                 FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)'), 1, 1, '') AS string
  FROM (SELECT DISTINCT EmpID
          FROM T_Staff_Profile) AS E;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-09 : 00:45:06

[code]
; with sample_data as
(
select [ID] = 1, [Name] = 'John', EmpID = 'S123A' union all
select [ID] = 2, [Name] = 'John A.', EmpID = 'S123A' union all
select [ID] = 3, [Name] = 'John Anderson', EmpID = 'S123A' union all
select [ID] = 4, [Name] = 'Peter', EmpID = 'S456B' union all
select [ID] = 5, [Name] = 'Peter M.', EmpID = 'S123A' union all
select [ID] = 6, [Name] = 'Peter Mckoy', EmpID = 'S456B'
)
select EmpID,
PKeys = stuff((select ',' + convert(varchar(10), [ID])
from sample_data x
where x.EmpID = s.EmpID
for xml path('')),
1, 1, '')
from sample_data s
group by EmpID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

hayashiryo
Starting Member

46 Posts

Posted - 2011-03-09 : 07:39:34

quote:
Originally posted by khtan

[code]
; with sample_data as
...

Worked like a charm

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources