I spend too long on finding the solution for this problem…
Basically I had a table which looked like this
What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
Solution
Initially I tried to write one SELECT:
SELECT
STUFF
(
(
SELECT ', ' + cast(id AS NVARCHAR(max)) + ': ' + name
FROM People
ORDER BY Id
FOR XML PATH ('')
),1,1,''
)
The problem here was FOR XML PATH escaped characters that could break the XML (e.g. ‘<' was replaced with '<'), which was not acceptible for me.
I looked for another solution but whatever I tried didn't work because of the ORDER BY statement which can't be used in a subquery or inline view.
Finally I decided to use a temporary table filling it with correct data and then create the final result. Here is the code:
DECLARE @res NVARCHAR(max) SET @res = '' CREATE TABLE #Result ( item nvarchar(max) ) INSERT INTO #Result SELECT CAST(Id AS NVARCHAR(max)) + ': ' + Name AS item FROM People ORDER BY id SELECT @res = @res + item + ', ' from #Result SELECT substring(@res,1,len(@res)-1) as Result
If you find a better solution can you please post it as a comment? I could use in in future 
Thanks in advance!
You could always stream it as XML Data and convert it into one column that way, similar to what you started with:
ISNULL(LTRIM(RTRIM(REPLACE(
(
SELECT CAST(ID AS NVARCHAR(MAX)) + ‘: ‘ + NAME AS [data()]
FROM People
ORDER BY ID
FOR XML PATH (”)
), ”, ‘,’))), ”)
For instances where it might “replace” strings with things like >… you could always encapsulate in a REPLACE string to change it back?
I use this trick a lot, but all the data I pass to it is alphanumeric with no symbols or unicode characters.
So true, you can always replace parts of the string… Sometimes easy solutions are the hardest to find
Cool, thanks for this guys, I got stuck with something like this a while back. I could not do it with SQL, I had to move the formatting to my application.
— HOW ABOUT USING CURSORS??:
— SELECT ONE COLUMN —
DECLARE @colvalu varchar (150),
@cString1 varchar (MAX),
@cString2 varchar (MAX)
SET @cString2 = ”
DECLARE my_cursor CURSOR FOR
select distinct COLNAME from TEBLENAME
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @colvalu
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cString1 = @colvalu + ‘,’
FETCH NEXT FROM my_cursor
INTO @colvalu
SET @cString2 = @cString2 + @cString1
END
SET @cString2 = Left(@cString2, Len(@cString2)-1)
print @cString2
CLOSE my_cursor
DEALLOCATE my_cursor
— SELECT 2(+) COLUMNS —
DECLARE @colvalu_1 varchar (150),
@colvalu_2 varchar (150),
@cString1 varchar (MAX),
@cString2 varchar (MAX)
SET @cString2 = ”
DECLARE my_cursor CURSOR FOR
SELECT COLNAME_1, COLNAME_2 FROM DBNAME
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @colvalu_1, @colvalu_2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cString1 = @colvalu_1 + @colvalu_2 + ‘,’
FETCH NEXT FROM my_cursor
INTO @colvalu_1, @colvalu_2
SET @cString2 = @cString2 + @cString1
END
SET @cString2 = Left(@cString2, Len(@cString2)-1)
print @cString2
CLOSE my_cursor
DEALLOCATE my_cursor
Cursors are an option here but I didn’t want to use them. Not only is it (much) longer but also not that efficient (especially if a table holds lots of data).
Simple code for a simple problem. This is an oldie….
declare @RESULT NVARCHAR(max)
set @RESULT = ”
SELECT @RESULT = @RESULT + CASE @RESULT WHEN ” THEN ” ELSE ‘, ‘ END + cast(id as NVARCHAR(MAX)) + ‘: ‘ + ISNULL(NAME,’ ‘)
FROM people
ORDER BY id
Can try use my code if you are interested.
Sorry if it is abit messy, I’m new to SQL and I’m not particularly good at coding.
——————————————————————–
declare @Counter int
declare @RowCount int
declare @RESULT NVARCHAR(max)
declare @TempRESULT NVARCHAR(max)
declare @Table Table (
internalID int identity(1,1),
id int,
Name varchar(max)
)
insert @table (id, Name)
select id, Name from People
order by id asc
set @Counter = 0
set @RESULT = ”
select @RowCount = COUNT(internalID) from @Table
while @Counter < @RowCount
begin
set @Counter = @Counter + 1
Select @TempRESULT = cast(id as varchar(max)) from @Table where internalID = @Counter
select @TempRESULT = @TempRESULT + ':' + Name from @Table where internalID = @Counter
if (@Counter = 1)
begin
set @RESULT = @TempRESULT
end
else
begin
set @RESULT = @RESULT + ', ' + @TempRESULT
end
end
——————————————————————
It should work. I modified it abit from my codings to this.
I have one table named Fixture which contains a column FixtureID. For instance, I want to get top 5 FixtureID and pass it to another query like comma separated value, but the comparison would be with BIGINT field like;
CREATE TABLE Fixture (FixtureID BIGINT)
INSERT INTO Fixture(FixtureID) VALUES(11111111)
INSERT INTO Fixture(FixtureID) VALUES(22222222)
INSERT INTO Fixture(FixtureID) VALUES(33333333)
INSERT INTO Fixture(FixtureID) VALUES(44444444)
INSERT INTO Fixture(FixtureID) VALUES(55555555)
DECLARE @res NVARCHAR(max)
SET @res = ”
SELECT TOP 5 @res = @res + FixtureID + ‘, ‘ from Fixture
SELECT @res = substring(@res,1,len(@res)-1)
SELECT * FROM Match
WHERE FixtureID IN (@res)
FixtureID is BIGINT, how should I compare comma separated values with BIGINT column?
Thanks a lot Jarosław, your page saved me a lot of time!
Didn’t need as sophisticated solution as you and what Chris suggested was perfect for my needs
thanks