PIVOT the date column in SQL Server 2012

Question

Here is my input table like:

date        E1  E2  E3  E4
05-27-15    1   1   2   3
05-28-15    2   3       5
05-29-15    3   4       2

I would like my output be:

Error type  05-27-15    05-28-15    05-29-15    Total   Percentage%
E1              1           2            3       6       6/35
E2              1           3            4       8       8/35
E3              2                                2       2/35
E4              3           5            2       10      10/35

I know this is little problem but I am not getting my answer like this from other Google help.

Please help me to sort out this. Thanks.

But i have multiple dates for two years. – Jack Jul 17 '15 at 12:53 — Jack, Jul 17 '15 at 12:53
where the percentage 35 comes @DatabasePirate – mohan111 Jul 17 '15 at 13:40 — mohan111, Jul 17 '15 at 13:40
@DatabasePirate is this answer useful for you – mohan111 Jul 18 '15 at 01:09 — mohan111, Jul 18 '15 at 01:09

mohan111 · Accepted Answer · 2015-07-20T09:28:32.797

0

as per my assumption and data provided we can get using Cross Apply but i'm not clear about Percentage column

declare @t table (dated varchar(10),E1 int,E2 int,E3 int,E4 int)
insert into @t
(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)


Select P.col,MAX([05-27-15])[05-27-15],
MAX([05-28-15])[05-28-15],
MAX([05-29-15])[05-29-15],
SUM([05-27-15])+SUM(COALESCE([05-28-15],0))+SUM(COALESCE([05-29-15],0))Total
 from (
select col,[05-27-15],[05-28-15],[05-29-15] from (
select * from @t
CROSS APPLY(values('E1',E1),('E2',E2),('E3',E3),('E4',E4))cs (col,val))PP
PIVOT(MAX(val) for dated IN ([05-27-15],[05-28-15],[05-29-15]))P)P
GROUP BY P.col

Dynamic version :

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 NVARCHAR(max)

SELECT @select_columns = Isnull(@select_columns + ', ', '')+ N'MAX([' + tbl.dated + ']) AS ['+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

edited Jul 20 '15 at 09:28

answered Jul 17 '15 at 13:44

mohan111

8,633
4
28
55

Thanks mohan111, but you hardcode the dates, i have multiple dates in date column like 2-3 years dates i can hardcode error type but not date. is their another solution for it. any dynamic solution. – Jack Jul 20 '15 at 04:09
we can do in dynamic but let me work it out on that one get back to you @DatabasePirate – mohan111 Jul 20 '15 at 05:41
@DatabasePirate now check the dynamic version so you can send the datas dynamically – mohan111 Jul 20 '15 at 07:29
Great !!!, this is better, but now column name comes like (No Column Name) instead of dates. how to give proper date as column name. – Jack Jul 20 '15 at 08:29
It will take more execution time. performance issue. – Jack Jul 20 '15 at 08:52
yeah basing on your requirement it is quite complicated but it will depend on that how much data because it is filtering dates and getting max dates,during aggregation also.just check it out execution plan – mohan111 Jul 20 '15 at 08:56
i want E1 count for every distinct date E2 and E3 same....I have E1 to E36 types – Jack Jul 20 '15 at 09:10
please check update query for columns you have asked and now it is getting and what is E36 types ???@DatabasePirate – mohan111 Jul 20 '15 at 09:26
Thanks Mohan, now everything is working fine. – Jack Jul 20 '15 at 10:36
if it is useful please mark it as useful answer @DatabasePirate – mohan111 Jul 20 '15 at 10:45

PIVOT the date column in SQL Server 2012

1 Answers1

Linked