Unpivot in oracle. ORA-00918: column ambiguously defined when using pivot. . Sep 28, 2018 · You want "100" as 100 not 100 as '100':. Basically, pivot operator allow you to transpose rows into columns. . The (KEY1, KEY2) = (11111, A) is present for February 2015, and Purpose. Ask Question Asked 10 months ago. You have to concatenate the original grouping field to make it distinct. Internally Oracle Database converts it to a "classic" pivot. So for the medal SQL Server supports the pivot and unpivot clauses since version 2005 (documentation). Just for fun, I thought I’d build my own equivalents, and it turns out that SQL Server is actually better at optimizing those than the original PIVOT and UNPIVOT, which is a positive surprise to me. At this point, cannot use full featured FLASHBACK ARCHIVE with CONTEXT but it would have the same issues I thinkENVIRONMENT</b>I have a change logging table (e. Identifier Column: The [Identifier_column] is a column that uniquely identifies each row. Just be careful with dynamic columns names. Asked: July 27, 2020 - 6:21 pm UTC. Nov 19, 2015 · Please suppose you have this Oracle table: I would like to write an Oracle query that, without using PIVOT / UNPIVOT, produces the following output: In the source table, you can see that I have two main blocks of data: date related to February 2015, and date related to March 2015. 7. Nov 25, 2017 · 0. In this case, you can make it a NUMBER column, and you can assign values 0, 1, 2, to columns M0, M1, M2, - so you can use that directly to adjust the start dates. I have a temp table TEMP. Last updated: July 28, 2020 - 1:55 pm UTC. Jan 27, 2022 · I am using the follwing script using UNPIVOT: SELECT TEST_NUMBER, ParamID, ParamValue FROM (SELECT TEST_NUMBER, ANALYSIS, STARTER, DATE_STARTED, SAMPLE FROM T WHERE T. FROM dual. Query for and select the unpivot column from the target table columns. Aug 13, 2017 · Connor and Chris don't just spend all day on AskTOM. So we need to transformthe data into the migration Dec 27, 2019 · In this article you will learn how to use PIVOT and UNPIVOT operators in Oracle 11g. You need to specify which columns you are UNPIVOT ing to rows; you cannot use any unless you have defined a column called any. Jul 23, 2015 · A quick example of using the PIVOT and UNPIVOT operators introduced in Oracle Database 11g. Open a query document to select it as a data source for a pivot table. The PIVOT clause simplifies the process of rotating data in SQL queries. Built with using Oracle APEX(opens in new window) Unpivot columns and values Hi,I'm working with migrating data into a system using a migration tool supplied by the vendor of the target system. Click the Map Column icon. One such feature is the UNPIVOT operator, a powerful tool that can transform your data from a column-based format into a row-based format. I can use union all select order,code1,value1 from table union all s How Pivot Table works in ORACLE? Explain with Different Examples? How will you convert UNPIVOT Data into PIVOT Data? Jan 29, 2016 · I personally really dislike the PIVOT and UNPIVOT constructs, because their syntaxes are unintuitive and hard to remember. If part or all of the result of a SELECT statement is equivalent to an existing materialized view, then Oracle Database may use the materialized view in place of one or Feb 4, 2016 · The PIVOT/UNPIVOT statements have been added in Oracle 11g, so it won't work with previous versions. Let me demonstrate this with an example; lets create a table with pivoted data from the previous query with the script below. ReportingDate. value. A typical scenario: Customer data is validated against a set of audit checks and the validation results are stored against the Customer Id as Y/N flags. When you unpivot, the column that keeps track of unpivoted columns can be of any data type. Jan 30, 2011 · 1. walk you through an example of how to use the new Pivot Component. UNPIVOT is another relational operator in SQL Server that performs almost the reverse operation of PIVOT, by rotating column values into rows values. An unpivot converts a source table with a short, fat structure of many columns and few rows into a target table that represents the same data in a tall, skinny structure with more rows and fewer columns, based on the value in an unpivot column, which must be associated with a codelist. I have followed the simple steps below to get to the result. for col in (price, quantity) ) u. Unpivot Component in the Component palette and then clicking on the Mapping. Nov 9, 2017 · 0. Consider a table called customer which has below data stored inside. com. id = 2 THEN data2. Columns data into rows in oracle. Feb 17, 2012 · 1. Now my question is can we pass column names dynamically to the UNPIVOT so that when the columns in the table increases we may not have to alter the statement. I’m trying to get back into the swing of writing again after my 2 month layoff. Oct 12, 2018 · SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. The UNPIVOT clause is new for Oracle Database 11g and enables you to flip the columns into rows in the output from a query, and, at the same time,allow you to run an aggregation function on the data. Then take a series of quizzes on these clauses from Kim Berg Hansen, the Dev Gym's very own SQL quizmaster. SELECT * FROM ( SELECT * FROM Temperatures UNPIVOT( avg_temp FOR MONTH_NUM IN (Jan, Feb, Mar, Apr, May, Jun)) ); Here is the Jan 25, 2016 · Pivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. sql-server. This column remains unchanged during the unpivoting process. First, let's look at an example in Oracle. Analytic View Objects. You need to supply three things: The name of a new column that will store the values from the old columns; Another new column showing the source of these values; The list of columns that hold the values you want to become rows. It’s a little daunting, but quite fun as well. It seems that the problem was a missing set of paranthesis around the (2) arguments to the FOR clause and a corresponding set around Feb 9, 2015 · For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle. In Oracle PL/SQL, the PIVOT operation is used to transpose rows into columns, transforming the result of a SELECT statement into a more readable and compact format. Nov 21, 2017 · My query's unpivot query performance is very low, it runs very slowly and takes around 11s to execute, while the base query that it's based on takes around 60ms to execute. Learn how to use the PIVOT and UNPIVOT operators to transform data in Oracle Database 11g. 1. For example: count ( case when medal = 'Gold' then 1 end ) So the following pivot: Oct 2, 2014 · with t1 as (select * from vw_summary unpivot (plantvalue for plant in (au11, au12, bd10))), t2 as (select uacheck, uadesc, plant, plantvalue, row_number over Aug 5, 2022 · how do I use dynamic pl sql to unpivot several different columns into several different rows? With static sql it works well, but with dynamic sql i keep getting errors. There’s no straightforward method for dynamic pivoting in Oracle’s SQL, unless it returns XML type results. The base query is complex, with several joins, but it returns only a few rows. My thinking is that somehow I need to create a pseudo group name for the columns, and I want to avoid using a union clause to get the data separately Thanks Apr 24, 2017 · If you want to show rows with null as a column, map the null to a value first and add this to the IN list. Second, specify a list of comma-separated column names within parentheses. com UnPivot in oracle 12c give wrong results 1061562 Feb 9 2015 — edited Feb 9 2015 Sep 28, 2015 · PIVOT và UNPIVOT (Phần 1) 13:09 Posted by NTP Analysis , Database No comments. Problem I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction. We will record the department year and sales amount for each quarter. Jun 2, 2023 · Learn how to use the Oracle PIVOT and UNPIVOT keywords to convert data from rows into columns or vice versa. Feb 28, 2014 · Pivoting Data in ODI 12c. 0. Oracle has an UNPIVOT directive, which transposes columns into rows. You can also catch regular content via Connor's blog and Chris's blog. Aug 11, 2017 · 1. Converting column to rows in Oracle 11g. Version: 12. 2. Julien TESTUT. Apr 22, 2017 · EDIT -- pivot xml option (also Oracle 11g) Unpivot columns into rows (oracle) 0. The trick is to provide a comma-separated list of the columns you want to appear in your results before FOR. In simple terms, we can say that SQL Pivot operator converts the unique values from one column values which are in rows into different columns. from my_table. please try the below query for that select 1001 device_id, 4 quantity from dual union all. Oracle 11g R2 Schema Setup:. Feb 9, 2015 · For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle. Instructor. SELECT DISTINCT ReportingDate, PortfolioID,ISIN, PortfolioNme, Section,REPLACE(REPLACE(Risks,'‘',''''),'’','''') FROM. I have the following UNPIVOT code and I would like to order it by the FactSheetSummary columns so that when it is converted to rows it is order 1 - 12: INSERT INTO #Results. Sep 4, 2019 · or use a query with unpivot keyword by considering the common expression for the column ( namely col in this case ) must have same datatype as corresponding expression : select result_col from ( select col1, to_char(col2) as col2, col3, col4, to_char(col5) as col5, to_char(col6) as col6 from temprow ) unpivot (result_col for col in (col1,col2 Oct 31, 2019 · This example is in Transact-SQL. SQL Fiddle. Since you are using Oracle 11g, you can use the unpivot function: select year, value. Read an article explaining the PIVOT and UNPVIOT operators from Tim Hall. how to set dynamic header using BIRT? See more linked questions. CREATE OR REPLACE FUNCTION Get_Month_Values RETURN SYS_REFCURSOR IS. UNPIVOT in Oracle. See the IN list of the UNPIVOT clause. CHANGESTABLE) that is basically the mirror image column-wise of an actual base table (e. PIVOT and UNPIVOT Operators in UNPIVOT. i. add an Unpivot component into the Mapping. Ask Question Asked 7 years, 5 months ago. id = 1 THEN data1. 0. Find out how to turn rows into columns and back again. 78 AS Jan 27, 2024 · The UNPIVOT operation is applied to subquery that selects the columns you want to unpivot. May 26, 2022 · In this article learn how to use PIVOT and UNPIVOT in SQL Server to transform your data output along with examples. They are displayed as boxes with columns. Viewed 775 times -1 I'm not at all familiar with PIVOT Jan 22, 2019 · UNPIVOT (<value_column> FOR <name_column> IN (<column_list>)) however, i can do unpivot for one column but dont know how to unpivot for multiple columns so that i can have columns such as code pyr plan group1 group2 ins instead of multiples code columns etc. TEMP has following structure:-USER COUNT TYPE ---- ----- ---- 1 10 T1 2 21 T2 3 45 T1 1 7 T1 2 1 T3 I need a query which will show all types has column names,and types can have any value like T1, T2,. This allows you to quickly and easily analyze your data and gain valuable insights. To insert a new row into a table, you use the Oracle INSERT statement as follows: VALUES ( value_list); Code language: SQL (Structured Query Language) (sql) In this statement: First, specify the name of the table into which you want to insert. com UnPivot in oracle 12c give wrong results 1061562 Feb 9 2015 — edited Feb 9 2015 Jul 27, 2020 · Thanks for the question, Migs. However, the release of 11g includes explicit pivot Feb 12, 2017 · You can deal with it by using a different join syntax - instead of on j. In a query, it is specified in the FROM clause after the table name or subquery. Analytic views are described in the following topics. FROM employees e. Aug 30, 2020 · The following syntax summarizes how to use Oracle SQL UNPIVOT operator. ----- Jun 1, 2015 · Converting Columns to Rows – UNPIVOT. Place a pivot clause containing these items after the table name, like so: Copy code snippet. In this blog post we will. We have recently added several new Mapping Components in. You can do this using a cross join and some case statements by using a dummy subquery that holds the same number of rows as you have columns that you want to unpivot (since you want each column to go into its own row) like so: WITH your_table AS (SELECT 'Red' Team, 'Adam' usr, 4 Apples, 5 Oranges, 6 Pears FROM dual UNION ALL. Oracle Data Integrator 12c such as Pivot or Unpivot. select 'a' name, 1 id, 'eng' lang from dual union all. sql-server-2008-r2. In conclusion, pivot tables are a powerful tool for organizing and summarizing large sets of data in Oracle Database. But perhaps you could start from P_COLLECTED and join C_CORRECTIONS to it. Dynamic Pivot for Oracle 11g+. Aug 28, 2014 · Re-arrange columns after Unpivot ORACLE. id = f. UNPIVOT To get the result like above i am using unpivot as follows: select * from ( select Math, MathStartDate, Science, ScienceStartDate, Comp, CompStartDate, Hist I am using oracle 10g. It must be associated with a codelist. Modified 7 years, 5 months ago. htmlHow to use unpivot in SQL?This video talks about usage of UNPIVOT in sql. We will base our example on a table called orders with the following definition:. select 1002 device_id, 20 quantity from dual union all. As I am not a professional Oracle developer, these are simple steps I've done: 1) Download the zip package to find pivotFun. it is looking very simple after referring your previous post. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. sql to create a new function 3) Use the function in normal SQL. Feb 1, 2017 · Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. Dec 8, 2016 · Most efficient way to UNPIVOT a table with large # of columns Database is 11. This is particularly useful when you want to pivot data based on a specific column’s values. So this is medal. Jul 22, 2022 · Does this answer your question? Oracle 11g: Unpivot multiple columns and include column name Apr 25, 2024 · I have such table with 5 columns order code1 value1 code2 value2 I want to to have one column for code and one column for value. Column to Unpivot: The columns, [column1], [column2], are the columns you want to unpivot. g. How to UNPIVOT into multiple May 29, 2016 · 0. Jul 4, 2023 · unpivot and pivot in oracle. CONNECT BY LEVEL <= 4 -- the number of columns to unpivot. Tn and result will be like:- Sep 5, 2014 · The unpivot is the process of converting multiple rows into multiple columns. , PortfolioID. In dbForge Studio for Oracle, pivot tables are created from the built-in Query Builder. In Database Explorer, select required tables from your database and drag-and-drop them to the query document. Next. It has several columns. sql in there. To get zeroes in your ouput, use standard nvl/coalesce in the select: with rws as (. You didn't specify what version of SQL Server you are using but there are several different ways to get the When you have multiple sets of columns you want to unpivot, chaining unpivot operations multiplies rows in your results. In SQL Oracle, Pivot and Unpivot are relational operators that are used to transform one SQL table into another SQL table in order to achieve a simpler view of the data. Converting columns to rows in oracle 11gR1. Even if your client is 11g, what really matters is the server version; if your server is 10g, you have no support for unpivot. So, I think I was doing exactly what you suggested with your unpivot code, but you indirectly answered my question by providing the correct syntax. The column name aliasing is much more manual in MSFT SQL; The code below doesn't have aliasing. Unpivot operates on ~7 group-by columns and unpivots around 40 columns. What am I doing wrong? 1. 1) pid_prep - Get the Year, PID and the Counts 2) vid_prep - Get the Year, VID and the Counts 3) pid_pivoted - Pivot the counts for PID 4) vid_pivoted - Pivot the counts for VID 5) main SELECT - Join pid_pivoted and vid_pivoted to get the result. Jul 10, 2023 · In this article. Aug 22, 2014 · Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows. PIVOT rotates a table-valued expression by turning the unique values from one column in the May 22, 2018 · https://easy-learning-tech. unpivot xmltable Hi,I want to parse xml like:<xml> <field1>value1</field1> <field2>value2</field2> <field3>value3</field3></xml></i></b>I write query PIVOT および UNPIVOTの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』および「PIVOTおよびUNPIVOTの使用例:」を参照してください。 unpivot_clause INCLUDE | EXCLUDE NULLS 句を使用すると、NULL値の行を含めるか除外するかを選択できます。 To create an Unpivot transformation: Select the source and target tables and click the Map icon, then select a Transformation Type of Unpivot. Jun 16, 2020 · CSA_P3 as ('CSA', 'P3'), ESA_P3 as ('ESA', 'P3') ) ) dbfiddle. Here there's something on unpivot in 10g and earlier versions. To create a pivot table, you can use the PIVOT and UNPIVOT operators in combination with other SQL statements. Viewed 1000+ times Jun 27, 2012 · I have a table like this: Id Name parentId ----- 5 Rollers 2 2 Paint and Brushes 1 1 Decorating NULL Using PIVOT or any other Apr 19, 2017 · Pivot is just syntactic sugar really. Consider the standard EMP table in the SCOTT schema. You have to do mutiple PIVOT calls. SQL Merge rows instead of UNION ALL. This is done by clicking on the. See examples, syntax, and pre-11g alternatives for these operators. CROSS JOIN (SELECT LEVEL ID. Unpivot transformations are used for tables where multiple May 29, 2022 · In Oracle or SQL Server this could be achieved with the UNPIVOT operator, but that is not available in Postgres. We will create a table with some simple sales data. You can use the following SQL statements to recreate this. For more information about Oracle (NYSE:ORCL), visit oracle. Overview of Analytic Views. Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, materialized views, analytic views, or hierarchies. com/articles/11g/piv Sep 8, 2016 · For example, count, sum, min, etc. CREATE TABLE PIVOTED_SALES ( sales_per_month NUMBER, "100" NUMBER, "101" NUMBER, "105" NUMBER, "106" NUMBER, "200" NUMBER ); INSERT INTO pivoted_sales VALUES( 1, 0, 20, 30, 0, 50 ); Jan 25, 2017 · 3. UNPIVOT is a relational operator that accepts two columns (from a table or subquery), along with a list of columns, and generates a row for each column specified in the list. ERROR ORA-00904: in simple UNPIVOT query. Static sql works fine : SELE Nov 10, 2015 · 1. So for the medal With analytic views you can easily create complex analytic queries on large amounts of hierarchical and dimensional data in database tables and views. id = 3 THEN data3. -- setting up a table variable with your starting data declare @t table ( Name varchar(10) primary key ,[Age 18-24] int ,[Age 25-34] int ,[Age 35-44] int ); insert @t (Name, [Age 18-24], [Age 25-34], [Age 35-44]) values ('John', 1, 0, 0 ) ,('Maria', 0, 0, 1 Dec 27, 2023 · Introduction In the world of data management, Oracle SQL provides a wide array of functions and features to manipulate and transform data. Sep 8, 2016 · It's time for the unpivot! Split columns to rows (UNPIVOT) Unpivot works in a similar way to pivot. Check the documentation for this join syntax if you are not familiar with it. With PIVOT Clause. However, the ambiguity of the test_1 column name can't be addressed this way, and as you saw, you can't use table qualifiers with unpivot. Dec 5, 2020 · Solution. datastore from the Models into the Mapping. 5. (. May 16, 2021 · Oracle unpivot grouping. Third, specify a list of PIVOT and UNPIVOT Operators. select * from table. Click the Unpivot icon. blogspot. (SELECT DISTINCT. How to change columns to rows. (select UserId, HomePhone as [3000] OfficePhone as [3001] CellPhone as [3002] Fax as [3003] Website as [3005] Oct 22, 2018 · Hi all, I have a table saving values for every month in a year (columns like year_month) as it is displayed below WITH TABLE_2017 AS (SELECT 2 AS ID1,3 AS ID2,34. id = 4 THEN data4. Hot Network Questions File extension difference of 'charge-density Jan 19, 2023 · Oracle Pivot Table Summary. When you have multiple sets of columns you want to unpivot, chaining unpivot operations multiplies rows in your results. pivot ( 3 for 1 in (2, 2, 2) ); So to create the final medal table from the raw data, you need to plug in: You want the medals to become columns. select 'b' name, 2 id, 'eng' lang from dual union all. Aug 20, 2013 · 2013-08-25 Sunday. May 29, 2014 · Microsoft SQL is not nearly as simple and flexible when needing to pivot multiple columns. You unpivot C_CORRECTIONS and then join P_COLLECTED to it. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. Click OK. Test example: declare @P_COLLECTED table (Amount int, Type varchar(8), Account varchar(8), REFNUM varchar(8)); In this Module you will learn to:• Identify the benefits of pivoting and unpivoting operations• Write cross-tabulation queries to pivot (rotate) columnvalues into new columns and to unpivot (rotate) columnsinto column values• Pivot and unpivot with multiple columns and multipleaggregates• Use wildcards and aliases with pivoting operations. Dec 5, 2016 · PIVOT & UNPIVOT in Oracle SQL. model clause (Oracle) The proprietary model clause, which was introduced with Oracle Version 10g, can also be used to solve the pivot problem (documentation). May 25, 2012 · If you need to store result of UNPIVOT you could use INSERT ALL:. February 28, 2014 | 5 minute read. select 1001 id, 'Yes' wifi, 'No' email, 'No' bluetooth from dual union all. Nov 12, 2019 · This can be done with the UNPIVOT operator, or "the old way" (how unpivoting was done before Oracle introduced the UNPIVOT operator) shown below. Rotates a table by transforming columns into rows. You do not need to UNPIVOT, just PIVOT with multiple columns in the pivot: SELECT * FROM ( SELECT Sep 15, 2014 · Issue with unpivot and oracle objects. CREATE TABLE orders ( order_id integer NOT NULL, customer_ref varchar2(50) NOT NULL, order_date date, product_id integer, quantity integer, CONSTRAINT orders_pk PRIMARY KEY (order_id) ); Jan 11, 2017 · Easily transpose columns as rows in Oracle 11G+, with the UNPIVOT clause in SQL. select 1003 device_id, 1 quantity from dual. diagram. in/2018/05/unpivot-in-sql. See examples, syntax, tips and tricks for pivoting and unpivoting data in Oracle SQL. WHEN d. For more information see:https://oracle-base. Aug 29, 2017 · CASE WHEN d. add the Source table UNPIVOT_TEST into your Mapping, to do so drag and drop the. 2. with. The tool uses a predefined schema and we are getting the data in non normalized csv format. SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. BASETABLE) Mar 31, 2020 · The UNPIVOT syntax is: UNPIVOT ( value FOR key IN ( column1 AS 'alias1', column2 AS 'alias2' ) ) You cannot have a aggregation expression as the value. Hot Network Questions Example. END DATA. For the non-XML results PL/SQL might be used through creating functions of SYS_REFCURSOR return type. PostgreSQL doesn't support this syntax or functionality, but we can easily get the same result. The Oracle (11g+) syntax is similar but with a few more options, such as how to treat null values. 2) Run once the pivotFun. unpivot. Attribute Dimension and Hierarchy Objects. CREATE TABLE T5_unpiv(IDNUM NUMBER,SK NUMBER,F NUMBER,E NUMBER,H NUMBER ,F_COL_NAME VARCHAR2(100 Dec 16, 2016 · oracle unpivot multiple columns into multiple columns. for each row value you're pivoting, you have a column which returns null if the row doesn't have that value. Since P_COLLECTED already has the multiple rows. Transform column to row data in Oracle. SAMPLE IN (SELECT ID FROM S)) OriginalT UNPIVOT (ParamValue FOR ParamID IN (ANALYSIS, STARTER, DATE_STARTED, SAMPLE) ) ; But I am getting the following error: Nov 21, 2008 · PIVOT and UNPIVOT in 11g…. You can wrap the aliases in another select statement so the aliases are recognized, and unpivot it from there: select * from. SELECT 'Red ORA-00904: "CEILING": invalid identifier in ORACLE 11G Hot Network Questions What is a Christian response to the claim that atheists make that "the Bible condones slavery" in Colossians 3:22-25? Oct 19, 2020 · how can I unpivot so that I get the following: Description sum count col1 _name sum_col1 count_col1 col2_name sum_col2 count_col2 col3_name sum_col3 count_col3. There’s a bunch of 11g new features that didn’t make it into the OCP exam, and the PIVOT and UNPIVOT operators fall slap bang into that category. Related. Pivoting is a common technique, especially for reporting, and it has been possible to generate pivoted resultsets with SQL for many years and Oracle versions. 4. Mar 7, 2014 · in a Mapping: First. Trong quá trình lấy số liệu, thiết lập bảng biểu và báo cáo, một trường hợp thường gặp là bạn phải hiển thị dữ liệu từ dạng dòng chuyển thành cột - kiểu truy vấn như vậy gọi là Pivot Query và Apr 21, 2023 · My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. Let's look at how to use the PIVOT clause in Oracle. Suppose we want to show the null count in columns EMPNO, ENAME, MGR and COMM, in that order. They are also available in the Oracle Database since version 11g (documentation). e. However Postgres’ ability to use the VALUES clause like a table makes this actually quite easy: The conversion of columns into rows is called an UNPIVOT. id write using(id). And of course, keep up to date with AskTOM via the official twitter account. ajpenjfvtolijlneqywd