Thursday, May 29, 2008

Comma separated list of values of single Database table field

Many times you need to create a comma seperated list of values in a table. Here is a line of T-SQL solution to get comma separated list of values of single field of a database table.
DECLARE @commaSeparatedVal AS VARCHAR(MAX);
SELECT @commaSeparatedVal = ISNULL(@commaSeparatedVal +',','') + CONVERT(VARCHAR,[SKU]) FROM PRODUCT
PRINT @commaSeparatedVal

1 comment:

nageswara said...

I Am using the following code to
get solution for "SPLITTING CSV COLUMN TO MULTIPLE COLUMNS IN 1 ROW"

How to hide the column with null value?

in the following code the tempararyEmail3 has null value.
My requirement is Move the TempararyEmail4 column content to TempararyEmail3 and remove the TempararyEmail4 from the list.

Please give me some solution.

Thanks in advance.

Rao


CREATE TABLE [dbo].[Info ](
[ID] [varchar](10) NOT NULL,
[email] [varchar](128) NULL)




INSERT INTO Info
(ID
,email)
VALUES
(1, 'nr@test2.com,np@rr.com,rr@rr.com')





CREATE procedure getInfo
(
@ID INT
)
AS
BEGIN
Declare @tempEmail VARCHAR(128)
Declare @tempEmail1 VARCHAR(128)
Declare @tempEmail2 VARCHAR(128)
Declare @tempEmail3 VARCHAR(128)
Declare @tempEmail4 VARCHAR(128)

SELECT @tempEmail = Email
FROM
Info WITH (NOLOCK)
where
siteID=@SiteID

Declare @Pos int
Declare @SPos int
Set @Pos = CharIndex(',',@tempEmail,0)
Set @SPos = 1
IF @Pos > 0
Begin
SET @tempEmail1 = Substring(@tempEmail,@SPos,@Pos-@SPos)
Set @SPos = @Pos + 1
Set @Pos = CharIndex(',',@tempEmail,@Pos+1)
End
IF @Pos > 0
Begin
SET @tempEmail2 = Substring(@tempEmail,@SPos,@Pos-@SPos)
Set @SPos = @Pos + 1
Set @Pos = CharIndex(',',@tempEmail,@Pos+1)
End
IF @Pos > 0
Begin
SET @tempEmail3 = Substring(@tempEmail,@SPos,@Pos-@SPos)
Set @SPos = @Pos + 1
Set @Pos = CharIndex(',',@tempEmail,@Pos+1)
End
--IF @Pos > 0
--Begin
SET @tempEmail4 = Substring(@tempEmail,@SPos,DataLength(@tempEmail))
--END


SELECT
ID,
@tempEmail1 AS Email1,
@tempEmail2 AS Email2,
@tempEmail3 AS Email3,
@tempEmail4 AS Email4
FROM
Info WITH (NOLOCK)
where
siteID=@SiteID

END


exec Info 1