Hi,
Thanks for supplying the data - it makes life easier for anyone answering your question - and it means you will get an answer quicker.
One (quick and dirty) way of doing it would be:-
WITH TheData (TYPE,Col1,Col2,Col3,Col4) as
(SELECT
'Type'[Type]
,(CASE WHEN code='09' THEN sum(Amt/100) ELSE 0 END) [Col1]
,(CASE WHEN code='10' THEN sum(Amt/100) ELSE 0 END) [Col2]
,(CASE WHEN code='11' THEN sum(Amt/100) ELSE 0 END) [Col3]
,(CASE WHEN code='12' THEN sum(Amt/100) ELSE 0 END) [Col4]
FROM TEST
GROUP BY Code)
SELECT Type,
SUM(col1) col1,
SUM(col2) col2,
SUM(col3) col3,
SUM(col4) col4
FROM TheData
GROUP BY type
It is possible that some one will come up with a more elegant method then mine - so don't rush to use any solution you see here. Also make sure you understand the method you choose as you will be supporting it going forwards :w00t:
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx