Úvod
V mém posledním článku „Převod Čárkou Oddělené Hodnoty do Řádků a Naopak v serveru SQL Server.“, mluvil jsem o tom, jak si můžete převést oddělené čárkou (nebo oddělené nějaký jiný znak) hodnoty do jednoho sloupce do řádků a naopak. V tomto článku demonstruji, jak můžete převést hodnoty řádků na hodnoty sloupců (PIVOT) a hodnoty sloupců na hodnoty řádků (UNPIVOT) V serveru SQL.,
Převod Řádků na Sloupce – PIVOT
SQL Server má PIVOT relační operátor otočit jedinečných hodnot zadaného sloupce z více řádků do více hodnot sloupců ve výstupu (cross-tab), účinně rotační stůl. Umožňuje také provádět agregace, kdykoli je to nutné, pro hodnoty sloupců, které se očekávají v konečném výstupu. Základní syntaxe pro pivot relační operátor vypadá takto:
se skriptem níže vytvoříme tabulku a načteme do ní některá data., Jak můžete vidět na obrázku níže, má prodejní informace pro některé země po dobu několika let. Také, pokud si všimnete, pro každou zemi a pro každý rok je samostatný řádek.
pomocí OTOČNÉHO operátor, budeme převést řadě hodnoty do sloupec hodnot s skriptu uvedeny níže a výsledky, jak je znázorněno na obrázku níže.
přestože jsme použili funkci agregace součtů, v tomto případě neexistuje součet, protože pro každou jedinečnou kombinaci pro zemi a rok existuje pouze jeden řádek., Vezměte prosím na vědomí použití závorek pro hodnoty otočných sloupců; jsou vyžadovány.
Přináší Dynamiku PIVOT
Pokud si všimnete na výše uvedeném skriptu, jsme poskytli hodnoty (2005, 2006, 2007 a 2008) pro kontingenční sloupce, protože tyto hodnoty jsou k dispozici v původní datové soubory. Ale co když se očekává, že některé další hodnoty přijdou v budoucnu, například v letech 2009 a 2010 atd. pro otočný sloupec?,
v takovém případě můžete stále používat hodnoty sloupců pivot, které se očekávají (nebo které stále nejsou k dispozici v původním datovém souboru) v budoucnu, i když uvidíte NULL pro jeho hodnoty. Skript níže ukazuje tento scénář a obrázek níže ukazuje NULLs pro roky 2009 a 2010, protože pro tyto roky neexistují žádná data.
výše zmíněný přístup funguje dobře, pokud jste již znát všechny možné hodnoty pro pivot sloupec, ale co když ne?,
V tomto případě, můžete napsat dynamické dotazu se nejprve chytit všechny jedinečné hodnoty pro kontingenční sloupce za běhu a pak psát dynamické dotazu, provedení s čepem dotazu, jak je znázorněno níže:
Převedení Sloupců na Řádky – UNPIVOT
UNPIVOT je další relační operátor serveru SQL Server, která provádí téměř reverzní operace PIVOT, otáčením hodnoty sloupců do řádků hodnot. Dovolte mi to ukázat příkladem; umožňuje vytvořit tabulku s otočnými daty z předchozího dotazu se skriptem níže., Obrázek níže ukazuje data nově vytvořené tabulky s otočnými daty.
pro otočení hodnot sloupců do hodnot řádků nyní použijeme operátora UNPIVOT, jak je uvedeno níže. Obrázek níže ukazuje, otáčet údaje:
Pokud jste se vrátit zpět, řekl jsem UNPIVOT je téměř reverzní KONTINGENČNÍ subjektu; to znamená, že to může nebo nemusí být přesně stejné. Důvodem je, že Pivot provádí agregaci při otáčení hodnot řádků do hodnot sloupců a může sloučit možné hodnoty více řádků do hodnoty jednoho sloupce na výstupu., Zvažte například pro danou zemi a rok existují dvě hodnoty, řekněme 5000 a 6000. Nyní, když ji otočíte, bude mít výstup 11000 jako hodnotu sloupce, pokud máte součet jako agregační funkci. Později, pokud ji chcete rozbalit zpět, získáte 11000 ne bi-furcated hodnoty (5000 a 6000 jako originál). Můžeme říci, že pokud jsou otočné hodnoty agregované hodnoty, nebude možné získat původní data zpět.,
závěr
v tomto článku jsem ukázal, jak můžete převést hodnoty řádků na hodnoty sloupců (PIVOT) a hodnoty sloupců na hodnoty řádků (UNPIVOT) v SQL Serveru. Také jsem mluvil o psaní dynamického dotazu, abych napsal dynamický pivotový dotaz, ve kterém jsou možné hodnoty pivot column určeny za běhu.