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.
Author |
Topic |
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-07-21 : 17:27:16
|
hello thereI have a pivot script but I would like to use a case statement,See below script and resultselect top 10 id, [1421],[1420],[1419],[1418]from (select Purchaseid,Purchaseid as id, occasionid, transactionid from ticket_facts ) as querypivot (count(transactionid) for purchaseidin ([1421],[1420],[1419],[1418])) as pivid 1421 1420 1419 1418210605 5 6 0 06964 0 0 4 030520 7 0 0 085874 0 21 0 21979 0 0 6 063002 6 0 0 054991 0 0 0 0165489 0 0 52 0204218 15 0 0 2330364 0 0 0 0I would like the count to becase (count(transactionid) when >0 then 1 else 0) endhow would I use the case statement with the pivot.RegardsRob |
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-21 : 17:38:14
|
It is so easy...select top 10 id,sign([1421])[1421],sign([1420])[1420],sign([1419])[1419],sign([1418])[1418]from (select Purchaseid,Purchaseid as id, occasionid, transactionidfrom ticket_facts ) as querypivot (count(transactionid) for purchaseidin ([1421],[1420],[1419],[1418])) as piv; |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-07-21 : 17:51:09
|
cool cheersdeclare @positive as intdeclare @negative as intdeclare @zero as intset @positive = 56set @negative = -200set @zero = 0 select sign(@positive)select sign(@negative)select sign(@zero) |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-21 : 18:05:25
|
quote: Originally posted by masterdineen cool cheersdeclare @positive as intdeclare @negative as intdeclare @zero as intset @positive = 56set @negative = -200set @zero = 0 select sign(@positive)select sign(@negative)select sign(@zero)
So?No need variable. constant is enough select sign(56)select sign(-200)select sign(0) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 01:23:31
|
quote: Originally posted by masterdineen cool cheersdeclare @positive as intdeclare @negative as intdeclare @zero as intset @positive = 56set @negative = -200set @zero = 0 select sign(@positive)select sign(@negative)select sign(@zero)
sounds like this to meselect top 10 id, case when [1421] > 0 then 1 else 0 end,case when [1420] > 0 then 1 else 0 end,case when [1419] > 0 then 1 else 0 end,case when [1418] > 0 then 1 else 0 endfrom (select Purchaseid,Purchaseid as id, occasionid, transactionid,case when from ticket_facts ) as querypivot (count(transactionid) for purchaseidin ([1421],[1420],[1419],[1418])) as piv ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-07-22 : 03:10:34
|
Thank you Visakh16,have a good week |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 03:53:52
|
welcomeyou too have a great one------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-22 : 05:39:18
|
quote: Originally posted by visakh16
quote: Originally posted by masterdineen cool cheersdeclare @positive as intdeclare @negative as intdeclare @zero as intset @positive = 56set @negative = -200set @zero = 0 select sign(@positive)select sign(@negative)select sign(@zero)
sounds like this to meselect top 10 id, case when [1421] > 0 then 1 else 0 end as [1421],case when [1420] > 0 then 1 else 0 end as [1420],case when [1419] > 0 then 1 else 0 end as [1419],case when [1418] > 0 then 1 else 0 end as [1418]from (select Purchaseid,Purchaseid as id, occasionid, transactionid,case when from ticket_facts ) as querypivot (count(transactionid) for purchaseidin ([1421],[1420],[1419],[1418])) as piv ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
The COUNT value never be zero. so my SIGN function always has correct result.So, what's the problem with it ??This is shorter or that ?? also you forgot to aliasing columns!case when [1421] > 0 then 1 else 0 end,case when [1420] > 0 then 1 else 0 end,case when [1419] > 0 then 1 else 0 end,case when [1418] > 0 then 1 else 0 endsign([1421])[1421],sign([1420])[1420],sign([1419])[1419],sign([1418])[1418] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 05:58:35
|
quote: Originally posted by sigmas
quote: Originally posted by visakh16
quote: Originally posted by masterdineen cool cheersdeclare @positive as intdeclare @negative as intdeclare @zero as intset @positive = 56set @negative = -200set @zero = 0 select sign(@positive)select sign(@negative)select sign(@zero)
sounds like this to meselect top 10 id, case when [1421] > 0 then 1 else 0 end as [1421],case when [1420] > 0 then 1 else 0 end as [1420],case when [1419] > 0 then 1 else 0 end as [1419],case when [1418] > 0 then 1 else 0 end as [1418]from (select Purchaseid,Purchaseid as id, occasionid, transactionid,case when from ticket_facts ) as querypivot (count(transactionid) for purchaseidin ([1421],[1420],[1419],[1418])) as piv ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
The COUNT value never be zero. so my SIGN function always has correct result.So, what's the problem with it ??This is shorter or that ?? also you forgot to aliasing columns!case when [1421] > 0 then 1 else 0 end,case when [1420] > 0 then 1 else 0 end,case when [1419] > 0 then 1 else 0 end,case when [1418] > 0 then 1 else 0 endsign([1421])[1421],sign([1420])[1420],sign([1419])[1419],sign([1418])[1418]
I was answering to OPs question belowhow would I use the case statement with the pivotAnd I think OP got what he looked for and has moved on.Stop being an online compiler if you can and give more attention on the approach used rather on typos etchttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186960------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 06:29:31
|
[code]SELECT TransactionID, MAX(CASE WHEN PurchaseID = 1421 THEN 1 ELSE 0 END) AS [1421], MAX(CASE WHEN PurchaseID = 1420 THEN 1 ELSE 0 END) AS [1420], MAX(CASE WHEN PurchaseID = 1419 THEN 1 ELSE 0 END) AS [1419], MAX(CASE WHEN PurchaseID = 1418 THEN 1 ELSE 0 END) AS [1418]FROM dbo.Ticket_FactsGROUP BY TransactionID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-22 : 07:31:55
|
Shorter than SwePeso!(compatibility: SQL Server 2012)SELECT TransactionID, MAX(IIF(PurchaseID = 1421, 1, 0)) AS [1421], MAX(IIF(PurchaseID = 1420, 1, 0)) AS [1420], MAX(IIF(PurchaseID = 1419, 1, 0)) AS [1419], MAX(IIF(PurchaseID = 1418, 1, 0)) AS [1418]FROM dbo.Ticket_FactsGROUP BY TransactionID; |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 08:30:35
|
Do you see a difference in performnance by using IIF instead of CASE? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-22 : 09:48:21
|
quote: Originally posted by SwePeso Do you see a difference in performnance by using IIF instead of CASE? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
I posted the query with IIF just for shorting the code. Is this problematic for you?Also, do you see a difference in performnance by using GROUP BY and aggregation instead of PIVOT?SQL Server Database Development MCTS, MCITP |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 09:53:08
|
Internally IIF is evaluated as a CASE ..WHEN condition itself so the two queries should be equivalent.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-22 : 09:56:21
|
quote: Originally posted by sigmas
quote: Originally posted by SwePeso Do you see a difference in performnance by using IIF instead of CASE? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
I posted the query with IIF just for shorting the code. Is this problematic for you?Also, do you see a difference in performnance by using GROUP BY and aggregation instead of PIVOT?SQL Server Database Development MCTS, MCITP
Hah! Caught you sigmas!! You have a spelling mistake there. It should be "Performance". Of course, I am teasing you sigmas, don't take it seriously. I find your candor and honesty and attention to detail very refreshing!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 10:15:05
|
quote: Originally posted by James K
quote: Originally posted by sigmas
quote: Originally posted by SwePeso Do you see a difference in performnance by using IIF instead of CASE? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
I posted the query with IIF just for shorting the code. Is this problematic for you?Also, do you see a difference in performnance by using GROUP BY and aggregation instead of PIVOT?SQL Server Database Development MCTS, MCITP
Hah! Caught you sigmas!! You have a spelling mistake there. It should be "Performance". Of course, I am teasing you sigmas, don't take it seriously. I find your candor and honesty and attention to detail very refreshing!!
But certainly I don't think it was a nice thing quoting another persons post and then striking through his signature.See the post on 07/22/2013 : 09:48:21------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-22 : 10:31:45
|
quote: Originally posted by visakh16
quote: Originally posted by James K
quote: Originally posted by sigmas
quote: Originally posted by SwePeso Do you see a difference in performnance by using IIF instead of CASE? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
I posted the query with IIF just for shorting the code. Is this problematic for you?Also, do you see a difference in performnance by using GROUP BY and aggregation instead of PIVOT?SQL Server Database Development MCTS, MCITP
Hah! Caught you sigmas!! You have a spelling mistake there. It should be "Performance". Of course, I am teasing you sigmas, don't take it seriously. I find your candor and honesty and attention to detail very refreshing!!
But certainly I don't think it was a nice thing quoting another persons post and then striking through his signature.See the post on 07/22/2013 : 09:48:21------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
It is just a conspiracy, you can seeDatabase Development MCTS, MCTIP |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 11:01:14
|
Yes, the "MAX(CASE..." thingy is slightly faster than PIVOT. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|