Query help. Need to Remove Comma at the end.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query help. Need to Remove Comma at the end.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-14 : 12:40:13

It looks simple but I try different way and can't get this to work, I have a list of id concat them and remove the last comma at the
end. Please see the results desire below. Thank you so much. SQL 2012

DECLARE @TempGroup TABLE
(
CounterId INT IDENTITY(1,1) NOT NULL,
HIXID VARCHAR(50) NULL
);

INSERT @TempGroup
SELECT 220100000000002
UNION
SELECT 220100000000003
UNION
SELECT 120000000000001
UNION
SELECT 150000000000007

DECLARE @List VARCHAR(8000)
SET @List = ''

SELECT @List = (@List + HIXID + ', ')
FROM @TempGroup
PRINT @List

--Result want:
120000000000001, 150000000000007, 220100000000002, 220100000000003

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 13:22:58

[code]
DECLARE @TempGroup TABLE
(
CounterId INT IDENTITY(1,1) NOT NULL,
HIXID VARCHAR(50) NULL
);

INSERT @TempGroup
SELECT 220100000000002
UNION
SELECT 220100000000003
UNION
SELECT 120000000000001
UNION
SELECT 150000000000007

SELECT STUFF((SELECT ',' + HIXID FROM @TempGroup ORDER BY CAST(HIXID AS bigint) FOR XML PATH('')),1,1,'')

output
--------------------------------
120000000000001,150000000000007,220100000000002,220100000000003

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-14 : 14:10:51

visakhm,
Thank you so much. I try similar logic before on my end but it did not work but your work.

Again, thank you.

quote:
Originally posted by visakh16

DECLARE @TempGroup TABLE
(
CounterId INT IDENTITY(1,1) NOT NULL,
HIXID VARCHAR(50) NULL
);

INSERT @TempGroup
SELECT 220100000000002
UNION
SELECT 220100000000003
UNION
SELECT 120000000000001
UNION
SELECT 150000000000007

SELECT STUFF((SELECT ',' + HIXID FROM @TempGroup ORDER BY CAST(HIXID AS bigint) FOR XML PATH('')),1,1,'')

output
--------------------------------
120000000000001,150000000000007,220100000000002,220100000000003

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Rajan Sahai
Starting Member

8 Posts

Posted - 2013-11-23 : 01:59:37

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources