SQL Server classified reports in a technical, very practical

server|sqlserver| Tips
There are table TableA as follows:-----------------------------------------------------ID, type, countnumber, countDate1 a 10 2005-02   -01 21:23:342 B 8 2005-02-01 21:23:343 C 5 2005-02-01 21:23:344 B 4 2005-02-01 11:23:345 C 5 2005-02-01 22:23:346 A 11 2005-02-02 12:23:347 B 9 2005-02-02 14:23:348 C 8 2005-02-02 17:23:349 A 2005-02-02 19:23:3410 C 6 2005-02-02 04: 23:3411 A 7 2005-02-03 1:23:3412 B 2005-02-03 2:23:3413 C 12 2005-02-03 5:23:34 ..............

Want to use a statement to generate the following report

Date Type a type B type C 2005-02-01 10 12 10 2005-02-02 26 9 14 2005-02-03 7 11 12

I used the following SQL statement:

Select A.DATEFMT (select SUM (*) from TableA where type= ' a ' and convert (char (), countdate,20) = a.datefmt) TypeA, (select SUM (*) from TableA where type= ' B ' and CONVERT (char (), countdate,20) = a.datefmt) TypeB, (select SUM (*) from TableA where Type= ' C ' and CONVERT (char (), countdate,20) = a.datefmt) Typec

From (SELECT convert (chars, countdate,20) datefmt from TableA GROUP by convert (char), countdate,20)

But this statement is particularly slow to execute on SQL Server, long (more than 20 seconds), with 90,000 data in the Tabela. And I am in MySQL (the table has 20,000 data) executes on much faster!

Later on the forum to see the master of this writing

Select CONVERT (char, countdate,20), typea=sum (case type when A then CountNumber else 0 "), typeb=sum (case type when B t Hen countnumber Else 0 end), typec=sum (case type when C then CountNumber else 0 end) from Tableagroup by convert (char), CO untdate,20)

Only in 1 seconds! Alas! It's awesome!