Oracle 11g and above As of Oracle 11g, you can now use the PIVOT operator to achieve that result: create table tq84_pivot ( month number, value number ); insert into tq84_pivot values(1, 100); insert into tq84_pivot values(2, 200); insert into tq84_pivot values(3, 300); insert into tq84_pivot values(4, 400); insert into tq84_pivot values(5, 500); insert into … Read more
Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once. SELECT Action, MAX( CASE data WHEN ‘View’ THEN data ELSE ” END ) ViewCol, MAX( CASE data WHEN ‘Edit’ THEN data ELSE ” END ) EditCol FROM t GROUP BY … Read more
As mentioned by David Anderson Spark provides pivot function since version 1.6. General syntax looks as follows: df .groupBy(grouping_columns) .pivot(pivot_column, [values]) .agg(aggregate_expressions) Usage examples using nycflights13 and csv format: Python: from pyspark.sql.functions import avg flights = (sqlContext .read .format(“csv”) .options(inferSchema=”true”, header=”true”) .load(“flights.csv”) .na.drop()) flights.registerTempTable(“flights”) sqlContext.cacheTable(“flights”) gexprs = (“origin”, “dest”, “carrier”) aggexpr = avg(“arr_delay”) flights.count() ## … Read more
The only way in MySQL to do this dynamically is with Prepared statements. Here is a good article about them: Dynamic pivot tables (transform rows to columns) Your code would look like this: SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘MAX(IF(pa.fieldname=””‘, fieldname, ”’, pa.fieldvalue, NULL)) AS ‘, fieldname ) ) INTO @sql FROM product_additional; SET … Read more
There are several ways that you can transform this data. In your original post, you stated that PIVOT seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server. However, if you do not have access to those functions this can be replicated … Read more
You cannot put a dynamic statement in the PIVOT’s IN statement without using PIVOT XML, which outputs some less than desirable output. However, you can create an IN string and input it into your statement. First, here is my sample table; myNumber myValue myLetter ———- ———- ——– 1 2 A 1 4 B 2 6 … Read more
Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that: CREATE EXTENSION IF NOT EXISTS tablefunc; Improved test case CREATE TABLE tbl ( section text , status text , ct integer — “count” is a reserved word in standard SQL ); INSERT … Read more
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns. It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially. First up, here are … Read more
Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement: select pt.partner_name, count(case when pd.product_name=”Product A” THEN 1 END) ProductA, count(case when pd.product_name=”Product B” THEN 1 END) ProductB, count(case when pd.product_name=”Product C” THEN 1 END) … Read more
There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, … Read more