Hi,
I have a result set from a store procedure which looks like this.
Column1 Column2 Column3
A 1 lit1
A 1 Rep2
A 1 Reg3
B 2 ram1
B 2 lim2
I want to show data in my table like this:
Column1 Column2 Column3
A 1 lit1; Rep2;Reg3
B 2 ram1;lim2
Can anyone tell me how I can do this?
Thanks
Ashwini
Hi,
While getting the result from the database you can do one thing i.e write a Scalar valued function which will take the Column 1 value in that stored procedure write the logic to get the Column3 values separated by semi colon like this
ALTER FUNCTION [dbo].[GetColumn3]
(
@.Column1 bigint //here datatype of your column
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @.Result varchar(max)
SET @.Result = ''
--Select all Column3 into a string seperated by ';' for a column1
SELECT @.Result = @.Result + CONVERT(VARCHAR(MAX), p.Column3) + ', '
from //your table table1
WHERE
AND table1.(Your Column name here) = @.Column1
//This code to remove your last semi colon
IF @.Result != ''
SELECT @.Result= SUBSTRING(@.Result , 1, LEN(@.Result )-1)
RETURN @.Result
END
and call that function in your main Select query:
Select Column1,Column2 , dbo.GetColumn3(Column1) as Column3
From table1
joins
Where Conditions
Hope this helps.
sql