I want zeroes in my pivot intead of NULLs

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

General SQL Server Forums

New to SQL Server Programming

I want zeroes in my pivot intead of NULLs

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-10 : 19:07:57

How can i get zeroes in a pivot without creating a temp table? I wrote a query to get zeroes, but I used temp tables. iS THERE Any way to do it without creating temp tables? My solution is below where it says ALTERNATIVE ANSWER.

------------------------------------------------------------------------------------------------------------
-----> QUESTION:
------------------------------------------------------------------------------------------------------------

DECLARE @Bit AS INT = 1

SELECT *
FROM (
SELECT entitylist.comment AS 'Authenticator',
Count(users.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
JOIN sso.userauthenticator WITH (readuncommitted)
ON userauthenticator.userid = users.userid
JOIN sso.entitylist WITH (readuncommitted)
ON entitylist.entityid = userauthenticator.providerid
WHERE ( sso.users.status & @Bit ) = @Bit
AND entitylist.entityname = 'UserAuthenticator.ProviderID'
AND users.userid NOT IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (
readuncommitted)
GROUP BY userauthenticator.userid
HAVING Count(*) > 1)
GROUP BY entitylist.comment,
Datepart(dd, sso.users.createddate)
UNION ALL

SELECT 'Multiple' AS 'Authenticator',
Count(DISTINCT userauthenticator.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
JOIN sso.userauthenticator WITH (readuncommitted)
ON userauthenticator.userid = users.userid
WHERE ( sso.users.status & @Bit ) = @Bit
AND users.userid IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (readuncommitted)
GROUP BY userauthenticator.userid
HAVING Count(*) > 1)
GROUP BY Datepart(dd, sso.users.createddate)
UNION ALL
SELECT 'Organic' AS 'Authenticator',
Count(users.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
WHERE ( sso.users.status & @Bit ) = @Bit
AND users.userid NOT IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (
readuncommitted))
AND passwordquestion <>
'What is the postal code in the ad that you first submitted?'
GROUP BY Datepart(dd, sso.users.createddate)
UNION ALL
SELECT 'Ad Placement' AS 'Authenticator',
Count(users.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
WHERE ( sso.users.status & @Bit ) = @Bit
AND users.userid NOT IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (
readuncommitted))
AND passwordquestion =
'What is the postal code in the ad that you first submitted?'
GROUP BY Datepart(dd, sso.users.createddate)) drv

PIVOT ( SUM(drv.UserCount)
FOR Authenticator IN
([Ad Placement], [Facebook],[Google],[Multiple],[Organic],[Yahoo])
) AS Result

----select *from #derived ORDER BY [Day]
----select Authenticator, UserCount, [Day] from #derived ORDER BY [Day]

--SELECT [Day], [Ad Placement], Facebook, Google, Multiple, Organic, Yahoo from #Derived
--PIVOT ( SUM(UserCount) for Authenticator IN
-- ([Ad Placement], [Facebook],[Google],[Multiple],[Organic],[Yahoo])
-- ) AS Result
--ORDER BY [Day]
------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
-----> ALTERNATIVE ANSWER:
------------------------------------------------------------------------------------------------------------

drop table #Derived
go

DECLARE @Bit AS INT = 1

SELECT *
INTO #Derived
FROM (

SELECT entitylist.comment AS 'Authenticator',
Count(users.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
JOIN sso.userauthenticator WITH (readuncommitted)
ON userauthenticator.userid = users.userid
JOIN sso.entitylist WITH (readuncommitted)
ON entitylist.entityid = userauthenticator.providerid
WHERE ( sso.users.status & @Bit ) = @Bit
AND entitylist.entityname = 'UserAuthenticator.ProviderID'
AND users.userid NOT IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (
readuncommitted)
GROUP BY userauthenticator.userid
HAVING Count(*) > 1)
GROUP BY entitylist.comment,
Datepart(dd, sso.users.createddate)
UNION ALL

SELECT 'Multiple' AS 'Authenticator',
Count (DISTINCT userauthenticator.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
JOIN sso.userauthenticator WITH (readuncommitted)
ON userauthenticator.userid = users.userid
WHERE ( sso.users.status & @Bit ) = @Bit
AND users.userid IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (readuncommitted)
GROUP BY userauthenticator.userid
HAVING Count(*) > 1)
GROUP BY Datepart(dd, sso.users.createddate)
UNION ALL
SELECT 'Organic' AS 'Authenticator',
Count(users.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
WHERE ( sso.users.status & @Bit ) = @Bit
AND users.userid NOT IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (
readuncommitted))
AND passwordquestion <>
'What is the postal code in the ad that you first submitted?'
GROUP BY Datepart(dd, sso.users.createddate)
UNION ALL
SELECT 'Ad Placement' AS 'Authenticator',
Count(users.userid) AS 'UserCount',
Datepart(dd, sso.users.createddate) AS 'Day'
FROM sso.users WITH (readuncommitted)
WHERE ( sso.users.status & @Bit ) = @Bit
AND users.userid NOT IN (SELECT userauthenticator.userid
FROM sso.userauthenticator WITH (
readuncommitted))
AND passwordquestion =
'What is the postal code in the ad that you first submitted?'
GROUP BY Datepart(dd, sso.users.createddate)) drv

--PIVOT ( SUM(drv.UserCount)
-- FOR Authenticator IN
-- ([Ad Placement], [Facebook],[Google],[Multiple],[Organic],[Yahoo])
-- ) AS Result

----select *from #derived ORDER BY [Day]
----select Authenticator, UserCount, [Day] from #derived ORDER BY [Day]

SELECT [Day], ISNULL([Ad Placement], 0) AS [Ad Placement], ISNULL(Facebook, 0) AS Facebook, ISNULL(Google, 0) AS Google, ISNULL(Multiple, 0) AS Multiple, ISNULL(Organic, 0) AS Organic, ISNULL(Yahoo, 0) AS Yahoo from #Derived
PIVOT ( SUM(UserCount) for Authenticator IN
([Ad Placement], [Facebook],[Google],[Multiple],[Organic],[Yahoo])
) AS Result
ORDER BY [Day]
------------------------------------------------------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 20:01:24

you need to include logic to cross join to master table on the field that contain values [Ad Placement], [Facebook],[Google],[Multiple],[Organic],[Yahoo] etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-10 : 20:03:19

Thanks for the reply, but i dont undestand what you mean.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 20:10:37

show your table with some sample data. then i can show you sample code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources