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
| 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
source : MSSQL Server 2000 book
7:32 PM |
Category: |
21
comments
Comments (21)
wuih MYSQL kita lanjut ah...:) suit suit jalannnnnnnnn grak,,,
wizz.. it's a great post.
Nice site!
[url=http://uzfvvreg.com/fvgc/oafm.html]My homepage[/url] | [url=http://godclaqw.com/muel/kjfl.html]Cool site[/url]
Great work!
http://uzfvvreg.com/fvgc/oafm.html | http://akpufaty.com/jmhv/mzln.html
Thanks....perfectly simple and very effective explanation!
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]
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]
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]
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]
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]
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]
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]
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
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]
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]
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]
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]
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]
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]
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]
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]