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