CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jack
Jack

Reputation: 520

Transpose string value in SQL Server

I have Emp_Name column with 

Emp_Name
A
B
C
D

values.

I want to print in the one row with sequence number like 

1)A, 2)B, 3)C, 4)D ....

Upvotes: 0

Views: 1820

Answers (3)

Vecchiasignora
Vecchiasignora

Reputation: 1315

try this

declare @myTable table([Emp_Name] varchar(max))

insert @myTable values ('A'), ('B'), ('C'), ('D')


select STUFF((
        select  ', ' + CAST(ROW_NUMBER() over (order by Emp_Name) AS VARCHAR) +')' + [Emp_Name]
        from @myTable
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') [values]

just change your table with my temp table

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use CTE function to get transpose result:

DECLARE @String VARCHAR(100) = 'A,B,C,D'

;WITH _CTE ( TransStr , RemainStr ) AS
(
    SELECT SUBSTRING(@String,0,CHARINDEX(',',@String))   , 
           SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String))
   UNION ALL
   SELECT CASE WHEN CHARINDEX(',',RemainStr) = 0 THEN RemainStr ELSE   
           SUBSTRING(RemainStr,0,CHARINDEX(',',RemainStr)) END, 
          CASE WHEN CHARINDEX(',',RemainStr) = 0 THEN '' ELSE  
        SUBSTRING(RemainStr,CHARINDEX(',',RemainStr)+1,LEN(RemainStr)) END
   FROM _CTE 
   WHERE RemainStr<> '' 
)

SELECT  STUFF((
SELECT  ', '+ CAST( ROW_NUMBER() OVER(ORDER BY TransStr) AS VARCHAR) +')' + 
TransStr FROM _CTE FOR XML PATH('')
),1,2,'')   AS String

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Try the below query,

SELECT  STUFF((
    SELECT  ', '+ CAST( ROW_NUMBER() OVER(ORDER BY Emp_Name) AS VARCHAR) +')' +Emp_Name
    FROM    @Employee_Table
    FOR XML PATH('')
    ),1,2,'')   AS Emp_Name

Upvotes: 3

Related Questions