{"id":1025,"date":"2023-06-08T12:36:30","date_gmt":"2023-06-08T12:36:30","guid":{"rendered":"https:\/\/tarkvara.thkit.ee\/veeb\/?page_id=1025"},"modified":"2023-06-08T12:37:33","modified_gmt":"2023-06-08T12:37:33","slug":"pivot-ja-unpivot","status":"publish","type":"page","link":"https:\/\/tarkvara.thkit.ee\/veeb\/pivot-ja-unpivot\/","title":{"rendered":"PIVOT JA UNPIVOT"},"content":{"rendered":"\n<p>Pivot tekitab tavalisest tabelist kahem\u00f5\u00f5tmelise risttabeli. Unpivot teeb t\u00e4pselt vastupidist.<\/p>\n\n\n\n<p>P\u00fc\u00fcame tekitada risttabeli, milles oleks ridades laste s\u00fcnnilinn ning veergudes s\u00fcnniaastad ning andmetena laste keskmised pikkused:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM\n\t( SELECT SynniAasta, SynniLinn, Pikkus \n\t  FROM dbo.Laps_tbl ) AS data\nPIVOT (\n\tAVG (Pikkus)\n\tFOR Synniaasta IN (&#x5B;1995], &#x5B;1996], &#x5B;1997])\n) AS piv\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"398\" height=\"522\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-36.png\" alt=\"\" class=\"wp-image-1029\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-36.png 398w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-36-229x300.png 229w\" sizes=\"auto, (max-width: 398px) 100vw, 398px\" \/><\/figure>\n\n\n\n<p>Vastupidise teisenduse saame korraldada UNPIVOT k\u00e4suga. Selleks salvestan eelnevalt eelmise p\u00e4rinu tulemuse ajutisse tabelisse kasutades SELECT &#8230; INTO #ristt \u2026 konstruktsiooni, ehk siis<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT * INTO #ristt\nFROM\n\t( SELECT SynniAasta, SynniLinn, Pikkus \n\t  FROM dbo.Laps_tbl ) AS data\nPIVOT (\n\tAVG (Pikkus)\n\tFOR Synniaasta IN (&#x5B;1995], &#x5B;1996], &#x5B;1997])\n) AS piv\n<\/pre><\/div>\n\n\n<p>Iseenesest \u00f5nnestus ka varem moodustada analoogseid konstruktsioone, kasutades CASE valikuid SELECT loetelus. PIVOT on muutnud selle protsessi oluliselt lihtsamaks kuid risttabelisse minevad v\u00e4ljanimed (veerud) tuleb endiselt sisse tr\u00fckkida ning neid pole v\u00f5imalik tekitada d\u00fcnaamiliselt.<\/p>\n\n\n\n<p>Lahenduseks on sellele d\u00fcnaamiline SQLi koostamine ning k\u00e4ivitamine EXEC abil.<\/p>\n\n\n\n<p>Selleks tuleb luua skript, mis tekitab komadega eraldatud loetelu k\u00f5igist risttabelisse minevatest v\u00e4ljadest ning kleepida see SQLis \u00f5ige koha peale.<\/p>\n\n\n\n<p>Kuna kasutame SQL 2005 siis teeksin selle tegevuse kasutades CTE abi:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nDECLARE @aastad AS VARCHAR(MAX);\nWITH AastaCTE\nAS \n(\n\tSELECT DISTINCT SynniAasta AS Aasta FROM dbo.Laps_tbl\n)\nSELECT @aastad = ISNULL(@aastad + &#039;,&#x5B;&#039;, &#039;&#x5B;&#039;) + \nCAST(Aasta AS CHAR(4)) + &#039;]&#039;\nFROM AastaCTE\nORDER BY Aasta\nDECLARE @sql NVARCHAR(max)\nSET @sql = N&#039;SELECT * \n\tFROM ( SELECT Pikkus, SynniAasta, SynniLinn \n\t\t FROM dbo.Laps_tbl ) AS Lapsed\n\tPIVOT ( AVG(Pikkus) FOR SynniAasta IN(&#039; + @aastad + N&#039;)) AS Piv&#039;\nEXEC (@sql)\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"555\" height=\"513\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-37.png\" alt=\"\" class=\"wp-image-1034\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-37.png 555w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/06\/image-37-300x277.png 300w\" sizes=\"auto, (max-width: 555px) 100vw, 555px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Pivot tekitab tavalisest tabelist kahem\u00f5\u00f5tmelise risttabeli. Unpivot teeb t\u00e4pselt vastupidist. P\u00fc\u00fcame tekitada risttabeli, milles oleks ridades laste s\u00fcnnilinn ning veergudes s\u00fcnniaastad ning andmetena laste keskmised pikkused: Vastupidise teisenduse saame korraldada UNPIVOT k\u00e4suga. Selleks salvestan eelnevalt eelmise p\u00e4rinu tulemuse ajutisse tabelisse kasutades SELECT &#8230; INTO #ristt \u2026 konstruktsiooni, ehk siis Iseenesest \u00f5nnestus ka varem moodustada analoogseid &hellip; <a href=\"https:\/\/tarkvara.thkit.ee\/veeb\/pivot-ja-unpivot\/\" class=\"more-link\">Loe edasi <span class=\"screen-reader-text\">PIVOT JA UNPIVOT<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1025","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/1025","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/comments?post=1025"}],"version-history":[{"count":7,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/1025\/revisions"}],"predecessor-version":[{"id":1035,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/1025\/revisions\/1035"}],"wp:attachment":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/media?parent=1025"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}