Wednesday, March 21, 2012

Data display in table

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

No comments:

Post a Comment