is any other way to do in dynamic pivot

Question

while answering one question i got struck with other question in mind.when it is normal pivot it is working fine but if i'm trying to do Dynamic query when the problem arises

after answering he asked for Dynamic Pivot

PIVOT the date column in SQL Server 2012

if OBJECT_ID('tempdb..#temp') is not null
begin
drop table #temp
end


CREATE  table #temp (dated varchar(10),E1 int,E2 int,E3 int,E4 int)
insert into #temp
(dated,E1,E2,E3,E4)values 
('05-27-15',1,1,2,3),
('05-28-15',2,3,NULL,5),
('05-29-15',3,4,null,2)

DECLARE @statement NVARCHAR(max)
,@columns NVARCHAR(max)

SELECT @columns = ISNULL(@columns + ', ', '') + N'[' + tbl.dated + ']'
FROM (
   SELECT DISTINCT dated
   FROM #temp
   ) AS tbl


SELECT @statement = 'Select P.col,MAX('+@columns+') from ( 
select col,' + @columns + ' from (
select * from #temp
CROSS APPLY(values(''E1'',E1),(''E2'',E2),(''E3'',E3),(''E4'',E4))cs (col,val))PP
PIVOT(MAX(val) for dated IN  (' + @columns + ')) as PVT)P
GROUP BY P.COL
'
PRINT @statement
EXEC sp_executesql @statement = @statement

my problem is how can i take MAX() conditions for the all dates dynamically like max(05-27-15),max(05-28-15) etc dates are coming dynamically how to assign max condition

Pரதீப் · Accepted Answer · 2015-07-20T06:44:42.863

Moving the MAX aggregate to column list variable will fix the issue

DECLARE @statement      NVARCHAR(max),
        @columns        NVARCHAR(max),
        @select_columns NVARCHAR(max)

SELECT @select_columns = Isnull(@select_columns + ', ', '')+ N'MAX([' + tbl.dated + '])'
FROM   (SELECT DISTINCT dated
        FROM   #temp) AS tbl

SELECT @columns = Isnull(@columns + ', ', '') + N'[' + tbl.dated+ ']'
FROM   (SELECT DISTINCT dated
        FROM   #temp) AS tbl

SELECT @statement = 'Select P.col,' + @select_columns
                    + ' from ( 
    select col,' + @columns
                    + ' from (
    select * from #temp
    CROSS APPLY(values(''E1'',E1),(''E2'',E2),(''E3'',E3),(''E4'',E4))cs (col,val))PP
    PIVOT(MAX(val) for dated IN  (' + @columns
                    + ')) as PVT)P
    GROUP BY P.COL
' 


PRINT @statement
EXEC sp_executesql @statement = @statement

it is showing syntax error what ever the code you have posted — mohan111, Jul 20 '15 at 06:16
may be you have added max in declared statement but it wont in pivot taking max() condition — mohan111, Jul 20 '15 at 06:26

is any other way to do in dynamic pivot

1 Answers1