{"id":3556,"date":"2026-10-09T05:33:28","date_gmt":"2026-10-08T21:33:28","guid":{"rendered":"http:\/\/www.simorghict.com\/blog\/?p=3556"},"modified":"2026-10-09T05:33:28","modified_gmt":"2026-10-08T21:33:28","slug":"how-to-use-the-unpivot-step-in-kettle-489d-83a28d","status":"publish","type":"post","link":"http:\/\/www.simorghict.com\/blog\/2026\/10\/09\/how-to-use-the-unpivot-step-in-kettle-489d-83a28d\/","title":{"rendered":"How to use the Unpivot step in Kettle?"},"content":{"rendered":"

Hey there! If you\u2019ve ever spent hours stuck trying to turn those wide, messy Excel exports or SQL query outputs with all those columnar metrics into long, skinny rows that actually make sense for analysis, you\u2019re not alone. I\u2019m part of the Kettle team that\u2019s talked to hundreds of developers, data analysts, and even small business owners who\u2019ve pulled their hair out over this exact problem. Today, let\u2019s cut through the jargon and get real about the Unpivot step in Kettle\u2014because trust me, this little tool is about to become your new go-to for cleaning up wonky data, no fancy SQL or spreadsheets required. Kettle<\/a><\/p>\n

<\/p>\n

First off, let\u2019s level set: if you\u2019ve ever exported data from a legacy system and got something like Month1, Month2, Month3, ProductA_Sales, ProductB_Sales<\/em> all as separate columns, you know what I\u2019m talking about. That\u2019s \u201cwide\u201d data, and it\u2019s basically impossible to run a quick trend on ProductA sales across the year without manually copying and pasting or writing a messy UNION query that\u2019s a nightmare to debug. Unpivot is Kettle\u2019s built-in shortcut to flip that wide data into \u201clong\u201d data\u2014where each metric, its value, and its context get their own row. No more messy copies, no more typos from manual work.<\/p>\n

Before we dive into step-by-step, let\u2019s talk about when this actually matters for real work (not just textbook examples). Last quarter, a retail client of ours was pulling weekly store sales data from their old POS system. The export had columns for Store1_Sales, Store2_Sales, Store3_Sales<\/em>\u2026 up to Store47. They needed to compare foot traffic to sales, but their tool only wanted one \u201cSales\u201d column and a \u201cStore Name\u201d column. Their in-house dev tried writing a UNION query and bailed at Store12 because it was taking 2 hours just to type. We sent \u2019em straight to Unpivot, and they had their clean data in 10 minutes flat. That\u2019s the kind of win we live for here.<\/p>\n

Now, let\u2019s walk through how to actually use Unpivot in Kettle\u2014no fancy prereqs, just open Spoon (that\u2019s Kettle\u2019s UI, by the way\u2014everyone calls it Spoon, no need to say \u201cSpoon GUI\u201d like a robot) and fire up a new transformation. I\u2019ll keep this super concrete, so grab whatever messy wide data you have laying around (I\u2019ll use that retail store sales example to make it real) and follow along.<\/p>\n

Step 1: Add and connect your source. Start with a Text File Input step (or whatever your source is\u2014Excel, SQL table, CSV, whatever). For this example, my source has 5 columns: Date, Store1_Sales, Store2_Sales, Store3_Sales, Foot_Traffic<\/em>. Make sure your source is set to pull those in\u2014no need for any special settings here. Drop that on your canvas, then right-click it and select \u201cUnpivot\u201d from the \u201cTransform\u201d menu. Yeah, it\u2019s right there\u2014we didn\u2019t hide it.<\/p>\n

Step 2: Configure the Unpivot step core settings. When you open the Unpivot window, you\u2019ll see two key sections: \u201cFields to unpivot\u201d and \u201cDestination fields\u201d. Let\u2019s start with the easy part first: Fields to unpivot<\/em>. These are the wide columns you want to flip into rows. In my example, that\u2019s Store1_Sales, Store2_Sales, Store3_Sales. Don\u2019t include Date or Foot_Traffic here\u2014those are columns we want to keep as-is, right? Wait, hold up\u2014some people accidentally include every column here and wonder why Date is turning into a messy row. That\u2019s a common misstep, so just remember: only pick the columns that are your \u201cmetric\u201d columns.<\/p>\n

Now the Destination fields. By default, Kettle names two things: the \u201cField name\u201d (that\u2019s what the old column was called, like Store1_Sales) and the \u201cField value\u201d (the number itself, like $12,450). You can rename these if you want\u2014here, I\u2019d change Field name to \u201cStore_Source\u201d and Field value to \u201cSales_Amount\u201d to make it clearer, but it\u2019s totally up to you. No pressure here\u2014defaults work fine for quick jobs.<\/p>\n

Wait, quick pro tip here: if you have more than a handful of columns to unpivot, you can click the \u201cSelect\u201d button in the Fields to unpivot section and check all the ones you need in one go, instead of clicking each individually. Saves so much time\u2014we built that for people who deal with 20+ columns, like that retail client with 47 stores.<\/p>\n

Step 3: Optional but useful: fix messy names and extra context. A lot of times, those old column names are garbage, like Store1_Sales instead of just Store 1. If you want to clean that up, you can add a Calculator step after Unpivot to split the Store_Source column\u2014take the number part, add a space, and you\u2019ve got \u201cStore 1\u201d. Or, even easier, Kettle has a \u201cReplace in String\u201d step you can drop right after Unpivot to swap underscores for spaces. No need to overcomplicate it, but those little tweaks make your data usable later.<\/p>\n

Step 4: Test it. This is non-negotiable. Hit the \u201cPreview\u201d button in the Unpivot window, and you\u2019ll see exactly what your output looks like. In my example, instead of 5 rows per date (one for store sales, one for foot traffic), I\u2019ll get 3 rows per date: one for each store\u2019s sales, plus the foot traffic is still a single column. If something\u2019s off\u2014like Date is missing, or Store1_Sales didn\u2019t unpivot\u2014go back and check your Fields to unpivot list. That\u2019s almost always the issue.<\/p>\n

Let\u2019s circle back to that retail client for a second. They had a CSV with 50 store sales columns, and they were using a super old version of Kettle (we\u2019d say 7.x, no judgment) where Unpivot was called \u201cDenormalize\u201d and it was way clunkier. We walked them through adding a Text File Input, selecting all 50 sales columns to unpivot, naming the destination fields correctly, and previewing\u2014done. They told us later that their weekly report process went from 3 hours to 15 minutes, and they stopped dreading the spreadsheet work every Monday. That\u2019s the real value of Unpivot\u2014it\u2019s not just a tool, it\u2019s the thing that takes away the boring, error-prone work so you can focus on actually using your data.<\/p>\n

Now, let\u2019s talk about common mistakes we see all the time, because even the seasoned Kettle users slip up here. First: including non-metric columns in Fields to unpivot. We mentioned that earlier, but it bears repeating. If you accidentally add Date, you\u2019ll end up with rows where Field name is \u201cDate\u201d and Field value is \u201c10\/01\/2023\u201d\u2014that\u2019s bad. Always double-check that list. Second: not testing the preview. We can\u2019t tell you how many times someone runs the entire transformation only to realize they missed a column. Preview is free, use it. Third: renaming fields to something useless. If you name the destination fields \u201cColumn1\u201d and \u201cColumn2\u201d, you\u2019ll forget what they mean in 2 days. Spend 10 extra seconds giving them clear names\u2014your future self will thank you.<\/p>\n

Wait, one more thing: can you use Unpivot for other stuff? Yeah, not just sales data. I\u2019ve used it for survey data where every question is a column, for inventory data where each month\u2019s stock is a column, even for HR data where each department\u2019s headcount is a column. Literally any time your data is structured in columns that should be rows, Unpivot is the way to go. It\u2019s way more reliable than manual work and way faster than writing custom SQL every time.<\/p>\n

At the end of the day, data cleaning doesn\u2019t have to be a headache. Unpivot in Kettle is built to cut through that headache, and if you\u2019re dealing with messy, wide data on a regular basis, it\u2019s going to save you so much time and frustration. If you\u2019re using an older version of Kettle and finding Unpivot is glitchy, or you need help configuring it for a super complex dataset, our team is here to help. We work with all kinds of teams\u2014small businesses to enterprise\u2014so whether you\u2019re just starting out with Kettle or you\u2019re a seasoned pro who wants to streamline their workflow, we can help you get set up right.<\/p>\n

<\/p>\n

If you\u2019re ready to stop fighting with messy data and start using Unpivot to make your work easier, reach out to our team to chat about your specific needs. We can walk you through custom setups, help with any kinks, and make sure you\u2019re getting the most out of Kettle\u2019s transform tools. Don\u2019t let messy data slow you down\u2014let\u2019s get your pipeline running smoothly.<\/p>\n

Digital Air Fryer<\/a> References
\nPentaho. (n.d.). Kettle Unpivot Step Documentation. Retrieved from official Pentaho resources.
\nKettle Community. (n.d.). Real-World Use Cases for Unpivot in ETL Workflows. Community-shared ETL guides.<\/p>\n

\n

Ningbo Lanka International Trading Co., Ltd.<\/a>
As one of the most professional kettle manufacturers and suppliers in China, we’re featured by quality products and low price. Please rest assured to wholesale high-grade kettle for sale here from our factory. Also, custom service is available.
Address: No.11 Dongqian Lake Area, Yinxian Avenue, Ningbo, China
E-mail: messi@lanka10.com
WebSite: https:\/\/www.lanka-10.com\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Hey there! If you\u2019ve ever spent hours stuck trying to turn those wide, messy Excel exports … How to use the Unpivot step in Kettle?<\/span>Read more<\/a><\/p>\n","protected":false},"author":949,"featured_media":3556,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[3519],"class_list":["post-3556","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-industry","tag-kettle-4671-8460fb"],"_links":{"self":[{"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/posts\/3556","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/users\/949"}],"replies":[{"embeddable":true,"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/comments?post=3556"}],"version-history":[{"count":0,"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/posts\/3556\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/posts\/3556"}],"wp:attachment":[{"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/media?parent=3556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/categories?post=3556"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.simorghict.com\/blog\/wp-json\/wp\/v2\/tags?post=3556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}