SQL Pivot query using case instead of PIVOT

[JustATheory]

Greetings,

Is it possible to use CASE to create a two column pivot, PIVOT is not an option with the database I'm working with. I need the count of Txn and the sum of Amount. Is it possible without Union All?

Original Table

Account Txn Amount
123 111 $10.00
456 222 $15.00
789 333 $20.00
234 111 $25.00
567 222 $30.00
891 333 $35.00

Results Table

Account TXN 111 Amt 111 TXN 222 Amt 222
123 1 $10.00
456 1 $15.00
234 1 $25.00
567 1 $30.00


Any help is greatly appreciated, thanks in advance.

JustATheory

 

[gmmastros]

Do you mean something like this?

Declare @Temp Table(Account Int, Txn Int, Amount Decimal(10,2))

Insert Into @Temp Values(123,    111,    10.00)
Insert Into @Temp Values(456,    222,    15.00)
Insert Into @Temp Values(789,    333,    20.00)
Insert Into @Temp Values(234,    111,    25.00)
Insert Into @Temp Values(567,    222,    30.00)
Insert Into @Temp Values(891,    333,    35.00)

Select Account,
       Count(Case When Txn = 111 Then 1 End) As Txn111Count,
       Sum(Case When Txn = 111 Then Amount End) As Txn111Sum,
       Count(Case When Txn = 222 Then 1 End) As Txn222Count,
       Sum(Case When Txn = 222 Then Amount End) As Txn222Sum
From   @Temp
Group By Account

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JustATheory]

Thanks George,

Works like a charm! I wrote something similar and it didn't work, sometimes it's just the extra set of eyes.

Thanks much,
JustATheory