tsql - CTE Pivot with Group by results -

tsql - CTE Pivot with Group by results -

i have table datetime column, want check if there's record each hr of day, display missing hr of day, write first script pivot find if ther's record of each hr result don't grouping rows same date,

with test (select [numero] ,[client] ,[creele] ,[montantpaye] ,[anneeproduction] ,cast(creele date) datee ,datepart(hour, creele) 'heure' [clmdatabase].[dbo].[fiches] grouping creele, numero, client, montantpaye,anneeproduction ) select datee, isnull([1],0) "01h", isnull([2],0) "02h", isnull([3],0) "03h", isnull([4],0) "04h", isnull([5],0) "05h", isnull([6],0) "06h", isnull([7],0) "07h", isnull([8],0) "08h", isnull([9],0) "09h", isnull([10],0) "10h", isnull([11],0) "11h", isnull([12],0) "12h", isnull([13],0) "13h", isnull([14],0) "14h", isnull([15],0) "15h", isnull([16],0) "16h", isnull([17],0) "17h", isnull([18],0) "18h", isnull([19],0) "19h", isnull([20],0) "20h", isnull([21],0) "21h", isnull([22],0) "22h", isnull([23],0) "23h", isnull([00],0) "00h" test pivot ( count (client)for heure in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15] ,[16],[17] ,[18] ,[19] ,[20],[21],[22],[23],[00])) pvt order datee ;

the result :

date ¦ 01h ¦ 02h ¦03h ¦ 04h ... 03-10.2006 ¦ 1 ¦ 0 ¦ 0 ¦ 0 ... 03-10.2006 ¦ 0 ¦ 0 ¦ 1 ¦ 0 ... 13-11.2006 ¦ 0 ¦ 0 ¦ 0 ¦ 1 ...

rather :

date ¦ 01h ¦ 02h ¦03h ¦ 04h ... 03-10.2006 ¦ 1 ¦ 0 ¦ 1 ¦ 0 ... 13-11.2006 ¦ 0 ¦ 0 ¦ 0 ¦ 1 ...

you can agregate in subquery or in cte required info , transpose thro pivot

select datee , isnull([1], 0) [01h] , isnull([2], 0) [02h] , isnull([3], 0) [03h] , isnull([4], 0) [04h] , isnull([5], 0) [05h] , isnull([6], 0) [06h] , isnull([7], 0) [07h] , isnull([8], 0) [08h] , isnull([9], 0) [09h] , isnull([10], 0) [10h] , isnull([11], 0) [11h] , isnull([12], 0) [12h] , isnull([13], 0) [13h] , isnull([14], 0) [14h] , isnull([15], 0) [15h] , isnull([16], 0) [16h] , isnull([17], 0) [17h] , isnull([18], 0) [18h] , isnull([19], 0) [19h] , isnull([20], 0) [20h] , isnull([21], 0) [21h] , isnull([22], 0) [22h] , isnull([23], 0) [23h] , isnull([00], 0) [00h] ( select count([client]) client , convert(date, creele) datee , datepart(hour, creele) [heure] #t grouping convert(date, creele) , datepart(hour, creele) ) newtest pivot ( sum(client) heure in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [00] ) ) pvt order datee;

tsql pivot common-table-expression