CREATE TABLE EMPDev
(eno int,
ENAME varchar(20),
DName varchar(20),
Hours Decimal(10,2),
WorkType varchar(20));

INSERT INTO EMPDev Values(1,'xyz','Computers',14.12,'HOME');
INSERT INTO EMPDev Values(2,'xyz','Computers',15.20,'OFFICE')
INSERT INTO EMPDev Values(3,'xyz','Finance',16.30,'HOME')
INSERT INTO EMPDev Values(4,'xyz','Computers',14.10,'OFFICE')
INSERT INTO EMPDev Values(5,'xyz2','Computers',7.30,'HOME')
INSERT INTO EMPDev Values(6,'xyz2','Computers',16.45,'OFFICE')
INSERT INTO EMPDev Values(7,'xyz3','Computers',2.20,'HOME')
INSERT INTO EMPDev Values(8,'xyz4','Computers',14.10,'OFFICE')

I Want output like

        ENAME DNAME HOME_Hours OFFICE_Hours
        xyz Computers 14.12 29.50
    xyz Finance 16.30 0
    xyz2 Computers 7.30 16.45
    xyz3 Computers 2.20 0
    xyz4 Computers 0 14.20

Please help me.

Thanks in advance

CREATE FUNCTION dbo.GetHours
    (
    @eno int,
    @WorkType nvarchar(20)
    )
RETURNS decimal
AS
    BEGIN
    Declare @w_hours decimal
    set @w_hours = 0

    if(@WorkType = ( Select WorkType From EMPDev Where eno = @eno))
      Begin
         Select @w_hours = [Hours] From EMPDev Where eno = @eno
      End
    RETURN @w_hours
    END
-----------------------------------

Select EName,DName, dbo.GetHours(eno,'HOME') As HOME_Hours , dbo.GetHours(eno,'OFFICE') As OFFICE_Hours
 From EMPDev






Bijgupt

Why a UDF?

SELECT ENAME,
          DName,
          SUM(CASE WHEN WorkType = 'HOME' THEN Hours ELSE 0 END) AS Home_Hours,
          SUM(CASE WHEN WorkType = 'OFFICE' THEN Hours ELSE 0 END) AS Office_Hours
FROM Table1
GROUP BY ENAME,
          DName
ORDER BY ENAME,
          DName

To make matters worse, the UDF doesn't do the required SUM ;)

--Jeff Moden

Nice, Peso q'ry is performance wise much better.

Bijgupt