How to convert column to row in SQL Server

Question

I would like to convert following data

CLAIMID CODENUMBER
------------------
2          465.9
2          238.8
2          461.9
2          786.2

Would like to see as

CLAIMID ICD1    ICD2    ICD3    ICD4
2       465.9   238.8   461.9   786.2

Your help will be appreciated..

If you google your question title, you can come back to us when the solution doesn't work — HoneyBadger, Aug 10 '16 at 15:39
This has been asked and answered literally thousands and thousands of times all over the internet. — Sean Lange, Aug 10 '16 at 15:42
***Loads*** of duplicates - just **search** for the title you've entered - you'll get a ***loooooooong*** list of similar posts - e.g. http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server and a great many others.... — marc_s, Aug 10 '16 at 15:54

John Cappelletti · Answer 1 · 2016-08-10T16:09:05.537

PIVOT is clearly what you are looking for, but if you don't mind going DYNAMIC

The source can be a table,temp table, or even SQL as illustrated below. You can also add Group By's and even additional metrics (ie counts and totals).

Declare @SQL varchar(500) = 'Select *,RowNr=Concat(''ICD'',Row_Number() over (Partition By ClaimID Order By ClaimID)) from YourTable'
Exec [prc-Pivot] @SQL,'RowNr','max(CODENUMBER)[]','ClaimID','count(*)[Claims],sum(CODENUMBER)[Total]'

Returns

ClaimID Claims  Total    ICD1    ICD2    ICD3    ICD4
2       4       1952.80  465.90  238.80  461.90  786.20

The Stored Procedure

CREATE PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'
--Exec [prc-Pivot] '#Temp','Attribute','max(Description)[]','ID','count(*)[Records]'

Set NoCount On
Set Ansi_Warnings Off

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)


Set NoCount Off
Set Ansi_Warnings on

EDIT: The SQL supplied to the Pivot Procedure should really have a proper sequence. For example, in the Row_Number() ORDER BY should really be a claim date or true sequence.

Select *,RowNr=Concat('ICD',Row_Number() over (Partition By ClaimID Order By ClaimID)) from YourTable

Returns

CLAIMID CODENUMBER  RowNr
2       465.90      ICD1
2       238.80      ICD2
2       461.90      ICD3
2       786.20      ICD4

score 0 · Answer 2 · edited Aug 27 '16 at 04:24

I got it using this query:

SELECT
*
 FROM
 (
    Select CLAIMID,CODENUMBER, RowNr = Concat('ICD',Row_Number() over (Partition By ClaimID Order By ClaimID)) 
from ClaimCodeDetail
    WHERE ClaimCodeTypeID = 729
    and RowState = 'A'
 ) AS P
PIVOT
(
  MAX(CODENUMBER) FOR RowNr IN (ICD1,ICD2,ICD3,ICD4,ICD5,ICD6,ICD7,ICD8,ICD9,ICD10,ICD11,ICD12,ICD13,ICD14,ICD15,ICD16,ICD17,ICD18,ICD19,ICD20,
ICD21,ICD22,ICD23,ICD24,ICD25,ICD26,ICD27,ICD28,ICD29,ICD30,ICD31,ICD32,ICD33,ICD34,ICD35,ICD36,ICD37,ICD38,ICD39,ICD40,ICD41,ICD42,
ICD43,ICD44,ICD45,ICD46,ICD47,ICD48,ICD49,ICD50)
) AS pv

How to convert column to row in SQL Server

2 Answers2