fscode count areatype ASM001 11 EX ASM001 37 HQ ASM001 5 OS ASM002 6 EX ASM002 37 HQ ASM002 6 OS ASM003 9 EX ASM003 40 HQ ASM003 2 OS ASM004 2 EX ASM004 31 HQ ASM004 14 OS ASM005 1 EX ASM005 39 HQ ASM005 8 OS ASM006 51 HQ
code HQ EX OS ASM001 37 11 5 ASM002 37 6 6 ASM003 40 9 2 ASM004 31 2 14 ASM005 39 1 8 ASM006 51 0 0
pivot
SELECT FSCode, SUM(CASE WHEN AreaType = 'HQ' THEN [COUNT] ELSE 0 END) AS HQ, SUM(CASE WHEN AreaType = 'EX' THEN [COUNT] ELSE 0 END) AS EX, SUM(CASE WHEN AreaType = 'OS' THEN [COUNT] ELSE 0 END) AS OS FROM MyTable GROUP BY FSCode
SELECT * FROM ( SELECT fscode ,areatype ,ISNULL(SUM([count]), 0) AS C FROM MyTable GROUP BY fscode ,areatype ) AS data PIVOT(SUM(C) FOR areatype IN ( "HQ" ,"EX" ,"OS" )) AS [pivot]
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)