SQL Pivot with Multiple Columns

Pivot in Oracle based on multiple columns

You can definitely use multiple columns within a PIVOT clause, here is an example using your table setup:

SELECT *
FROM demo
PIVOT (MAX(VALUE) FOR (identifier_1, identifier_2) IN ((3000, 23) AS A3000_23, (3000, 24) AS A3000_24,
                                                        (3001, 25) AS A3001_25, (3001, 26) AS A3001_26));

N.B.: Excuse the "A" in the column names, you need to start the identifier with a character, not a number.

Here is a DBFiddle showing the results (LINK)

Obviously you can see how this would quickly grow out-of-hand if you need to list large amounts of PIVOT columns.

How to PIVOT multiple columns using SQL Server

You just need to pivot twice and combine the results, e.g.:

-- Setup example data...
drop table if exists #Example;
create table #Example (
  VendorId int,
  Category varchar(10),
  FirstSaleDate date,
  StoreId int
);

insert #Example (VendorId, [Category], FirstSaleDate, StoreId)
values
  (1, 'Car', '2021-01-01', 12),
  (1, 'Clothes', '2021-01-02', 13),
  (1, 'Toys', '2021-01-03', 14),
  (1, 'Food', '2021-01-04', 15),
  (1, 'Others', '2021-01-05', 15);

-- Pivot data...
with FirstSales as (
  select VendorId, Category, FirstSaleDate from #Example
), Stores as (
  select VendorId, 'StoreId_' + Category as Category, StoreId from #Example
)
select
  FirstSales.VendorId,
  Car, StoreId_Car,
  Clothes, StoreId_Clothes,
  Toys, StoreId_Toys,
  Food, StoreId_Food,
  Others, StoreId_Others
from (
  select VendorId, Car, Clothes, Toys, Food, Others
  from FirstSales
  pivot (min(FirstSaleDate) for Category in ([Car], [Clothes], [Toys], [Food], [Others])) as pvt
) as FirstSales
join (
  select VendorId, StoreId_Car, StoreId_Clothes, StoreId_Toys, StoreId_Food, StoreId_Others
  from Stores
  pivot (min(StoreId) for Category in ([StoreId_Car], [StoreId_Clothes], [StoreId_Toys], [StoreId_Food], [StoreId_Others])) as pvt
) as Stores on Stores.VendorId=FirstSales.VendorId;

SQL Pivot multiple columns without forcing aggregate

The problem was that I needed a row_number per every group of column names. So the below worked

SELECT DISTINCT TrnYear,
                TrnMonth,
                EntryDate,
                TrnTime,
                StockCode,
                Warehouse
FROM
  (SELECT  (ROW_NUMBER() OVER (ORDER BY dw_view_change_event_nr) - 1) / 6 + 1 AS rn,
          COLUMN_NAME ,
         column_value
   FROM
     #TheTable AS tmp) AS src PIVOT (MAX(column_value)
                                                          FOR COLUMN_NAME in ([TrnYear], [TrnMonth], [EntryDate], [TrnTime], [StockCode], [Warehouse])) AS piv

Big Query Pivot multiple columns in 2 columns

You can use the UNPIVOT operator for this:

CREATE TEMP TABLE t (
  solution STRING,
  sentiment STRING,
  `groups` ARRAY<STRING>,
  feeling BOOLEAN,
  playing BOOLEAN,
  doing BOOLEAN
);

INSERT INTO t
  (solution, sentiment, `groups`, feeling, playing, doing)
VALUES
  ('I am good', 'positive', ['good', 'am'], true, false, false),
  ('I am playing', 'positive', ['playing', 'am'], false, true, true),
  ('She is running', 'positive', ['running', 'she'], false, true, false),
  ('He is not eating', 'negative', ['eating'], true, false, true);

SELECT *
FROM t UNPIVOT(value FOR name IN (feeling, playing, doing));

returns

solution    sentiment   groups  value   name
He is not eating    negative    [eating]    true    feeling
He is not eating    negative    [eating]    false   playing
He is not eating    negative    [eating]    true    doing
I am good   positive    "[good,am]" true    feeling
I am good   positive    "[good,am]" false   playing
I am good   positive    "[good,am]" false   doing
She is running  positive    "[running,she]" false   feeling
She is running  positive    "[running,she]" true    playing
She is running  positive    "[running,she]" false   doing
I am playing    positive    "[playing,am]"  false   feeling
I am playing    positive    "[playing,am]"  true    playing
I am playing    positive    "[playing,am]"  true    doing

---

Your idea of using UNNEST can also work, you just need to keep both name and value in a single array:

SELECT solution, sentiment, `groups`, name, value
FROM t, 
UNNEST (
    ARRAY<STRUCT<name STRING, value BOOLEAN>>[('feeling', feeling), ('playing', playing), ('doing', doing)]
) ;

How to pivot multiple columns in BigQuery Standard SQL

Consider below approach

select * from (
  select * except(date)
  from your_table
)
pivot (sum(metric1) as metric1, sum(metric2) as metric2 for iso_year in (2021, 2020, 2019))        

if applied to sample data in your question - output is

---

Related Topics