{"id":845,"date":"2026-03-27T07:23:10","date_gmt":"2026-03-27T07:23:10","guid":{"rendered":"https:\/\/datadandies.nl\/?p=845"},"modified":"2026-03-27T07:23:10","modified_gmt":"2026-03-27T07:23:10","slug":"turn-values-in-different-rows-into-column-headers-with-the-pivot-function-in-snowflake","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2026\/03\/27\/turn-values-in-different-rows-into-column-headers-with-the-pivot-function-in-snowflake\/","title":{"rendered":"Turn values in different rows into column headers with the PIVOT function in Snowflake"},"content":{"rendered":"\n<p>In last week\u2019s post we talked about our pure, unrelenting hatred for PIVOTED data (you know, when months JAN, FEB, MAR, etc. each have their own column).<\/p>\n\n\n\n<p>As I am trying to hit my quota of hypocrite points this week, I\u2019d like to talk about how to turn UNPIVOTED data into PIVOTED data. &nbsp;<\/p>\n\n\n\n<p>A lot of database engines have functions for that, such as the PIVOT-function in Snowflake. However, for the sake of keeping up the fundamentals (and doing things the hard way for no reason at all), I also used CASE WHEN to achieve a PIVOTED dataset.<\/p>\n\n\n\n<p>Both methods are shown in the script below.<\/p>\n\n\n\n<p>Now we have a post about PIVOT but also of UNPIVOT. Balance has been brought to the force.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1.3rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * 1.3rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:16px 0 0 16px;width:100%;text-align:left;background-color:#1e1e1e\"><span style=\"background:#c7c7c7;padding:0.3rem 0.5rem 0.2rem;border-radius:1rem;font-size:0.8em;line-height:1;height:1.25rem;text-align:center;display:inline-flex;align-items:center;justify-content:center;color:#1e1e1e\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE OR REPLACE TEMP TABLE TEMP_TOY_SALES(CATEGORY VARCHAR, SALES INT) AS \n-- Original data; unpivoted.\n    SELECT * FROM VALUES\n        ('cars', 10), \n        ('bikes', 15),\n        ('boats', 5)\n;\n\n-- Pivot using CASE WHEN\nSELECT \n    SUM(CASE WHEN CATEGORY ILIKE 'cars' THEN SALES ELSE 0 END) AS CAR_SALES,\n    SUM(CASE WHEN CATEGORY ILIKE 'bikes' THEN SALES ELSE 0 END) AS BIKE_SALES,\n    SUM(CASE WHEN CATEGORY ILIKE 'boats' THEN SALES ELSE 0 END) AS BOAT_SALES\nFROM TEMP_TOY_SALES\n;\n\n-- Pivot using PIVOT function. Column CATEGORY is being &quot;pivoted away&quot;\nSELECT \n    *\nFROM TEMP_TOY_SALES\n    PIVOT(SUM(SALES) FOR CATEGORY IN ('cars', 'bikes', 'boats'))\n    AS p (cars, bikes, boats)\n;\n\" style=\"color:#D4D4D4;display:none\" aria-label=\"Kopieer\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">OR<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">REPLACE<\/span><span style=\"color: #D4D4D4\"> TEMP <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> TEMP_TOY_SALES(CATEGORY <\/span><span style=\"color: #569CD6\">VARCHAR<\/span><span style=\"color: #D4D4D4\">, SALES <\/span><span style=\"color: #569CD6\">INT<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Original data; unpivoted.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VALUES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        (<\/span><span style=\"color: #CE9178\">&#39;cars&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        (<\/span><span style=\"color: #CE9178\">&#39;bikes&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">15<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        (<\/span><span style=\"color: #CE9178\">&#39;boats&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">5<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Pivot using CASE WHEN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">CASE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> CATEGORY ILIKE <\/span><span style=\"color: #CE9178\">&#39;cars&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> SALES <\/span><span style=\"color: #569CD6\">ELSE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> CAR_SALES,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">CASE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> CATEGORY ILIKE <\/span><span style=\"color: #CE9178\">&#39;bikes&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> SALES <\/span><span style=\"color: #569CD6\">ELSE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> BIKE_SALES,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">CASE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> CATEGORY ILIKE <\/span><span style=\"color: #CE9178\">&#39;boats&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> SALES <\/span><span style=\"color: #569CD6\">ELSE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> BOAT_SALES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> TEMP_TOY_SALES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Pivot using PIVOT function. Column CATEGORY is being &quot;pivoted away&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> TEMP_TOY_SALES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    PIVOT(<\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(SALES) <\/span><span style=\"color: #569CD6\">FOR<\/span><span style=\"color: #D4D4D4\"> CATEGORY <\/span><span style=\"color: #569CD6\">IN<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;cars&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;bikes&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;boats&#39;<\/span><span style=\"color: #D4D4D4\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> p (cars, bikes, boats)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#1E1E1E;color:#c7c7c7;font-size:12px;line-height:1;position:relative\">SQL<\/span><\/div>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"823\" src=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2026\/03\/20260330-pivot-data-in-Snowflake-1024x823.png\" alt=\"\" class=\"wp-image-846\" srcset=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2026\/03\/20260330-pivot-data-in-Snowflake-1024x823.png 1024w, https:\/\/datadandies.nl\/wp-content\/uploads\/2026\/03\/20260330-pivot-data-in-Snowflake-300x241.png 300w, https:\/\/datadandies.nl\/wp-content\/uploads\/2026\/03\/20260330-pivot-data-in-Snowflake-768x617.png 768w, https:\/\/datadandies.nl\/wp-content\/uploads\/2026\/03\/20260330-pivot-data-in-Snowflake-1536x1234.png 1536w, https:\/\/datadandies.nl\/wp-content\/uploads\/2026\/03\/20260330-pivot-data-in-Snowflake-2048x1645.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>In last week\u2019s post we talked about our pure, unrelenting hatred for PIVOTED data (you know, when months JAN, FEB, MAR, etc. each have their own column). As I am trying to hit my quota of hypocrite points this week, I\u2019d like to talk about how to turn UNPIVOTED data into PIVOTED data. &nbsp; A&hellip;<\/p>\n<p class=\"more-link\"><a href=\"https:\/\/datadandies.nl\/index.php\/2026\/03\/27\/turn-values-in-different-rows-into-column-headers-with-the-pivot-function-in-snowflake\/\" class=\"themebutton\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[40,4],"class_list":["post-845","post","type-post","status-publish","format-standard","hentry","category-blog","tag-snowflake","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/845","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=845"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/845\/revisions"}],"predecessor-version":[{"id":847,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/845\/revisions\/847"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=845"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=845"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}