Convert column values into row

I have a table which stores information in this way:
Date EMP_ID ADCode Amount
-- -- --
--
1-Jan-2007 101 RPF 150
1-Jan-2007 101 RPF.ADV 200
1-Jan-2007 101 GIS 60
1-Jan-2007 101 GIS.ADV 100
1-Jan-2007 102 RPF 50
1-Jan-2007 102 RPF.ADV 100
Using SQL query or any other way, I want to show like this:
Date Emp_ID RPF RPF.ADV GIS GIS.ADV
-----
1-Jan-2007 101 150 200 60
100
1-Jab-2007 102 50 100
0 0In SQL Server 2005 you can use PIVOT operator:
SELECT
Date,
EMP_ID,
ISNULL([RPF],0) [RPF],
ISNULL([RPF.ADV],0) [RPF.ADV],
ISNULL([GIS],0) [GIS],
ISNULL([GIS.ADV],0) [GIS.ADV]
FROM (
SELECT Date, EMP_ID, ADCode, Amount
FROM YourTable
) AS T
PIVOT
(
SUM(T.Amount)
FOR ADCode IN ([RPF], [RPF.ADV], [GIS], [GIS.ADV])
) AS P
In SQL Server 2000 you can use CASE statement:
SELECT
Date,
EMP_ID,
SUM(CASE ADCode WHEN 'RPF' THEN Amount ELSE 0 END) [RPF],
SUM(CASE ADCode WHEN 'RPF.ADV' THEN Amount ELSE 0 END) [RPF.ADV],
SUM(CASE ADCode WHEN 'GIS' THEN Amount ELSE 0 END) [GIS],
SUM(CASE ADCode WHEN 'GIS.ADV' THEN Amount ELSE 0 END) [GIS.ADV]
FROM YourTable
GROUP BY Date, EMP_ID
ORDER BY Date, EMP_ID
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "RP" <rpk.general@.gmail.com> napisal w wiadomosci
news:1187845549.489646.66260@.q3g2000prf.googlegroups.com...
>I have a table which stores information in this way:
> Date EMP_ID ADCode Amount
> -- -- --
> --
> 1-Jan-2007 101 RPF 150
> 1-Jan-2007 101 RPF.ADV 200
> 1-Jan-2007 101 GIS 60
> 1-Jan-2007 101 GIS.ADV 100
> 1-Jan-2007 102 RPF 50
> 1-Jan-2007 102 RPF.ADV 100
>
> Using SQL query or any other way, I want to show like this:
> Date Emp_ID RPF RPF.ADV GIS GIS.ADV
> -----
> 1-Jan-2007 101 150 200 60
> 100
> 1-Jab-2007 102 50 100
> 0 0
>