Transposing ONLY ONE column in ONE row

Hi at all

as by topic title i would want to transpose one column in one Row. Suppose we have one table with ONE column as follow

COL
1
2
3
4
5
6
7
8

and we want transpose this column in row as follow:

Row
1,2,3,4,5,6,7,8

One possible solution does use of the Cursor and is the following:

Code:

---

DECLARE @var NCHAR(10)
DECLARE @PATH VARCHAR(200) = ''

DECLARE mycursor CURSOR FOR SELECT* FROM TEMP
OPEN mycursor
FETCH NEXT FROM mycursor INTO @var

WHILE @@FETCH_STATUS = 0
        BEGIN
                SET @PATH = ISNULL(RTRIM(CAST(@var AS VARCHAR(10)))+',','') + @Path
                FETCH NEXT FROM mycursor INTO @var
        END

CLOSE mycursor
DEALLOCATE mycursor

---

What I want to ask is if is there one solution that does not use the cursor or is the cursor indispensable.

thanks.

You can use XML_PATH function in SQL Server to do this.

SELECT ',' + col AS [text()]
FROM temp
FOR XML PATH('')

It will have , as first character so you will need to write a CHARINDEX and SUBSTR to get rid of that.