SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pivot with case
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 07/21/2013 :  17:27:16  Show Profile  Reply with Quote
hello there

I have a pivot script but I would like to use a case statement,
See below script and result

select top 10 id,

[1421],
[1420],
[1419],
[1418]

from (

select Purchaseid,Purchaseid as id, occasionid, transactionid
from ticket_facts ) as query

pivot (count(transactionid) for purchaseid

in (
[1421],
[1420],
[1419],
[1418])) as piv


id 1421 1420 1419 1418
210605 5 6 0 0
6964 0 0 4 0
30520 7 0 0 0
85874 0 21 0 2
1979 0 0 6 0
63002 6 0 0 0
54991 0 0 0 0
165489 0 0 52 0
204218 15 0 0 23
30364 0 0 0 0

I would like the count to be

case (count(transactionid) when >0 then 1 else 0) end

how would I use the case statement with the pivot.

Regards

Rob

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/21/2013 :  17:38:14  Show Profile  Reply with Quote
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, transactionid
from ticket_facts ) as query
pivot (count(transactionid) for purchaseid
in (
[1421],
[1420],
[1419],
[1418])) as piv;
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 07/21/2013 :  17:51:09  Show Profile  Reply with Quote
cool cheers

declare @positive as int
declare @negative as int
declare @zero as int

set @positive = 56
set @negative = -200
set @zero = 0

select sign(@positive)
select sign(@negative)
select sign(@zero)
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/21/2013 :  18:05:25  Show Profile  Reply with Quote
quote:
Originally posted by masterdineen

cool cheers

declare @positive as int
declare @negative as int
declare @zero as int

set @positive = 56
set @negative = -200
set @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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  01:23:31  Show Profile  Reply with Quote
quote:
Originally posted by masterdineen

cool cheers

declare @positive as int
declare @negative as int
declare @zero as int

set @positive = 56
set @negative = -200
set @zero = 0

select sign(@positive)
select sign(@negative)
select sign(@zero)


sounds like this to me


select 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 end

from (

select Purchaseid,Purchaseid as id, occasionid, transactionid,
case when  
from ticket_facts ) as query

pivot (count(transactionid) for purchaseid

in (
[1421],
[1420],
[1419],
[1418])) as piv


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 07/22/2013 :  03:10:34  Show Profile  Reply with Quote
Thank you Visakh16,

have a good week
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  03:53:52  Show Profile  Reply with Quote
welcome
you too have a great one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  05:39:18  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by masterdineen

cool cheers

declare @positive as int
declare @negative as int
declare @zero as int

set @positive = 56
set @negative = -200
set @zero = 0

select sign(@positive)
select sign(@negative)
select sign(@zero)


sounds like this to me


select 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 query

pivot (count(transactionid) for purchaseid

in (
[1421],
[1420],
[1419],
[1418])) as piv


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 end

sign([1421])[1421],
sign([1420])[1420],
sign([1419])[1419],
sign([1418])[1418]


Edited by - sigmas on 07/22/2013 05:46:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  05:58:35  Show Profile  Reply with Quote
quote:
Originally posted by sigmas

quote:
Originally posted by visakh16

quote:
Originally posted by masterdineen

cool cheers

declare @positive as int
declare @negative as int
declare @zero as int

set @positive = 56
set @negative = -200
set @zero = 0

select sign(@positive)
select sign(@negative)
select sign(@zero)


sounds like this to me


select 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 query

pivot (count(transactionid) for purchaseid

in (
[1421],
[1420],
[1419],
[1418])) as piv


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 end

sign([1421])[1421],
sign([1420])[1420],
sign([1419])[1419],
sign([1418])[1418]




I was answering to OPs question below

how would I use the case statement with the pivot

And 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 etc

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186960

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/22/2013 :  06:29:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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_Facts
GROUP BY	TransactionID;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  07:31:55  Show Profile  Reply with Quote
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_Facts
GROUP BY	TransactionID;

Edited by - sigmas on 07/22/2013 09:21:48
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/22/2013 :  08:30:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do you see a difference in performnance by using IIF instead of CASE?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  09:48:21  Show Profile  Reply with Quote
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

Edited by - sigmas on 07/22/2013 10:18:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  09:53:08  Show Profile  Reply with Quote
Internally IIF is evaluated as a CASE ..WHEN condition itself so the two queries should be equivalent.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 07/22/2013 :  09:56:21  Show Profile  Reply with Quote
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!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  10:15:05  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  10:31:45  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




It is just a conspiracy, you can see

Database Development MCTS, MCTIP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/22/2013 :  11:01:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, the "MAX(CASE..." thingy is slightly faster than PIVOT.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000