How can I dynamically convert row to columns and have different column name for each column

Question

How can I convert rows into columns and create different name for each column?

create table #TempTable  (InvoiceNum int,State varchar(2), ChargeName varchar(50),  PercentageRate decimal(5,3), FlatRate decimal(5,2))
insert into #TempTable values   (235736, 'AZ','Inspection & Policy Fee',  NULL,250.00)
                                ,(235736, 'AZ','Surplus Line Tax',0.03,NULL)
                                ,(235736, 'AZ','Stamping Fee',0.002,NULL

)

I need something like that:

UPDATE:

Using example I was able to unpivot it but the result is not what I wanted to:

create table #TempTable  (InvoiceNum int,State varchar(2), ChargeName varchar(50),  PercentageRate decimal(5,3), FlatRate decimal(5,2))
insert into #TempTable values   (235736, 'AZ','Inspection & Policy Fee',  NULL,250.00)
                                ,(235736, 'AZ','Surplus Line Tax',0.03,NULL)
                                ,(235736, 'AZ','Stamping Fee',0.002,NULL)

--select * from @TempTable


Declare @SQL nvarchar(max),
        @query nvarchar(max)

select @SQL = STUFF((SELECT ',' + QUOTENAME(ChargeName) 
                    from #TempTable
                    group by ChargeName, InvoiceNum
                    order by InvoiceNum
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
--select @SQL
set   @SQL = 'SELECT ' + @SQL + '  from
        (
            select PercentageRate, ChargeName
            from #TempTable
        ) x
        pivot
        (
        max(PercentageRate)
        for ChargeName in (' + @SQL + ')
        ) p '

exec sp_executesql @SQL;

UPDATE:

Running below query gives me this:

Why ChargeName is not on the first row? I would expect to see it like this: What am I missing?

declare  @TempTable table  (InvoiceNum int,StateID varchar(2), ChargeName varchar(50),  PercentageRate decimal(5,3), FlatRate decimal(5,2))
insert into @TempTable values   (235736, 'AZ','Inspection & Policy Fee',  NULL,250.00)
                                ,(235736, 'AZ','Surplus Line Tax',0.03,NULL)
                                ,(235736, 'AZ','Stamping Fee',0.002,NULL)
select 
        InvoiceNum,
        ChargeName,
        StateID,
        PercentageRate,
        FlatRate,
        row_number() over (partition by InvoiceNum order by ChargeName) as RN
into #TempTable
 from @TempTable #TempTable

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(RN)
FROM (SELECT DISTINCT RN FROM #TempTable) AS RN

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT InvoiceNum,  ' + @ColumnName + '
    FROM #TempTable
    PIVOT(MAX(ChargeName) 
          FOR RN IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery
drop table #TempTable

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — rs., Oct 25 '18 at 18:08
If you still think this is not a duplicate, try to add a calculated column for Tax1, Tax2.. using row_number and then use above link to achieve the results you are looking for. — rs., Oct 25 '18 at 18:10
Thanks. I used that example to get to some point. But got confused how to modify it in order to get desirable result. I updated question. Thanks again — Serdia, Oct 25 '18 at 18:19
This is going to require multiple pivots and get real ugly. I show how to do dynamic columns [here](https://stackoverflow.com/a/45423065/6167855) but it's going to require you to do it 3 times i think — S3S, Oct 25 '18 at 18:31
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ — Aaron Bertrand, Oct 25 '18 at 18:44
@scsimon Thank you . I used your example but for some reason I get ChargeName in each row. What am I doing wrong? I updated with picture. — Serdia, Oct 25 '18 at 22:40
That would be awesome.I really need it. Thank you very much for your time — Serdia, Oct 26 '18 at 15:32

score 0 · Answer 1 · answered Oct 25 '18 at 18:28

I would just join the temp table multiple times as needed. Given your #TempTable

SELECT T1.InvoiceNum,
    Tax1_Jurisdiction = T1.State, Tax1_TaxType = T1.ChargeName, Tax1_Percent = T1.PercentageRate, Tax1_FixedRate = T1.FlatRate, 
    Tax2_Jurisdiction = T2.State, Tax2_TaxType = T2.ChargeName, Tax2_Percent = T2.PercentageRate, Tax2_FixedRate = T2.FlatRate, 
    Tax3_Jurisdiction = T3.State, Tax3_TaxType = T3.ChargeName, Tax3_Percent = T3.PercentageRate, Tax3_FixedRate = T3.FlatRate
FROM #TempTable T1
JOIN #TempTable T2 ON T1.InvoiceNum = T2.InvoiceNum
JOIN #TempTable T3 ON T1.InvoiceNum = T3.InvoiceNum
WHERE T1.ChargeName = 'Inspection & Policy Fee'
    AND T2.ChargeName = 'Surplus Line Tax'
    AND T3.ChargeName = 'Stamping Fee'
;

Is the number of charges also dynamic? If not then you could use variables or another temp table to specify the different charge codes and the order they should be applied. — Russ, Oct 25 '18 at 18:38

How can I dynamically convert row to columns and have different column name for each column

1 Answers1