SQL Pivot Table multiple Columns

Question

How do I pivot multiple columns
CREATE TABLE #tblTest
(
AREA VARCHAR(10)
,LEVEL1 VARCHAR(3)
,LEVEL2 VARCHAR(3)
,Amount Decimal(5)

)
INSERT #tblTest VALUES ('HR', 'ABC', '', 100)
INSERT #tblTest VALUES ('COMP', 'BCD', '', 200)
INSERT #tblTest VALUES ('TECH','CDF', 'A1',5)
INSERT #tblTest VALUES ('TECH','CDF', 'A2',10)
INSERT #tblTest VALUES ('TECH','CDF', 'A3',15)
INSERT #tblTest VALUES ('OPR','DEF', '',90)

How I want it to look like.

----------

Heading

Answer 1

Try something like this:

drop table if exists #tblTest  
  
CREATE TABLE #tblTest  
(  
    AREA VARCHAR(10),  
    LEVEL1 VARCHAR(3),  
    LEVEL2 VARCHAR(3),  
    Amount Decimal(5)  
)  
  
INSERT #tblTest VALUES ('HR', 'ABC', '', 100)  
INSERT #tblTest VALUES ('COMP', 'BCD', '', 200)  
INSERT #tblTest VALUES ('TECH','CDF', 'A1',5)  
INSERT #tblTest VALUES ('TECH','CDF', 'A2',10)  
INSERT #tblTest VALUES ('TECH','CDF', 'A3',15)  
INSERT #tblTest VALUES ('OPR','DEF', '',90)  
  
select * from #tblTest  
  
---  
  
declare @columns as nvarchar(max)  
  
select @columns = STRING_AGG(QUOTENAME(case when LEVEL2 is null or LEVEL2 = '' then LEVEL1 else LEVEL2 end), ', ')  
from #tblTest  
  
declare @q as nvarchar(max) =   
N'select [AREA] as [ ], ' + @columns + ' from ( ' +  
' select [AREA], case when LEVEL2 is null or LEVEL2 = '''' then LEVEL1 else LEVEL2 end as V, Amount  
 from #tblTest   
) t  
pivot  
(  
    SUM(Amount) for V in (' + @columns + ')  
) p  
'  
  
--PRINT @q  
  
EXEC ( @q )  

The order of rows and columns was not specified. Empty cells are shown as ‘NULL’.

Answer 2

yes this is a dynamic query.
The values in LEvel2 for CDF is what used as columns

Answer 3

Hi @ojmp2001 ojmp2001 ，

It can also be achieved with ordinary SQL, please refer to：

    CREATE TABLE #tblTest  
    (  
    AREA VARCHAR(10)  
    ,LEVEL1 VARCHAR(3)  
    ,LEVEL2 VARCHAR(3)  
    ,Amount Decimal(5)  
      
    )  
    INSERT #tblTest VALUES ('HR', 'ABC', '', 100)  
    INSERT #tblTest VALUES ('COMP', 'BCD', '', 200)  
    INSERT #tblTest VALUES ('TECH','CDF', 'A1',5)  
    INSERT #tblTest VALUES ('TECH','CDF', 'A2',10)  
    INSERT #tblTest VALUES ('TECH','CDF', 'A3',15)  
    INSERT #tblTest VALUES ('OPR','DEF', '',90)  
      
      
    with cte  
    as(select AREA,case when LEVEL2='' then LEVEL1 else LEVEL2 end rn,Amount from #tblTest)  
    ,cte2 as  
    (select * from (select AREA,rn,Amount from cte) as t    
    pivot (max(Amount) for rn in (ABC,BCD,A1,A2,A3,DEF)) as p)  
    ,cte3 as  
    (select *,case when AREA='HR' then 1  
                  when AREA='COMP'then 2  
       when AREA='TECH'then 3  
       when AREA='OPR'then 4  
       end rn from cte2)  
      
    select AREA,ABC,BCD,A1,A2,A3,DEF   
    from cte3   
    order by rn  
      
    drop table #tblTest  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.