How to use NULL when using PIVOT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use NULL when using PIVOT

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-04-15 : 06:35:49

Hi,

I have below table in my sp

IF OBJECT_ID('tempdb.dbo.#tempEmployees') IS NOT NULL
DROP TABLE #tempEmployees

Create table #tempEmployees
(
employeeID INT,
bonusId INT,
totalSeconds REAL
)

INSERT INTO #tempEmployees VALUES(1,14,2600.99)
INSERT INTO #tempEmployees VALUES(1,NULL,33.33)
INSERT INTO #tempEmployees VALUES(1,14,2500)
INSERT INTO #tempEmployees VALUES(1,38,30.04)
INSERT INTO #tempEmployees VALUES(1,NULL,2533)

INSERT INTO #tempEmployees VALUES(2,NULL,2600.99)
INSERT INTO #tempEmployees VALUES(2,NULL,33.33)

INSERT INTO #tempEmployees VALUES(3,14,2500)
INSERT INTO #tempEmployees VALUES(3,38,30.04)
INSERT INTO #tempEmployees VALUES(3,41,2533)
INSERT INTO #tempEmployees VALUES(3,NULL,2533099)

SELECT
employeeId,
bonusId,
SUM(totalSeconds)/3600.0 AS totalHours
FROM
#tempEmployees
GROUP BY
employeeId,
bonusId
ORDER BY
employeeId

want to apply the PIVOT so tried like below query and I am getting no error when i used the below query..

SELECT employeeID,[14]/3600.0 AS [SalesBouns - 14], [38]/3600.0 AS [New-Hire-Bonus - 38], [41]/3600.0 AS [previousArrieros - 41]
FROM
(SELECT employeeID,BonusID,totalSeconds
FROM #tempEmployees) t
PIVOT
(
SUM(totalSeconds)
FOR BonusID IN ([14],[38],[41])
) AS pvt
ORDER BY employeeID

if an employee not having any bonusId i.e., NULL
want to show those hours also so i tried like below..

PIVOT
(
SUM(totalSeconds)
FOR BonusID IN ([NULL],[14],[38],[41])
) AS pvt

am getting error please help me out for below query...

SELECT employeeID,[14]/3600.0 AS [SalesBouns - 14], [38]/3600.0 AS [New-Hire-Bonus - 38], [41]/3600.0 AS [previousArrieros - 41]
FROM
(SELECT employeeID,BonusID,totalSeconds
FROM #tempEmployees) t
PIVOT
(
SUM(totalSeconds)
FOR BonusID IN ([NULL],[14],[38],[41])
) AS pvt
ORDER BY employeeID

dmilam
Posting Yak Master

185 Posts

Posted - 2011-04-15 : 12:00:50

You don't say what the error is exactly, but avoid NULL. Use a literal such as 'NA' (for 'not available'), for example.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-15 : 12:09:39

Along those lines, something like this:

SELECT employeeID,[14]/3600.0 AS [SalesBouns - 14], [38]/3600.0 AS [New-Hire-Bonus - 38], [41]/3600.0 AS [previousArrieros - 41]
FROM
(SELECT employeeID,IsNull(BonusID,-1) BonusID,totalSeconds
FROM #tempEmployees) t
PIVOT
(
SUM(totalSeconds)
FOR BonusID IN ([-1],[14],[38],[41])
) AS pvt
ORDER BY employeeID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources