Convert table to cross-tab? - 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

Convert table to cross-tab?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2011-08-05 : 16:31:04

I'd like to convert this table:

DateKey CustID CustName Amount
20100701 123454 Cust ABC 0.00
20100703 327645 Cust XYZ 0.00
20100705 466506 Cust BBB 0.00
20100710 217728 Cust MMM 0.00
20100711 884013 Cust WWW 0.00
20100720 300182 Cust PBR 4127.00
20100805 659914 Cust FBI 1763.00
20100812 333856 Cust CIA 821.00
20110702 123457 Cust ABC 1439.00
20110707 327648 Cust XYZ 1152.00
20110708 466508 Cust BBB 985.00
20110724 217725 Cust MMM 23671.00
20110726 884013 Cust WWW 33406.00
20110801 300182 Cust PBR 0.00
20110805 659912 Cust FBI 1689.00
20110810 333852 Cust CIA 941.00

Into a table with the following columns with the above data:

CustName CustID AmountsBetween20100701and20110630 AmountsBetween20110701and20120630 Total

Any advice appreciated, thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-05 : 20:04:00

[code]
select CustName, CustID,
AmountsBetween20100701and20110630 = sum(case when DateKey between '20100701' and '20110630' then Amount end),
AmountsBetween20110701and20120630 = sum(case when DateKey between '20110701' and '20120630' then Amount end),
total = sum(Amount)
from yourtable
group by CustName, CustID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources