sql - Pivot table for datetime -

sql - Pivot table for datetime -

i have next table pivoting.

example:

table:

create table testing ( column_date datetime )

insertion of records:

insert testing values('2014-11-07'),('2014-11-08'), ('2014-11-01'),('2014-11-02'),('2014-11-04');

expected result:

column_date 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 ---------------------------------------------------------------------------------------------------------- 2014-11-07 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-08 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-01 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-02 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-04 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 attempt: select a.column_date,[01],[02],[03],[04],[05],[06],[07],[08],[09],[10], [11],[12],[13],[14],[15],[16],[17],[18],[19],[20], [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ( select column_date testing ) pivot ( count(column_date) column_date in([01],[02],[03],[04],[05],[06],[07],[08],[09],[10], [11],[12],[13],[14],[15],[16],[17],[18],[19],[20], [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) pvt;

error details:

msg 8114, level 16, state 1, line 11 error converting info type nvarchar date. msg 473, level 16, state 1, line 11 wrong value "01" supplied in pivot operator. msg 4104, level 16, state 1, line 1 multi-part identifier "a.column_date" not bound.

try this. need utilize datepart days of month in select query produces data , utilize in pivot result..

select column_date, [01],[02],[03],[04],[05],[06],[07],[08],[09], [10],[11],[12],[13],[14],[15],[16],[17],[18], [19],[20],[21],[22],[23],[24],[25],[26],[27], [28],[29],[30],[31] (select column_date, datepart(dd, column_date) dd, column_date ddate #testing) pivot ( count(ddate) dd in( [01],[02],[03],[04],[05],[06],[07],[08],[09], [10],[11],[12],[13],[14],[15],[16],[17],[18], [19],[20],[21],[22],[23],[24],[25],[26],[27], [28],[29],[30],[31]) ) pvt;

output

column_date 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 --------------------------------------------------------------------------------------------------------------------------------------------------- 2014-11-01 00:00:00.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-02 00:00:00.000 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-04 00:00:00.000 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-07 00:00:00.000 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2014-11-08 00:00:00.000 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

sql sql-server tsql sql-server-2008-r2 pivot