How to Perform this Concept [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	@ 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

@ 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

0	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

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

Latest Posts