pivot with case - SQL Server Forums

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

pivot with case

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-21 : 17:27:16

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

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, transactionid
from ticket_facts ) as query
pivot (count(transactionid) for purchaseid
in (
[1421],
[1420],
[1419],
[1418])) as piv;

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-21 : 17:51:09

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)

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-21 : 18:05:25

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 01:23:31

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

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

welcome

you too have a great one

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

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

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_Facts
GROUP 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_Facts
GROUP 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 MVP
http://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 MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

It is just a conspiracy, you can see

Database 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources