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