Working With comma Separated values

Question

Post reply

Working With comma Separated values

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
May 17, 2010 at 5:55 am

#147063

This is scripts of three tables.
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Empmaster](
[EmpId] [int] NOT NULL,
[EmpName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Salary] [int] NULL,
CONSTRAINT [PK_Empmaster] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Test]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DeptMaster](
[DeptId] [int] NOT NULL,
[Deptname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_DeptMaster] PRIMARY KEY CLUSTERED
(
[DeptId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmpMapping](
[EmpId] [int] NOT NULL,
[DeptId] [int] NOT NULL
) ON [PRIMARY]
This is the data of three tables
EmpIdEmpNameSalary
111Malli20000
112Reddy10000
113Rahim5000
114naga10000
DeptIdDeptname
1Developement
2testing
3Maintanance
4Database
5Marketing
EmpIdDeptId
1111
1112
1113
1124
1125
1135
1142
1143
Expected Result
EmpIdEmpnameSalaryDeptnames
111Malli20000Development,testing,Maintanance
112Reddy10000Database,Marketing
113Rahim5000Marketing
114naga10000testing,Maintanance
The above is the Expected result i want.Please help me on this.
Iam getting comma Separated values but that is not exacted as i am Expecting.
Malleswarareddy
I.T.Analyst
MCITP(70-451)

Viewing 12 posts - 1 through 11 (of 11 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Malli, this will get you there

;WITH CTE AS

(

SELECT Map.EMpId, Dep.deptName

FROM [EmpMapping] Map

LEFT JOIN [DeptMaster] dep

ON Map.DeptId = dep.DeptId

),

Concat_Data AS

(

SELECT dep1.empid,

STUFF( ( SELECT ',' + dep2.deptName

FROM CTE dep2

WHERE dep2.empid = dep1.empid

ORDER BY dep2.deptName

FOR XML PATH('')

)

, 1, 1, '') AS Concat_Values

FROM CTE dep1

GROUP BY dep1.empid

)

SELECT Emp.EmpID, Emp.EmpName, Emp.Salary ,C.Concat_Values Depts

FROM [Empmaster] EMP

LEFT JOIN Concat_Data C

ON Emp.EmpId = C.EmpId

Tell us if this worked for you!

~Edit: Beautified the code; Fixed the trailing/leading comma!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

You have provided the DDLs, alright, but what about the INSERT INTO scirpts for the sample data! It took me about 15 mins to format the data u had given and then code for the requirement! Please provide the whole set-up in a ready-to-use manner which will save a lot of time for both of us. One with 837 posts to his/her name must take care of this. 🙂

For any other who are planning to work on this , here is the complete set-up:

-- My test DB

Use Scratch

GO

IF OBJECT_ID('Empmaster') IS NOT NULL

DROP TABLE [dbo].[Empmaster]

CREATE TABLE [dbo].[Empmaster](

[EmpId] [int] NOT NULL,

[EmpName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Salary] [int] NULL,

CONSTRAINT [PK_Empmaster] PRIMARY KEY CLUSTERED

(

[EmpId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

IF OBJECT_ID('DeptMaster') IS NOT NULL

DROP TABLE [dbo].[DeptMaster]

CREATE TABLE [dbo].[DeptMaster](

[DeptId] [int] NOT NULL,

[Deptname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_DeptMaster] PRIMARY KEY CLUSTERED

(

[DeptId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

IF OBJECT_ID('EmpMapping') IS NOT NULL

DROP TABLE [dbo].[EmpMapping]

CREATE TABLE [dbo].[EmpMapping](

[EmpId] [int] NOT NULL,

[DeptId] [int] NOT NULL

) ON [PRIMARY]

Sample data

INSERT INTO [Empmaster] (EmpId, EmpName ,Salary)

select 111 , 'Malli', 20000 union all

select 112 , 'Reddy', 10000 union all

select 113 ,'Rahim', 5000 union all

select 114 ,'naga' , 10000

select EmpId, EmpName ,Salary from [Empmaster]

INSERT INTO [DeptMaster] ( DeptId, Deptname)

select 1 ,'Developement' union all

select 2 ,'testing' union all

select 3 ,'Maintanance' union all

select 4 ,'Database' union all

select 5 ,'Marketing'

select DeptId, Deptname from [DeptMaster]

INSERT INTO [dbo].[EmpMapping] (EmpId, DeptId)

select 111, 1 union all

select 111, 2 union all

select 111, 3 union all

select 112, 4 union all

select 112, 5 union all

select 113, 5 union all

select 114, 2 union all

select 114, 3

😎

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

You are good.I thought that with For Xml it will work.

But i did not applied that logic.Beacause i did not written that queries using for xml.so did not got the solution.but can we get the same result with out using for xml.Because i tried it with out using for xml, but failed to get result exactly.

But your solution is good. but for last value in column there is comma. we want to remove that comma also. can we got the result without using for XML.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

malleswarareddy_m (5/17/2010)
But your solution is good. but for last value in column there is comma. we want to remove that comma also

Please revisit the code i had given, i have fixed the trailing comma issue with the new code. 🙂

. can we got the result without using for XML.

There are many ways. I dont remember, but Paul White once coded a beauty using CLR. I will try to find that out for you.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

I thought that want to put insert scripts but the table is available.that's why i copy & pasted.sorry for wasting your time.next time i will put complete script for queries.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

malleswarareddy_m (5/17/2010)
I thought that want to put insert scripts but the table is available.that's why i copy & pasted.sorry for wasting your time.next time i will put complete script for queries.

No Mr.Reddy, dont be apologetic! Don't feel sorry also! I am never tired to help! It is "never wasting my time"! Am happy to help always!

In future, please make sure tat INSERTs are present, that wud suffice!

Cheers and happy learning! 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 7

good job on providing the table structures and the desired output;

if you had posted the INSERT INTO for the data, your question would be perfect!

to convert rows into a comma seperated list, you want to use the FOR XML to get teh data formattted the way you expect.

here's teh solution:

CREATE TABLE [dbo].[Empmaster](

[EmpId] [int] NOT NULL,

[EmpName] [varchar](50) NULL,

[Salary] [int] NULL,

CONSTRAINT [PK_Empmaster] PRIMARY KEY CLUSTERED

(

[EmpId] ASC

)

GO

CREATE TABLE [dbo].[DeptMaster](

[DeptId] [int] NOT NULL,

[Deptname] [varchar](50) NULL,

CONSTRAINT [PK_DeptMaster] PRIMARY KEY CLUSTERED

(

[DeptId] ASC

)

CREATE TABLE [dbo].[EmpMapping](

[EmpId] [int] NOT NULL,

[DeptId] [int] NOT NULL

)

INSERT INTO Empmaster

(EmpId,EmpName,Salary)

SELECT '111','Malli','20000' UNION ALL

SELECT '112','Reddy','10000' UNION ALL

SELECT '113','Rahim','5000' UNION ALL

SELECT '114','naga','10000'

INSERT INTO DeptMaster

(DeptId, Deptname)

SELECT '1','Developement' UNION ALL

SELECT '2','testing' UNION ALL

SELECT '3','Maintanance' UNION ALL

SELECT '4','Database' UNION ALL

SELECT '5','Marketing'

--SELECT EmpMapping.EmpId, EmpMapping.DeptId,DeptMaster.Deptname FROM EmpMapping INNER JOIN DeptMaster ON EmpMapping.DeptId=DeptMaster.DeptId

INSERT INTO EmpMapping

(EmpId,DeptId)

SELECT '111','1' UNION ALL

SELECT '111','2' UNION ALL

SELECT '111','3' UNION ALL

SELECT '112','4' UNION ALL

SELECT '112','5' UNION ALL

SELECT '113','5' UNION ALL

SELECT '114','2' UNION ALL

SELECT '114','3'

--EmpId Empname Salary Deptnames

SELECT EmpId,

Empname,

Salary ,stuff(( SELECT ',' + Deptname

FROM (SELECT

EmpMapping.EmpId,

EmpMapping.DeptId,

DeptMaster.Deptname

FROM EmpMapping

INNER JOIN DeptMaster

ON EmpMapping.DeptId=DeptMaster.DeptId) s2

WHERE s2.EmpId= s1.EmpId --- must match GROUP BY below

ORDER BY Deptname

FOR XML PATH('')

),1,1,'') as Deptnames

FROM (SELECT

EmpMapping.EmpId,

Empmaster.EmpName,

Empmaster.Salary,

EmpMapping.DeptId,

DeptMaster.Deptname

FROM EmpMapping

INNER JOIN DeptMaster

ON EmpMapping.DeptId=DeptMaster.DeptId

INNER JOIN Empmaster

ON EmpMapping.EmpId = Empmaster.EmpId

) s1

GROUP BY s1.EmpId,EmpName,Salary --- without GROUP BY multiple rows are returned

ORDER BY s1.EmpId,EmpName,Salary

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

Another variant of the code. This will also perform the same

;WITH Master_CTE AS

(

SELECT

Emp.EmpID, Emp.EmpName, Emp.Salary ,dep.DeptName

FROM

[Empmaster] EMP

LEFT JOIN [EmpMapping] Map

ON Emp.EmpId = Map.EmpId

LEFT JOIN [DeptMaster] dep

ON dep.deptId = Map.Deptid

)

SELECT A.EmpID, A.EmpName, A.Salary ,

STUFF( ( SELECT ',' + B.DeptName

FROM Master_CTE B

WHERE B.EmpID = A.EmpID

ORDER BY B.DeptName

FOR XML PATH('')

)

, 1, 1, '') AS DeptName

FROM Master_CTE A

GROUP BY

A.EmpID, A.EmpName, A.Salary

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

There is another way using T-SQL to concatentate values that needs to be used in versions prior to SQL Server 2005, problem is that it does not scale as well as the FOR XML PATH method.

There also was a thread devoted to numerous methods of doing this as well, but I don't have a reference to it handy. Hopefully someone else may have it and could provide it.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 10

thanks for quick reply.This is the interview question i had faced in two interviews. and i told to interviewer that we can acheive that using For XML.but i did not written that.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

:Whistling:

--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)