Q:

SQL Server Exception Reporting Script

Seeing this is my first post of 2016, Happy New Year to all.
Once again, I'm stuck with a tricky report I must pull from SQL Server.
I have a two tables called Documents and Doctype
Columns in Documents:
File_Name, Date, Region, DoctypeID

Data inside this columns are as follows.
   00001,2016-01-06,JHB,1d187ecc
   00001,2016-01-06,JHB,bccc05f9
   00001,2016-01-06,JHB,fe697be0
   00001,2016-01-06,JHB,bbae8c73
   00002,2016-01-06,JHB,1d187ecc
   00002,2016-01-06,JHB,bccc05f9
   00002,2016-01-06,JHB,fe697be0

Columns in Doctype:
DoctypeID, Document_Type

Data inside this columns are as follows.
1d187ecc, Collection
bccc05f9, Image
fe697be0, Log
bbae8c73, Sent to warehouse.

My query needs to give me the below result, using the data above.
File_Name,Collection, Image, Log, Sent to Warehouse,Region
00001,         1,       1,    1,       1,            JHB
00002,         1,       1,    1,       0,            JHB

I hope the above makes sense, how would I go about doing this?
Thank you all in advance.

A:

As you can try this:
SELECT FILE_NAME, ISNULL([Collection],0) AS [Collection], ISNULL([Image],0) AS [Image],  
ISNULL([Log],0) AS [Log],  ISNULL([Sent to warehouse],0) AS [Sent to warehouse], Region
FROM (  
    SELECT FILE_NAME, Document_Type, COUNT(Document_Type) AS Frequency, Region 
    FROM documents d,doctype dt WHERE d.DoctypeID = dt.DoctypeID 
    GROUP BY Document_Type, FILE_NAME,REGION
) AS s
PIVOT
(
    SUM(Frequency)
    FOR [Document_Type] IN ([Collection], [Image], [Log], [Sent to warehouse])
)AS pvt

Here is a detail reference that you can read. 

