SELECT * FROM (
Select FormatID, Name
, v.value('./@id', 'varchar(255)') Macro
FROM Formats
CROSS APPLY GenMac.nodes('//g') TempXML(v)
--where v.value('./@TransFormat', 'varchar(255)') not like '%{Last Step}%'
) AS SRC
PIVOT( Count(Macro) FOR Macro IN (
["", 34],["",34],[B1],[B2],[B3],[B4],[B5],[B6],[B7],[B8],[C0],[C10],[C11],
[C12],[C2],[C22],[C3],[C4],[C5],[C6],[C7],[C8],[C9],[CHKBOX,12],[H1],[H2],
[H3],[H4],[H5],[m35],[m36],[PLNTPLMAC,0],[PLNTPLMAC2,0]
)) AS PVT1 Order by case when Name='Base' then 0 else 1 end, Name