Dynamic Pivot with multiple columns on SQL Server

Dynamic Pivot with multiple columns

create table raghudb.dbo.test_pivot2(id int,name varchar(20),city varchar(20),type varchar(10),inc int,value int)

insert into raghudb.dbo.test_pivot2 values(1,'A','CHE','aa',100000,100)

insert into raghudb.dbo.test_pivot2 values(1,'A','BLR','bb',200000,200)

insert into raghudb.dbo.test_pivot2 values(2,'B','CHE','aa',300000,300)

insert into raghudb.dbo.test_pivot2 values(2,'B','BLR','bb',400000,400)

insert into raghudb.dbo.test_pivot2 values(2,'B','DEL','cc',500000,500)

insert into raghudb.dbo.test_pivot2 values(3,'C','HYD','aa',600000,100)

Data in the table

SELECT [id]

      ,[name]

      ,[city]

      ,[type]

      ,[inc]

      ,[value]

  FROM [RaghuDB].[dbo].[test_pivot2]

id	name	city	type	inc	value
1	A	CHE	aa	100000	100
1	A	BLR	bb	200000	200
2	B	CHE	aa	300000	300
2	B	BLR	bb	400000	400
2	B	DEL	cc	500000	500
3	C	HYD	aa	600000	100

Query for dynamic pivot with multiple columns:

DECLARE @cols AS NVARCHAR(MAX),@cols1 AS NVARCHAR(MAX),

    @Dyn_Pivot  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(city)

                    from raghudb.dbo.test_pivot2

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,'')

select @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(TYPE)

                    from raghudb.dbo.test_pivot2

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,'')

set @Dyn_Pivot = 'alter VIEW Test_v1 as SELECT NAME,' + @cols + ','+@cols1 +'

             from

             (

                select ID,nAME,CITY,VALUE,type,inc

                from raghudb.dbo.test_pivot1

            ) t

            pivot

            (

                max(id)

                for CITY in (' + @cols + ')

            ) p

                     pivot

            (

                           max(inc)

                           for type in (' + @cols1 + ')

            ) p1

        '

print @dyn_pivot

Execute (@Dyn_Pivot)

Output for the above query:

NAME	BLR	CHE	DEL	HYD	aa	bb	cc
A	NULL	1	NULL	NULL	100000	NULL	NULL
A	1	NULL	NULL	NULL	NULL	200000	NULL
B	NULL	2	NULL	NULL	300000	NULL	NULL
B	2	NULL	NULL	NULL	NULL	400000	NULL
B	NULL	NULL	2	NULL	NULL	NULL	500000
C	NULL	NULL	NULL	3	600000	NULL	NULL

Dynamic Pivot in SQL Server

Dynamic Pivot in SQL Server

Source Table

CREATE TABLE [RaghuDB]. [dbo].[Test_Pivot_1](

       [Employee number] [varchar](50) NULL,

       [Name] [varchar](50) NULL,

       [Department] [varchar](50) NULL,

       [Class cateogory] [varchar](50) NULL,

       [Class code] [varchar](50) NULL,

       [Salary] [varchar](50) NULL

)

SELECT 

       [Employee number],[Name],[Department],[Class cateogory],[Class code],[Salary]

FROM [RaghuDB].[dbo].[Test_Pivot_1]

Data in the table -

Employee number	Name	Department	Class cateogory	Class code	Salary
DS123	ABC	BI Consultant	Age	25	40000
DS124	ABC	BI Consultant	Experience	2	40000
DS125	ABC	BI Consultant	Expertise	Tableau	40000
DS126	ABC	BI Consultant	DOB	1/1/1991	40000

Query to dynamic Pivot

=====================================================

DECLARE @cols AS NVARCHAR(MAX),@cols1 AS NVARCHAR(MAX),

    @Dyn_Pivot  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Class cateogory])

                    from [RaghuDB].[dbo].[Test_Pivot_1]

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,'')

set @Dyn_Pivot = 'alter VIEW Test_v2 as SELECT NAME,[Department],' + @cols + '

             from

             (

                select [Name],[Department],[Class cateogory],[Class code],[Salary]

                from [RaghuDB].[dbo].[Test_Pivot_1]

            ) t

            pivot

            (

                max([Class code])

                for [Class cateogory] in (' + @cols + ')

                                       ) p

                            '

print @dyn_pivot

--DECLARE @view1

--select * from test_v2

--from

Execute (@Dyn_Pivot)

alter VIEW Test_v2 as SELECT NAME,[Department],[Age],[DOB],[Experience],[Expertise]

             from

             (

                select [Name]

      ,[Department]

      ,[Class cateogory]

      ,[Class code]

      ,[Salary]

                from [RaghuDB].[dbo].[Test_Pivot_1]

            ) t

            pivot

            (

                max([Class code])

                for [Class cateogory] in ([Age],[DOB],[Experience],[Expertise])

                                       ) p

                            

=========================================================================================

select * from test_v2

NAME	Department	Age	DOB	Experience	Expertise
ABC	BI Consultant	25	1/1/1991	2	Tableau