Sometimes we need to show report to show summary information at the intersection of row and columns called crosstab report.

Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically, the following is sample data :

SELECT Year,Quarter,Amount
FROM Pivot
Order BY Year

Output:
Year      Quarter      Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4
A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:























Year
Q1
Q2
Q3
Q4
1990
1.1
1.2
1.3
1.4
1991
2.1
2.2
2.3
2.4

This is the SELECT statement used to create the rotated results:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY Year
This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.


source : MSSQL Server 2000 book

Comments (21)

On January 01, 2006 8:45 PM , Anonymous said...

wuih MYSQL kita lanjut ah...:) suit suit jalannnnnnnnn grak,,,

 
On January 02, 2006 12:48 AM , Budi Raharjo said...

wizz.. it's a great post.

 
On October 31, 2006 6:56 PM , Anonymous said...

Nice site!
[url=http://uzfvvreg.com/fvgc/oafm.html]My homepage[/url] | [url=http://godclaqw.com/muel/kjfl.html]Cool site[/url]

 
On October 31, 2006 6:56 PM , Anonymous said...

Great work!
http://uzfvvreg.com/fvgc/oafm.html | http://akpufaty.com/jmhv/mzln.html

 
On November 15, 2007 6:07 AM , Anonymous said...

Thanks....perfectly simple and very effective explanation!

 
On March 23, 2010 1:31 AM , Anonymous said...

pertusus vacine allergic reaction in adults [url=http://usadrugstoretoday.com/products/lisinopril.htm]lisinopril[/url] consumer health website evaluation http://usadrugstoretoday.com/products/combivent.htm post graduate center for mental health http://usadrugstoretoday.com/categories/antibioticos.htm
national dental centre singapore [url=http://usadrugstoretoday.com/products/evecare.htm]evecare[/url] bcbs of nebraska drug program [url=http://usadrugstoretoday.com/products/lasix.htm]guinea pig hair loss[/url]

 
On March 24, 2010 1:30 PM , Anonymous said...

florida department of health htm [url=http://usadrugstoretoday.com/products/rave--energy-and-mind-stimulator-.htm]rave energy and mind stimulator[/url] smoking blue http://usadrugstoretoday.com/products/acomplia.htm facial muscle stiffness http://usadrugstoretoday.com/products/fml-forte.htm
sleeping on an airbed [url=http://usadrugstoretoday.com/products/lamictal.htm]lamictal[/url] health department thailand [url=http://usadrugstoretoday.com/categories/anti-hongos---anti-ansiedad.htm]antibiotics that cause increase in blood pressure[/url]

 
On March 26, 2010 5:15 PM , Anonymous said...

diaper rash prescription [url=http://usadrugstoretoday.com/products/lexapro.htm]lexapro[/url] mineral fiber insulation http://usadrugstoretoday.com/categories/women-s-health.htm heart and kidney disease high thc http://usadrugstoretoday.com/products/torsemide.htm
scientific hills prescription diet for cats [url=http://usadrugstoretoday.com/products/starlix.htm]starlix[/url] large loss of blood [url=http://usadrugstoretoday.com/products/ayurslim.htm]fraud medical malpractice[/url]

 
On March 28, 2010 1:30 AM , Anonymous said...

urban procedure medical term [url=http://usadrugstoretoday.com/catalogue/c.htm]Online Drugstore[/url] diet pouder drinks http://usadrugstoretoday.com/categories/antivirale.htm calcium supplements during pregnancy http://usadrugstoretoday.com/products/urispas.htm
piedmont health care in statesville [url=http://usadrugstoretoday.com/categories/anti-allergique-et-l-asthme.htm]anti allergique et l asthme[/url] generic drug patent [url=http://usadrugstoretoday.com/products/propecia.htm]iatse health and welfare trust[/url]

 
On March 29, 2010 12:25 AM , Anonymous said...

melty blood react final tuned [url=http://usadrugstoretoday.com/products/altace.htm]altace[/url] harvard women health http://usadrugstoretoday.com/products/pro-erex.htm centrum vitamin silver http://usadrugstoretoday.com/products/lukol.htm
wells fargo equity enhancement program [url=http://usadrugstoretoday.com/products/keftab.htm]keftab[/url] iodine diabetes [url=http://usadrugstoretoday.com/products/frozen--energy-and-libido-enhancer-.htm]how to quit smoking pot[/url]

 
On March 30, 2010 12:34 AM , Anonymous said...

http://rxmeds.in/zantac/ranitidine-150-mg-o-zantac-ayuda-a-aliviar-el-molesto-dolor-d
[url=http://rxmeds.in/alli/alli-michelle]what drugs are used fro parkinsons[/url] how to make drugs out of prescription drugs [url=http://rxmeds.in/xeloda/acute-onset-headache-with-xeloda]acute onset headache with xeloda[/url]
get cialis fast http://rxmeds.in/acomplia/abana-jacobs
[url=http://rxmeds.in/cyproheptadine/cyproheptadine-hcl-4mg-for-animals]metabolism of methamphetamines drug test[/url] peripheral nervous system drugs [url=http://rxmeds.in/ibuprofen/celecoxib-versus-ibuprofen]celecoxib versus ibuprofen[/url]
strongest over the counter drugs http://rxmeds.in/exelon/comed-an-exelon-company
[url=http://rxmeds.in/amiodarone/rate-of-inhibition-of-microsomal-transfer-protein-by-amiodarone]baylor university school of medicine[/url] drug impact body [url=http://rxmeds.in/ibuprofen/lower-back-pain-ibuprofen-or-acetaminophen]lower back pain ibuprofen or acetaminophen[/url] drug interacionis [url=http://rxmeds.in/cytoxan/pet-tinic-lactulose-prednisone-amoxi-drops-cytoxan]pet tinic lactulose prednisone amoxi drops cytoxan[/url]

 
On April 01, 2010 12:49 AM , Anonymous said...

histrionic personality disorder what is [url=http://usadrugstoretoday.com/products/lioresal.htm]lioresal[/url] tachycardia induced cardiomyopathy secondary to anxiety disorder http://usadrugstoretoday.com/products/amitriptyline.htm great smoke http://usadrugstoretoday.com/products/clonidine.htm
antibiotics and virusex [url=http://usadrugstoretoday.com/products/pilocarpine.htm]pilocarpine[/url] blood in bowel motions [url=http://usadrugstoretoday.com/products/lioresal.htm]symptoms of drug allergy[/url]

 
On April 07, 2010 8:42 AM , Anonymous said...

http://www.wastedwebmasters.com/showthread.php?p=145793#post145793 http://jinhaefood.org/phtm/board_view.php?page=1&search_name=off&search_subject=on&search_content=on&keyword=&num=21121 http://www.femdomconnection.com/forums/viewtopic.php?f=9&t=297509 http://www.199offroadhouse.com.ph/199ORH_FORUM/viewtopic.php?f=7&t=3072
http://www.loveforbeauty.com/forum/viewtopic.php?f=2&t=177444 http://www.glamisfriends.com/forum/viewtopic.php?f=7&t=271018 http://www.gosearchlocal.com/forum/viewtopic.php?p=761691#761691 http://www.mmaaustralasia.com/forum/viewtopic.php?f=7&t=10109492
http://mrm.krosnocity.pl/viewtopic.php?f=2&t=21135 http://208.43.17.224/%7Emypower1/bb/viewtopic.php?p=75483#75483 http://www.notogyu.com/cgi-bin/bbs/apeboard_plus.cgi?command=read_message&msgnum=81

 
On April 09, 2010 12:29 AM , Anonymous said...

colon health and weight loss [url=http://usadrugstoretoday.com/products/paxil.htm]paxil[/url] drunk sleeping bitch fucked http://usadrugstoretoday.com/products/zovirax.htm child zyrtec singulair cough http://usadrugstoretoday.com/catalogue/h.htm
vacancies qatar health ministry [url=http://usadrugstoretoday.com/products/femara.htm]femara[/url] best height increase pills [url=http://usadrugstoretoday.com/products/arava.htm]aggressive personality disorder[/url]

 
On April 14, 2010 4:51 PM , Anonymous said...

http://online-health.in/azelastine/azelastine-side-effect
[url=http://online-health.in/avodart/genaric-avodart]buy viagra cialis china shanghai no prescription[/url] most common used anesthesia drugs [url=http://online-health.in/avalide/what-are-the-strengths-for-avalide]what are the strengths for avalide[/url]
drug salespeople see doctors less http://online-health.in/benadryl/benadryl-multisymptom
[url=http://online-health.in/azelastine/purchase-azelastine-online]powerpoint presentations clinical pharmacy[/url] southlight drug raleigh [url=http://online-health.in/avandamet/avandamet-case]avandamet case[/url]
effect of psychoactive drug and pregnant http://online-health.in/avandamet/avandamet-diabetes
[url=http://online-health.in/baclofen/pictures-of-baclofen]nti drugs[/url] benzos and drug tests [url=http://online-health.in/atorvastatin/lipitor-atorvastatin-delaware]lipitor atorvastatin delaware[/url] massachusetts september 2007 drug violations [url=http://online-health.in/antifungal/mango-as-antifungal]mango as antifungal[/url]

 
On April 14, 2010 5:21 PM , Anonymous said...

http://xws.in/aciphex/take-aciphex-20mg
[url=http://xws.in/duloxetine/duloxetine-hcl-60mg]the social transformation of american medicine starr summary[/url] hershey candy that looks like drugs [url=http://xws.in/differin/differin-ge]differin ge[/url]
saturday delivery cod pharmacy http://xws.in/amaryl/adalat-cc-synthroid-pravachol-bontril-amaryl-phencyclidine
[url=http://xws.in/ditropan/can-i-take-dramamine-and-ditropan]youth drug use statics[/url] testosterone gel pharmacy [url=http://xws.in/dosage/dosage-6-lb-dog-atopica]dosage 6 lb dog atopica[/url]
antiarrythmic drugs gss brb http://xws.in/abilify/abilify-a-treatment-option-for-bipolar-1-disorder
[url=http://xws.in/enhancer/sperm-enhancer]mortons pharmacy coupons[/url] become a drug counselor connecticut [url=http://xws.in/alfuzosin/flomax-alfuzosin]flomax alfuzosin[/url] drug interactions depakote er and effexor xr [url=http://xws.in/dutasteride/dutasteride-avodart-boots-thailand]dutasteride avodart boots thailand[/url]

 
On April 14, 2010 9:34 PM , Anonymous said...

http://meen.in/cardizem/cardizem-sa
[url=http://meen.in/fluticasone/fluticasone-propionate-discus]anitrejection drugs for donor transplants[/url] cvs pharmacy chicago [url=http://meen.in/flonase]flonase[/url]
ativan killer drug http://meen.in/celadrin/health-benefits-of-celadrin
[url=http://meen.in/fexofenadine/fexofenadine-interactions]sex and drugs pick up game[/url] what drugs can do too your body [url=http://meen.in/fluconazole/fluconazole-sale]fluconazole sale[/url]
grapefruit and blood pressure medicine http://meen.in/ciprofloxacin/ciprofloxacin-and-breastfeeding
[url=http://meen.in/clomid/clomid-hcg-injection-results]drug injected foam closure technique helps wounds heal[/url] indiana ohio illinois online pharmacy [url=http://meen.in/fda/black-iron-oxide-fda]black iron oxide fda[/url] levitra sales [url=http://meen.in/clomiphene/response-to-clomiphene]response to clomiphene[/url]

 
On April 15, 2010 8:41 PM , Anonymous said...

http://poow.in/methylprednisolone/methylprednisolone-acetate
[url=http://poow.in/geodon/geodon-suspension-sales-2007]fda approaved branded drugs[/url] australia generic in levitra sales [url=http://poow.in/gabapentin/no-prescription-cheap-gabapentin]no prescription cheap gabapentin[/url]
how to make the drug ice http://poow.in/methylprednisolone/methylprednisolone-prodrug
[url=http://poow.in/methylsulfonylmethane/lignisul-msm-methylsulfonylmethane-side-effects]cvs drugstore michigan[/url] dating after drug and alcohol rehab [url=http://poow.in/rhinitis/treatment-of-rhinitis]treatment of rhinitis[/url]
ocd treatment no drugs http://poow.in/zyban/zyban-for-weight-loss
[url=http://poow.in/levofloxacin/levofloxacin-use-in-dogs]list of party drugs[/url] free erectile disfunction tablet [url=http://poow.in/mestinon/mestinon-onset-of-action]mestinon onset of action[/url] antidepressant medicine welbrouton [url=http://poow.in/meloxicam/meloxicam-and-flexeril]meloxicam and flexeril[/url]

 
On April 22, 2010 5:28 PM , Anonymous said...

mercedes aps http://automoblog.in/pontiac/pontiac-g-5 auto insurance albion mi
[url=http://automoblog.in/morgan/morgan-county-kentucky-government]auto salvage pa[/url] how to terminate an auto lease early [url=http://automoblog.in/volvo/is-volvo-850-a-good-car]is volvo 850 a good car[/url]
carl beal stock racing http://automoblog.in/ford/ford-diesel-4x4-e-bay
[url=http://automoblog.in/chrysler/chrysler-tommohawk]auto milker penis[/url] auto body manager resumes [url=http://automoblog.in/scooters/three-wheeled-scooters]three wheeled scooters[/url]
automobile handling characteristics volkswagen beetle http://automoblog.in/royce/rolls-royce-bently-car-club
[url=http://automoblog.in/seat/e30-leather-seat-covers]auto center hump cup holder[/url] auto repair newport nh [url=http://automoblog.in/pontiac/pontiac-firebird-2000]pontiac firebird 2000[/url]

 
On April 30, 2010 12:17 AM , Anonymous said...

childrens designer clothing http://topcitystyle.com/white-dressy-tops-color4.html bacio fashion show [url=http://topcitystyle.com/?action=products&product_id=1872]womens clothes for men[/url] designer homes
http://topcitystyle.com/frankie-morello-funky-t-shirt-for-men-black-item1797.html laurentian mountains map [url=http://topcitystyle.com/black-grey-pullover-color24.html]massart fashion designer[/url]

 
On May 02, 2010 10:55 PM , Anonymous said...

current fashion in spain http://topcitystyle.com/red-grey-color219.html rejects shoes [url=http://topcitystyle.com/white-dress-shirts-color4.html]britany spears clothes change pics[/url] ny clothes
http://topcitystyle.com/39-on-sale-size28.html gucci positano [url=http://topcitystyle.com/-wallet-accessories-category78.html]designer dog[/url]