How to Perform this Concept [Resolved]

Posted by Jayakumars under C# on 3/1/2017 | Points: 10 | Views : 433 | Status : [Member] [MVP] | Replies : 2
Hi

Query Sql server Only

CREATE TABLE ONE
(
EMPID INT,
EMPNAME VARCHAR(40),
)

CREATE TABLE ONE1
(
ID INT,
DEPTNAME VARCHAR(40),
REFID INT
)

--insert into ONE values(1,'AA1')
--insert into ONE values(2,'AA2')
--insert into ONE values(3,'AA3')
--insert into ONE values(4,'AA4')
select * from ONE

--insert into ONE1 values(1,'Dept1',1)
--insert into ONE1 values(1,'Dept1',2)
--insert into ONE1 values(2,'Dept2',3)
--insert into ONE1 values(2,'Dept2',4)
--insert into ONE1 values(3,'Dept3',5)


select * from ONE
select * from ONE1

-- I Need output this
1 Dept1 1,2
2 Dept2 2,3,4
3 Dept3 5

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 3/1/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@ Jayakumars Sir, Please try this

--Table Schema with data
CREATE TABLE #ONE
(
EMPID INT,
EMPNAME VARCHAR(40),
)

INSERT INTO #ONE VALUES(1,'AA1')
INSERT INTO #ONE VALUES(2,'AA2')
INSERT INTO #ONE VALUES(3,'AA3')
INSERT INTO #ONE VALUES(4,'AA4')

CREATE TABLE #ONE1
(
ID INT,
DEPTNAME VARCHAR(40),
REFID INT
)

INSERT INTO #ONE1 VALUES(1,'Dept1',1)
INSERT INTO #ONE1 VALUES(1,'Dept1',2)
INSERT INTO #ONE1 VALUES(2,'Dept2',3)
INSERT INTO #ONE1 VALUES(2,'Dept2',4)
INSERT INTO #ONE1 VALUES(3,'Dept3',5)

------------------------------------------------------------------------------

--Query
SELECT DISTINCT
ID
,DEPTNAME
,STUFF((SELECT ',' + CAST(o1.REFID AS VARCHAR(20))
FROM #ONE1 o1
WHERE o1.ID=o.ID
FOR XML PATH('')),1,1,'') AS RefIDs
FROM #ONE1 o

------------------------------------------------------------------------------

--Clean up objects
DROP TABLE #ONE
DROP TABLE #ONE1


/*
ID	DEPTNAME	RefIDs
1 Dept1 1,2
2 Dept2 3,4
3 Dept3 5




*/

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 3/1/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use STUFF function to achevie your requirement
SELECT ID,DEPTNAME , STUFF((SELECT  ',' + Cast(REFID AS Varchar(10))

FROM ONE1 EE
WHERE EE.ID=E.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS listStr
FROM ONE1 E
GROUP BY E.ID,E.DEPTNAME


You can find a working demo of above query here : http://sqlfiddle.com/#!3/b9b2f/1

Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response