Urgent Help on below scenario.

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 2012 Forums

Transact-SQL (2012)

Urgent Help on below scenario.

Author

Topic

amit120k
Starting Member

16 Posts

Posted - 2015-03-18 : 01:46:59

table structure with data:

Name Age Role Department
----- --- ---- ----------
Amit 30 r1 d1
Ani 30 r2 d2
Amit 30 r3 d3
shik 27 r4 d4
Ani 30 r5 d5

Output should be like:(without Using XML PATH)

Name Age Role Department
----- --- ---- ----------
Amit 30 r1,r3 d1,d3
Ani 30 r2,r5 d2,d5
shik 27 r4 d4

Thanks in Advance..

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 02:35:28

[code]
SELECT

Name,Age
,STUFF(dbo.svf_RoleList(A.Name),1,1,'') AS Role
,STUFF(dbo.svf_DepartmentList(A.Name),1,1,'') AS Department
FROM
(SELECT
Name,Age
FROM
Role_Department
GROUP BY Name,Age) AS A
[/code]

output:
[code]
Name Age Role Department
Amit 30 r1,r3 d1,d3
Ani 30 r2,r5 d2,d5
shik 27 r4 d4
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 02:36:15

and the definition for scalar functions:



CREATE FUNCTION dbo.svf_RoleList
(
	@I_Name VARCHAR(500)
)
RETURNS VARCHAR(500)
AS
BEGIN
	
	DECLARE
		@vcRoleList AS VARCHAR(500)='';		

	SELECT
		@vcRoleList = @vcRoleList + ',' + Role
	FROM
		Role_Department
	WHERE
		Name =@I_Name;
	
	RETURN @vcRoleList;

END
GO

CREATE FUNCTION dbo.svf_DepartmentList
(
	@I_Name VARCHAR(500)
)
RETURNS VARCHAR(500)
AS
BEGIN
	
	DECLARE
		@vcDepartmentList AS VARCHAR(500)='';

	SELECT
		@vcDepartmentList = @vcDepartmentList + ',' + Department
	FROM
		Role_Department
	WHERE
		Name =@I_Name;
	
	RETURN @vcDepartmentList;

END
GO

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources