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 PRODUCTPRINT @commaSeparatedVal
Comments
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