Results 1 to 2 of 2

Thread: Transposing ONLY ONE column in ONE row

  1. #1
    Join Date
    Jun 2013
    Location
    Italy
    Posts
    4

    Transposing 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.
    Last edited by dancko; 06-09-2013 at 04:04 AM.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.
    Last edited by skhanal; 06-10-2013 at 01:25 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •