dynamic pivot table

Dynamic pivot table:- Easy learning

create table TestData

(

    Sno int,

    product varchar(30),

    product_value varchar(50)

   

)

--select * from TestData

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (1, N'A', N'100')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (2, N'A', N'200')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (3, N'A', N'300')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (4, N'B', N'400')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (5, N'A', N'500')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (6, N'C', N'600')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (7, N'C', N'700')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (8, N'B', N'800')

DECLARE @cols AS VARCHAR(MAX),

            @colsname as varchar(max),

            @query  AS VARCHAR(MAX);

           

set @cols = ''

set @colsname = ''

SELECT @colsname = COALESCE(@colsname + '['+ product+'],' ,','),

            @cols = COALESCE(@cols + 'ISNULL(sum(CAST(['+ product+']AS FLOAT)),0)'+ '['+ product+'],' ,',')

FROM

(

select distinct product from TestData    

)

TEMP

SET @colsname = SUBSTRING(@colsname,1,LEN(@colsname) -1)

SET @cols = SUBSTRING(@cols,1,LEN(@cols) -1)

set @query = 'SELECT Sno, ' + @cols + 'from

            (

                select Sno, product_value,product

                from TestData

            ) x

            pivot

            (

                max(product_value)

                for product IN(' + @colsname + ')

            ) p group by sno'

execute(@query)