September 30, 2010 at 11:44 pm
i have table like this
Poistion Name
Manager Scott
manager Jhon
Manger adam
Accountant marry
Accountant zee
and i want to display them like
poistion name
manager Scott, Jhon,Adam
acountant marry,Zee
Thansk
October 1, 2010 at 2:24 am
This was removed by the editor as SPAM
October 1, 2010 at 4:18 am
USE <DatabaseName>
Go
CREATE TABLE EmpTable
(Poistion Varchar(50), Name Varchar(50))
GO
INSERT EmpTable
VALUES ('Manager', 'Scott')
INSERT EmpTable
VALUES('manager', 'Jhon')
INSERT EmpTable
VALUES('Manager','adam')
INSERT EmpTable
VALUES('Accountant','marry')
INSERT EmpTable
VALUES('Accountant','zee')
GO
-- Funcation
CREATE FUNCTION [dbo].[fn_ReturnName]
(@Poistion Varchar(50))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Name VARCHAR(MAX)
SELECT @Name = COALESCE(@Name+', ' ,'') + Name
FROM EmpTable WHERE Poistion=@Poistion
ORDER BY Name
RETURN @Name
END
GO
--Sample Query 1
SELECT DISTINCT Poistion + ' ' + dbo.fn_ReturnName(Poistion) As EmpName
FROM EmpTable Order By 1 ASC
--Sample Query 2
SELECT DISTINCT Poistion
,dbo.fn_ReturnName(Poistion) As EmpName
FROM EmpTable Order By Poistion ASC
Ram
MSSQL DBA
October 1, 2010 at 6:31 am
No I did not try XML path....but its working ..thats really what I want..Bundle of thanks
October 1, 2010 at 6:33 am
Thanks ..Its what I want ...I was thinking the whole day about this ..thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply