Üks lahtipivootimise näide

Üks lahtipivootimise näide

 

 

Kategooria    [Valige kategooria või tippige uus]

 

 

Mõni päev tagasi tuli mul ette ülesanne – üks suht keeruline risttabel “lahti pivootida” ehk tagasi lametabeliks muuta

Väljakutseks muutis selle vajadus teha kõik valemitega ning vältida igasuguseid makrosid ja mis kõige olulisem

Ma tegin seda!

 

Ma tegin ka ühe lihtsustatud näite aj jagan seda heal meelel

http://1drv.ms/1JFMtGA (siin on toodud näide Exceli kujul)

 

Väike samm-sammuline selgitus

Minu näites on algne tabel klassikaline “risttabel”, kus erinevates veergudes atribuudid ja ridades kirjed ning atribuutide väärtused.

 

Lahtipivootimise tulemuseks peaks olema umbes selline tabel:

Atribuudid eraldi ridadel ja ainult need, mis tabelis ka olemas

Hakatuseks teen vahetabeli. Võib ka lihtsalt laiendada lähtetabelit, kuid ohutuse mõttes tegin vahetabeli. Tegelik algne risttabel ei olnud tabel ja koosnes veel eri laiusega sektsioonidest.

  • Esimene veerg:     Nr =ROW()-ROW(Tabel2)+1

See trikk võimaldab genereerida tabelisse numbreid, kui tabelit allapoole laiendada. Ja see ei sõltu tabeli asukohast

  • Teine veerg:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

See on üks teine (vana) trikk, mis võimaldab VLOOKUPiga logeda teisest tabelist andmeid veeru nime järgi

  • Kolmas veerg annab atribuutide arvu (> 0)

=COUNTIF(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”));”>0″)

Antud näite tegin, et ta ei sõltuks atribuudiveergude arvust – reaalses elus ei pruugi atribuudid olla sarnaste niumedega, sel juhul võiks valem olla umbes selline

=COUNTIF(OFFSET(Tabel1[Esimene atribuut];[@Nr]-1;0;1; — siia atribuutide arv tabelis –);”>0″)

Minu algses tabelis oli 17 atribuudiveergu

  • Neljandaks arvutan reanumbri, millest alates tulemuses on SELLE kirje atribuudid

=SUM(Tabel2[[#Päised];[NumOfAttr]]:[@NumOfAttr])-[@NumOfAttr]+1

See on klassikaline “kasvav summa” näide tabeliformaadis

  • Viies veerg on bitmask, mis näitab, millised atribuudid antud kirjes on aj millised mitte

     

Mask =SUMPRODUCT(

        (OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”))>0)

        *    

        (2^(8-COLUMN(OFFSET($A$1;0;0;1;COUNTIF(Tabel1[#Päised];”Attr*”)))))

)

See SUMPRODUCT arvutab bitmaski kasutades kahte vektorit:

Üks vektor on {true,false,…} jada, mis võrdleb atribuudi olemasolu (antud juhul >0)

Teine vektor on 2-astemete (pööratd) jada kujul {256, 128, 64, …}

Nende kahe vektori korrutis kokkuliidetuna annabki bitmaski

  • Kuues veerg on seesama aga kahendkujul (0-1 jada)

Bitmask =DEC2BIN([@Mask];8)&”0″

Juhul kui atribuute on üle 9, tuleb see arvutus tükeldada, kuna dec2bin ei võimalda üle 9 biti

Sellega on vahetabel otsas

Nüüd teeme tulemustabeli

  • Esimene:     NR =ROW()-ROW(Tabel3)+1 — sama trikk, mis eelmises

Siin on maitse küsimus, kas teha algusest peale maksimaalsuurusega tabel ja peita ülearused read IF-funktsiooniga või siis teha tabel ja “venitada” see vajaliku pikkuseni

  • Teine:         LineNr =MATCH([@Nr];Tabel2[ResLine];1)

Annab mulle rea numbri lähte- (ja vahe)tabelis

  • Kolmas:     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

Annab mulle atribuudi järjekorranumbri

Kõik eelnev tagab, et mul on vaid need read, mis lähtetabelis ja vaid need atribuudid, mis lähtetabelis olemas, atribuudiridade arv vastab atribuutude arvule ja atribuutide numbrid algavad 1-st

  • Neljas:         AtrCol =IF([@ResNr]=1;

                FIND(“1″;INDEX(Tabel2[BitMask];[@LineNr]));

                FIND(“1″;INDEX(Tabel2[BitMask];[@LineNr]);OFFSET([@AttrCol];-1;0)+1)

                )

See leiab iga järgmise atribuudi positsiooni tabelis. Tegelikult “1” positsiooni(d) bitmaskis

Kui atribuudi number on 1, siis leiame lihtsa FIND-funktsiooniga. Järgmiste atribuutidega kasutame FIND funktsiooni kolmandat atribuuti (millisest kohast alates), mille me loeme eelmisest reast.

Edasine on lihtne:

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — taas see vana trikk

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — siin loeme atribuudi nime päisereast

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — siin samamoodi andmereast

 

Igasugune tagasiside on teretulnud

Henn

Posted in Määratlemata | Lisa kommentaar

Simple unpivot example in excel

One unpivoting example

(kohe teen sama loo ka eesti keeles)

Few days ago I had challenge to unpivot one quite complex crosstable.

And challenge was to use formulas to repeat in future with new data and prevent any macros

 

I did it!

 

I made simplification as example I’d like to share

 

http://1drv.ms/1JFMtGA (there is sample workbook)

 

Some step-by-step explanation

(sorry – I use ee-location, so in formulas I use ; rather than , and [#header..] is replace to [#päised..])

 

On my sample I have table with names and 8 columns with attributes

 

Name, Attr1, Attr2, Attr3, …

Henn, , 3, 5

Ants, 1, , 4

Peeter, , , 7

 

Unpivoting result have to be something like

Name, AttrName, AttrValue

Henn, Attr2, 3

Henn, Attr3, 5

Ants, Attr1, 1

Ants, Attr3, 4

Peeter, Attr3, 7

 

1st I create intermediate table. Actually I can extend origin table, but for safety I created new one

btw – in origin – the starting cross wasn’t table and contains several sections

  • 1st column:     Nr =ROW()-ROW(Tabel2)+1

this is trick to generate numbers to table – insensitive of table placement

  • 2nd column:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

this is another trick to get with VLOOKUP matching attribute from lookup table

  • 3rd in number of attributes > 0

=COUNTIF(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”));”>0″)

this is actually working with different table with different number of attributes in my actual example I had 17 attributes and with different names.

There I simply count how many attributes current line have

  • 4th is calculate starting rownumber in resulting table

=SUM(Tabel2[[#Päised];[NumOfAttr]]:[@NumOfAttr])-[@NumOfAttr]+1

this is “classical” running total formula working in table

  • 5th is trick – bitmask showing what attributes exist on particular row

Mask =SUMPRODUCT(

        (OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”))>0)

        *    

        (2^(8-COLUMN(OFFSET($A$1;0;0;1;COUNTIF(Tabel1[#Päised];”Attr*”)))))

)

This SUMPRODUCT calculates bitmask on the way where each bit shows presence of attribute.

One vector is {true,false,…} vector comparing attribs

The second vector is list of 2-powers (in descending order) {256, 128, 64, …}

  • 6th step is to create same bitmask in binary format

Bitmask =DEC2BIN([@Mask];8)&”0″

on case more than 9 attributes – dec2bin have to be broken for 2 parts becouse limitation (? why) of dec2bin

This finalizes intermediate table

 

NExt I create resulting table

 

  • 1st     NR =ROW()-ROW(Tabel3)+1 — this is same as in previous table

it’s question of taste – on some cases I create max size table and unhide unneccessary rows with some IF-s. In that case I simply extend the table with autogenerated rows

  • 2nd     LineNr =MATCH([@Nr];Tabel2[ResLine];1)

this gives me line number in origin table :)

  • 3rd     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

gives me ordinar number of attribute

 

all the above garantees me that line number will never bigger than actual number of original lines and attribute number is never bigger than

number of attributes and start with 1.

 

  • 4th     AtrCol =IF([@ResNr]=1;

            FIND(“1″;INDEX(Tabel2[BitMask];[@LineNr]));

            FIND(“1″;INDEX(Tabel2[BitMask];[@LineNr]);OFFSET([@AttrCol];-1;0)+1)

            )

this finds the position of “1” in my bitmap mask – and actually position of attribute column in table

when attribute number = 1, I simply find “1” in bitmask

for next attributes I give additional “starting point” for find reading this from line above

 

Folllowing is simple

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — this is “old” trick

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — this read attribute name from Header

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — this read attribute value from correct line and column

 

Any comment are welcome

Henn

Posted in Excel | Lisa kommentaar

Vahel võib mõni asi lihtne olla

Äsja seisin silmitsi ühe sageli ettetuleva probleemiga.

Tabelis (Excelis) on koodid ja tooted ja miskid müügiandmed. Toodetel on kood ja nimetus, vaja oleks pivooti kokku võtta. Ainult et – sama koodiga toodet on vahel minetatud ühtemoodi (“Valged Hiired”) vahel teist moodi (“Hiired (valged)”). Kui pivooti panna ridadesse koodid, on kõik valged hiired kokku arvatud, aga kole, kui panna nimetus, on ilus, aga hiired eraldi ridades.

Seekord hästi lihtne valem, mis selle probleemi kõrvaldab. Selleks teed ühe lisaveeru oma tabelisse (näiteks [Ühine Nimetus]) ja kirjutad sinna sellise valemi (eeldame, et tootekood on veerus [Kood] ja nimetus veerus [Nimetus]).

=INDEX([Nimetus];MATCH([@Kood];[Kood];0))

Lihtne ja toimib

Henn

Posted in Excel | Lisa kommentaar

Kuidas arvutada Lihavõtteid

Paari kuu pärast tuleb jälle hakata pühadeks mune värvima. Heal perenaisel on kokku kogutud hulk sibulakoori, aga suur osa inimesi ei mäleta, millal on Lihavõtted e Ülestõusmispüha, millal Suur Reede (siis ju töövaba päev) ja ammugi, et millal Vastlapäev (näe jäigi sel aastal liug laskmata) või Suvistepüha.

Et elu lihtsamaks teha (ja ikka Exceli abiga) õpetan täna, kui imelihtne on arvutada igaks aastaks Lihavõtteid.

Piisab, kui kirjutad kolm “lihtsat” valemit:

[@temp] =MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)

[@tA]=[@temp]+21-IF([@temp]=29;1; IF([@temp]=28;IF(MOD([@Aasta];19)>10;1;0);0))

[@tE] =MOD(20-MOD([@tA]-19;7)-MID(“0135″; MOD(40-LEFT([@Aasta];2);4)+1;1)-MOD(MOD([@Aasta];100)+INT(MOD([@Aasta];100)/4);7);7)+1

Neile kes Excelit uut moodi – tabelina – ei mõista väike seletus

    Esimese valemi (alates =-märgist) paned lahtrisse ja [@Aasta] asemele pistad aastaarvu või viite lahtrile, kus aastaarv

    Teise valemi (alates =-märgist) mõnda teise lahtrisse ja viitad eelmisele ja nii edasi

Neile, kes Excelit tabelina oskavad, lihtsam seletus

    Tee omale tabel, kus veerud Aasta, temp, tA, tE ja Lihavõtted

    Aastad pista esimesse veergu

    Näidatud valemid kirjuta (alates võrdusmärgist) vastavasse tabeliveergu

Lisavõtted saad siis arvutada sellise valemiga

[@Lihavõtted] =DATE([Aasta];3;[@tE]+[@tA])

Ja niisama lihtne see ongi. Soovi korral võid veel veerge lisada

[SuurReede] = [Lihavõtted]-2

[Vastlapäev] = [Lihavõtted]-47

[Nelipüha] = [Lihavõtted] +49

Väike hoiatus ka – see arvutus annab õige tulemuse ainult aastani 4000. Kui see mõnele probleem, siis peab parema lahenduse otsima. Aastal 4000 tuleb niiehknii midagi ette võtta, kuna rooma numbrid saavad siis otsa ja Vatikan ei saa enam oma kalendreid trükkida.

PS. Ma mõtlesin lihavõtteks ka üheainsa valemi kirjutada, aga see on juba keeruline (lihtsamat ma ei oska või ei viitsi teha):

[Lihavõtted] =DATE([@Aasta];3;MOD(20-MOD(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)+21-IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=29;1;IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=28;IF(MOD([@Aasta];19)>10;1;0);0))-19;7)-MID(“0135″; MOD(40-LEFT([@Aasta];2);4)+1;1)-MOD(MOD([@Aasta];100)+INT(MOD([@Aasta];100)/4);7);7)+1+MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)+21-IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=29;1;IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=28;IF(MOD([@Aasta];19)>10;1;0);0)))

Posted in Excel | Lisa kommentaar

Valimistulemus kvootides

Kuulan ajakirjanduslikku analüüsi valimistulemustest ja siin on üks pisike nüanss, mida ajakirjanikud kipuvad unustama ja no ega veebilehed seda viitsi pakkuda ka. Nimelt ei ole korrektne kõrvutada eri ringkondadest korjatud hääli, kuna ringkondade koguhääl on erinev. Õigem oleks võrrelda ringkonnakvoote.

Panin väikese analüüsi aadressile

http://bit.ly/ValmisedKvootides

Siin on lehel “sheet3″ valimiskomisjoni lehelt nopitud andmetega valitute nimekiri järjestatud kvootides (erinevus valimiskomisjoni tabeliga on see, et siia on lisatud ka kompensatsioonimandaadid arvutatuna kvoodiks. Kõrval on ka väike tabel ringkonnakvoodi suurusega (ei pruugi olla täpne, on arvutatud, kuna valimiskomisjoni lehelt kvoodi suurust ei leidnud). Kvoot saadakse kui ringkonnas antud häälte arv jagada ringkonna mandaatide arvuga + 1 (minu mäletamist mööda).

Lehel “sheet4″ on kaks pivooti – ühes erakondade kogutud kvoodid, teises seesama ringkondade kaupa.

Kokku tuli neid “kvoote” pisut üle 57 – see tähendab siis, et niipalju hääli-kvoote kogusid riigikokku (esialgu) pääsenud. Ülejäänud “44 kvooti” siis läks asendusliikmetele ja väljajäänutele.

Et ikka Excel süüdi oleks

Henn

Posted in Excel | Lisa kommentaar

Excel arvutab „valesti“

Üks hää sõber jutustas mulle, et Excel kipub valesti arvutama – SUM ja SUMIF saavad erineva tulemuse.

Mina muidugi ei uskunud ja asusin kontrollima. Avasin Exceli ja tegin uue dokumendi. Veergu A:A lisasin miljon juhuslikku arvu (funktsioon =RAND()). Ning siis hakkasin neid liitma.

  • Ühte lahtrisse    =SUM(A:A)        // liidame kokku kõik arvud veerus A
  • Teise lahtrisse    =SUMIF(A:A;”<0,5″)    // liidame kokku kõik arvud, mis on väiksemad kui pool
  • Kolmandasse     =SUMIF(A:A;”>=0,5″)    // liidame kokku need, mis on suuremad kui pool (kaasa arvatud)
  • Neljandasse teise ja kolmanda summa    // liidame need kaks poolt ja võrdleme esimesega
  • Viiendasse esimese ja neljanda vahe – ja ongi:


Vahe pole suur aga ikkagi. On kohti (VLOOKUP näiteks), mis otsib täpseid asju

Hakkasin siis asja uurima – veebis soovitatakse kahte viisi – Precision as displayed ja ROUND. Paraku ei aita kumbki. Roundi, mis sa roundid, viga jääb alles. PRecision as displayed aga on saatanast ja teeb su kogu töö katki.

Vaatame seda roundimist – ümmardamist

  

5

=SUMPRODUCT((ROUND(A:A;$E$8)))

  

=SUMPRODUCT((ROUND(A:A;$E$8))*(A:A>0,5))

  

=SUMPRODUCT((ROUND(A:A;$E$8))*(A:A<=0,5))

  

=D10+D11

  

=D9-D12

  

 
 

Kirjutasin lahtrisse E8 5 (kohtade arv) ja kasutades müstilismugavat funktsiooni proovisin ümmardamisega 5 kohta peale koma. Viga on ikka alles

  

5

499887,0305

  

374597,1802

  

125289,8504

  

499887,0305

  

4,36557E-09

  

 
 

Muutes kohtade arvu ümmardades, selgus et viga kaob alles, kui ümmardada täisarvuni ( =ROUND(x;0) )

Natuke selgituseks. Excel EI SAAGI täpselt arvutada, kuna Excelis on kõik arvud UJUKOMA arvud. Veebis on rikkalikult näiteid, kuidas arvutamisel tekivad pisivead. Enamasti on need tähtsusetud, aga meie näites näe ei ole. Erandiks on aga arvud, mis on “täisarvud” (Excel tunneb sellised ära) ja nendega arvutades Excel “hoidub vigadest”. Kuidas ta seda teeb, on keerukam teema

Seega arv, millel on 5 kohta peale koma on umbmäärane, arv, mis täisarv aga täpne. Aga kui korrutaks arvu 100 000ga. Siis tal ju ei ole kohti peale koma? Mõeldud tehtud. Proovisin nii – korrutan oma juhuarvud saja tuhandega, ümmardan täisarvuni, liidan seejärel ja jagan tulemuse saja tuhandega. Peaks ju õnnestuma?

Proovisin:

  

100000

=SUMPRODUCT((ROUND(A:A*$E$15;0)))/$E$15

  

=SUMPRODUCT((ROUND(A:A*$E$15;0)*(A:A<0,5)))/$E$15

  

=SUMPRODUCT((ROUND(A:A*$E$15;0)*(A:A>=0,5)))/$E$15

  

=D17+D18

  

=D16-D19

  

 
 

Ja tulemus

  

100000

499887,03051

  

125289,85036

  

374597,18015

  

499887,03051

  

0

  

 
 

Seega – väikeste arvude asemel tegele suurtega ja väikeselt mõtlemise asemel mõtle suurelt

Panin näidise aadressile http://bit.ly/ExcelArvutabValesti – võid ise järele proovida. NB! Muutsin mõned valemid, et nad toimiks ka veebilehel. NB! Näidises tuleb valem =RAND() dubleerida lahtrini A1000000, muidu viga ei paista.

Henn – 9.9.2014

  

Posted in Excel, Funktsioonid, Office | 1 kommentaar

SQL and bit different Partitioned View

Last week I had to play with new old feature – Partitioned View. And I get in trouble. Fortunately only shortly.

For start – some recall – what is Partitioned View. This is technique putting several similar tables to behive as one. Each of tables can have different supporting indexes, they could reside in different (incl readonly) filegroups etc.

The good thing with Partitioned Views is that query against this view containing suitable where-clause will touch only tables needed for producing result. Some conditions allow threat Partitioned View as updatable view – this allow partitioned table looks like “real table” but more flexible and performing.

Short example:

create table T1 (kood int primary key (check kood between 1 and 100, .... )
create table T2 (kood int primary key (check kood between 101 and 200, .... )
create table T3 (kood int primary key (check kood between 201 and 300, .... )
GO
create view TT as
select * from T1
union all
select * from T2
union all
select * from T3
GO

Resulting view TT is actually partitioned view. Query like:

select ... from TT where kood = 107

touch only table T2 and query:

select ... from TT where kood between 170 and 210

touch tables T2 and T3.

Usually the partioning key will be some date period. This allow older rows to be placed into readonly filegroup for example and the queries will perform much better.

This feature I teach and use long time. This presume thar partitipating tables have partition key column and this column have correct and controlled constraint to divide big picture into small, non-overlaping ones.

That mean – I need to have Data Model under my control. But what to do when I haven’t?

I had a following case. Database contain hundred of tables and there is no way to change Data Model. Tables contain data for different departments and years and the name of table point to the department and year. Something like: TabXX2012, TabXX2013, Tabxx2014, TabYY2012, TabYY2013, TabYY2014. For reporting I need some summary view of those tables.

When I create union view like following

create view TabAll
as
select 'XX' as Department, 2012 as Year, * from TabXX2012
union all
select 'XX' as Department, 2013 as Year, * from TabXX2013
union all
select 'XX' as Department, 2014 as Year, * from TabXX2014
union all
select 'YY' as Department, 2012 as Year, * from TabYY2012
union all
select 'YY' as Department, 2013 as Year, * from TabYY2013
union all
select 'YY' as Department, 2014 as Year, * from TabYY2014

and query this view like:


select * from TabAll where Year = 2013

then my query plan shows that query touch all tables. Query optimizer “don’t see”, that Year 2013 contains only in 2 of tables. Now I find solution how to make real “partitioned” view (I use a script to generate that view).

create view TabAll
as
select * from (select 'XX' as Department, 2012 as Year, * from TabXX2012) T where Department = 'XX' and Year = 2012
union all
select * from (select 'XX' as Department, 2013 as Year, * from TabXX2013) T where Department = 'XX' and Year = 2013
union all
select * from (select 'XX' as Department, 2014 as Year, * from TabXX2014) T where Department = 'XX' and Year = 2014
union all
select * from (select 'YY' as Department, 2012 as Year, * from TabYY2012) T where Department = 'YY' and Year = 2012
union all
select * from (select 'YY' as Department, 2013 as Year, * from TabYY2013) T where Department = 'YY' and Year = 2013
union all
select * from (select 'YY' as Department, 2014 as Year, * from TabYY2014) T where Department = 'YY' and Year = 2014

This view works like “classical” partitioned view. Query like

select * from TabAll where Year = 2012

touch only tables TabXX2012 ja TabYY2012. Secret is in where clouse. Query optimizer “see” this as condition and knows how to optimize this execution plan.

I have some other ideas how to use that kind of view – more closer in follow up posts

Posted in Määratlemata, MS SQL Server | Tagged | Lisa kommentaar