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 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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy