July 2, 2009 at 11:45 pm
In my SQL 2005 datatbase I have a table Empl_Dept which has 3 columns EM_ID, EM_DeptName, EM_EmpId
where EM_ID is a auto generated identity column. In this table there will be multiple rows of the same department name (EM_DeptName) for which there will be the Employee id who is in the dept(EM_EmpId)
The sample data of this table is given in the script below
CREATE TABLE [Empl_Dept] (
[EM_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EM_DeptName] [varchar] (255) NOT NULL ,
[EM_EmpId] [int] NOT NULL ,
) ON [PRIMARY]
GO
insert into Empl_Dept values('Sales',111)
insert into Empl_Dept values('Sales',112)
insert into Empl_Dept values('Process',113)
insert into Empl_Dept values('Sales',114)
insert into Empl_Dept values('Process',115)
I want the output data in the format where all the employee ids are displayed in one column separated by a comma(,) like below
EM_DeptName EM_EmpId
Sales 111,112,114
Process113,115
Thanks
July 3, 2009 at 1:10 am
Hi, heres a link to get you started
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
July 3, 2009 at 1:10 am
Here you go.
CREATE TABLE [dbo].[Empl_Dept] (
[EM_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EM_DeptName] [varchar] (255) NOT NULL ,
[EM_EmpId] [int] NOT NULL ,
) ON [PRIMARY]
GO
insert into dbo.Empl_Dept values('Sales',111);
insert into dbo.Empl_Dept values('Sales',112);
insert into dbo.Empl_Dept values('Process',113);
insert into dbo.Empl_Dept values('Sales',114);
insert into dbo.Empl_Dept values('Process',115);
GO
select
*
from
dbo.Empl_Dept;
go
select distinct
EM_DeptName,
stuff((select
',' + cast(EM_EmpId as varchar(8))
from
dbo.Empl_Dept ed1
where
ed1.EM_DeptName = ed.EM_DeptName
for xml path ('')),1,1,'')
from
dbo.Empl_Dept ed;
go
drop table dbo.Empl_Dept;
go
July 3, 2009 at 2:00 am
This is what i did following the link from Dave. Worth checking out the link. Thanks Dave.
; WITH CTE ( EM_EmpId, EM_DeptName, seq )
AS ( SELECT E1.EM_EmpId, E1.EM_DeptName,
ROW_NUMBER() OVER (PARTITION BY EM_DeptName ORDER BY EM_EmpId)
FROM Empl_Dept E1)
SELECT EM_DeptName,
MAX( CASE seq WHEN 1 THEN cast(EM_EmpId as varchar) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN cast(EM_EmpId as varchar) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN cast(EM_EmpId as varchar) ELSE '' END )
FROM CTE
GROUP BY EM_DeptName;
July 3, 2009 at 2:05 am
sarvesh singh (7/3/2009)
This is what i did following the link from Dave. Worth checking out the link. Thanks Dave.
; WITH CTE ( EM_EmpId, EM_DeptName, seq )
AS ( SELECT E1.EM_EmpId, E1.EM_DeptName,
ROW_NUMBER() OVER (PARTITION BY EM_DeptName ORDER BY EM_EmpId)
FROM Empl_Dept E1)
SELECT EM_DeptName,
MAX( CASE seq WHEN 1 THEN cast(EM_EmpId as varchar) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN cast(EM_EmpId as varchar) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN cast(EM_EmpId as varchar) ELSE '' END )
FROM CTE
GROUP BY EM_DeptName;
That's only going to work if there are no more than 3 employee IDs per department. If I add more sample data
insert into dbo.Empl_Dept values('Sales',111);
insert into dbo.Empl_Dept values('Sales',112);
insert into dbo.Empl_Dept values('Process',113);
insert into dbo.Empl_Dept values('Sales',114);
insert into dbo.Empl_Dept values('Process',115);
insert into dbo.Empl_Dept values('Sales',116);
insert into dbo.Empl_Dept values('Sales',117);
insert into dbo.Empl_Dept values('Sales',118);
insert into dbo.Empl_Dept values('Sales',119);
your query returns
[font="Courier New"]
Process 113, 115,
Sales 111, 112, 114[/font]
when it should return
[font="Courier New"]
Process 113, 115,
Sales 111, 112, 114, 116, 117, 118, 119[/font]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 2:42 am
How can i tackle that Gail.
July 3, 2009 at 2:47 am
Lynn's response above should be exactly what you need.
The link i gave also describes this method.
July 3, 2009 at 2:49 am
Thanks dave, but i was wondering if it can be achieved using CTE as used in my query.
July 3, 2009 at 2:58 am
If you expand your seqno list the yes, but you will still have an upper limit.
The XML method is the best in terms of performance. Its not without problems though. & , get translated to &_amp; ,&_lt; and &_gt; (with the underscores though)
July 3, 2009 at 3:43 am
Is there a way to use the seq number in such a way that i do not need to specify each row. It would be tidious if there are 100 rows as gail mentioned.
I am not that familiar with XML, hence a bit reluctant in using it, Something that i do need to learn.
Can it be done without using XML?
July 3, 2009 at 3:47 am
You dont need to know XML. It really has nothing to do with it , it's just a handy cheat to concatenate strings.
July 3, 2009 at 4:02 am
I guess i just have to read up on 'Stuff' and XML path.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply