Pivoting a Table in SQL (i.e. Cross tabulation / crosstabulation)
This is my first stab at it. Refinement coming once I know more about the contents of the Content table.
First, you need a temporary table:
CREATE TABLE pivot (count integer);
INSERT INTO pivot VALUES (1);
INSERT INTO pivot VALUES (2);
Now we're ready to query.
SELECT campaignid, sourceid, a.contentvalue, b.contentvalue
FROM content a, content b, pivot, source
WHERE source.campaignid = content.campaignid
AND pivot = 1 AND a.contentrowid = 'A'
AND pivot = 2 AND b.contentrowid = 'B'
Rows into Columns Oracle pl sql
You should remove your previous database and build a new database in order to solve this problem
How to do calculations with crosstab/pivot via case in sqlite?
You can always just do the sums again, like so:
SELECT
shop_id,
sum(CASE WHEN product = 'Fiesta' THEN units END) as Fiesta,
sum(CASE WHEN product = 'Focus' THEN units END) as Focus,
sum(CASE WHEN product = 'Puma' THEN units END) as Puma,
sum(CASE WHEN product = 'Fiesta' THEN units END) / sum(CASE WHEN product = 'Focus' THEN units END) as Ratio
FROM sales
GROUP BY shop_id
Or, faster, you can wrap it up in a subquery, like this:
select
shop_id,
Fiesta,
Focus,
Puma,
Fiesta/Focus as Ratio
from
(
SELECT
shop_id,
sum(CASE WHEN product = 'Fiesta' THEN units END) as Fiesta,
sum(CASE WHEN product = 'Focus' THEN units END) as Focus,
sum(CASE WHEN product = 'Puma' THEN units END) as Puma
FROM sales
GROUP BY shop_id
) x
Crosstab/Pivot query in TSQL on nvarchar columns
Use:
SELECT t2.recordid,
MAX(CASE WHEN t1.property = 'Name' THEN t2.value END) AS name,
MAX(CASE WHEN t1.property = 'City' THEN t2.value END) AS city,
MAX(CASE WHEN t1.property = 'Designation' THEN t2.value END) AS designation
FROM TABLE2 t2
JOIN TABLE1 t1 ON t1.id = t2.table1id
GROUP BY t2.recordid
ORDER BY t2.recordid
How I fixed the incompatible error in PostgreSQL while pivoting?
Source of the error msg
One of the columns does not have data type you think it has. Must be operative_margin
, probably text
?
The 1-parameter form of crosstab()
only uses the "category" column (year
in your example) only for sorting. And the "row_name" column (dist_chann_name
- or dist_chann_id
?) would produce a different error msg.
Solution
Either way, unless you can guarantee that every "row_name" has exactly two values to it, it's safer to use the 2-parameter form of corosstab()
:
SELECT *
FROM crosstab(
$$
SELECT dist_chann_name, year, operative_margin
FROM marginality_by_channel
ORDER BY 1, 2
$$
, 'VALUES (2020), (2021)'
) AS ct ("DC" int, "2020" int, "2021" int);
db<>fiddle here
This variant also happens to be more tolerant with type mismatches (as everything is passed as text
anyway). See:
- PostgreSQL Crosstab Query
crosstab()
shines for many resulting value columns (faster, shorter). For just two "value" columns, aggregate FILTER
might be the better (simpler) choice. Not much performance to gain (if any, after adding some overhead). See:
- a_horse's answer under this question
- Conditional SQL count
Broken setup
That aside, your setup is ambiguous to begin with. It includes two rows for the same (dist_chann_name, year) = (3, 2021)
.
- a_horse uses
sum()
in his aggregateFILTER
solution. You might also usemin()
ormax()
, or whatever ... - My solution with the 2-parameter form outputs the last value according to sort order. (Think of it as each next value overwriting it's dedicated spot.)
- The 1-parameter form outputs the first value according to sort order. (Think of it as "first come, first serve". Superfluous rows are discarded.)
A clean solution would use an explicit sort order and document the effect, or work with a query producing distinct values, or use the appropriate aggregate function with the FILTER
solution.
Dynamic alternative to pivot with CASE and GROUP BY
If you have not installed the additional module tablefunc, run this command once per database:
CREATE EXTENSION tablefunc;
Answer to question
A very basic crosstab solution for your case:
SELECT * FROM crosstab(
'SELECT bar, 1 AS cat, feh
FROM tbl_org
ORDER BY bar, feh')
AS ct (bar text, val1 int, val2 int, val3 int); -- more columns?
The special difficulty here is, that there is no category (cat
) in the base table. For the basic 1-parameter form we can just provide a dummy column with a dummy value serving as category. The value is ignored anyway.
This is one of the rare cases where the second parameter for the crosstab()
function is not needed, because all NULL
values only appear in dangling columns to the right by definition of this problem. And the order can be determined by the value.
If we had an actual category column with names determining the order of values in the result, we'd need the 2-parameter form of crosstab()
. Here I synthesize a category column with the help of the window function row_number()
, to base crosstab()
on:
SELECT * FROM crosstab(
$$
SELECT bar, val, feh
FROM (
SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
FROM tbl_org
) x
ORDER BY 1, 2
$$
, $$VALUES ('val1'), ('val2'), ('val3')$$ -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int); -- more columns?
The rest is pretty much run-of-the-mill. Find more explanation and links in these closely related answers.
Basics:
Read this first if you are not familiar with the crosstab()
function!
- PostgreSQL Crosstab Query
Advanced:
- Pivot on Multiple Columns using Tablefunc
- Merge a table and a change log into a view in PostgreSQL
Proper test setup
The test setup that's missing in the question:
CREATE TEMP TABLE tbl_org (id int, feh int, bar text);
INSERT INTO tbl_org (id, feh, bar) VALUES
(1, 10, 'A')
, (2, 20, 'A')
, (3, 3, 'B')
, (4, 4, 'B')
, (5, 5, 'C')
, (6, 6, 'D')
, (7, 7, 'D')
, (8, 8, 'D')
;
Dynamic crosstab?
Not very dynamic, yet, as @Clodoaldo commented. Dynamic return types are hard to achieve with plpgsql. But there are ways around it - with some limitations.
So not to further complicate the rest, I demonstrate with a simpler test case:
CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
('A', 'val1', 10)
, ('A', 'val2', 20)
, ('B', 'val1', 3)
, ('B', 'val2', 4)
, ('C', 'val1', 5)
, ('D', 'val3', 8)
, ('D', 'val1', 6)
, ('D', 'val2', 7)
;
Call:
SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);
Returns:
row_name | val1 | val2 | val3
----------+------+------+------
A | 10 | 20 |
B | 3 | 4 |
C | 5 | |
D | 6 | 7 | 8
Built-in feature of tablefunc
module
The tablefunc module provides a simple infrastructure for generic crosstab()
calls without providing a column definition list. A number of functions written in C (very fast):
crosstabN()
crosstab1()
- crosstab4()
are pre-defined. One minor point: they require and return all text
. So we need to cast our integer
values. But it simplifies the call:
SELECT * FROM crosstab4('SELECT row_name, attrib, val::text -- cast!
FROM tbl ORDER BY 1,2')
Result:
row_name | category_1 | category_2 | category_3 | category_4 |
---|---|---|---|---|
A | 10 | 20 | ||
B | 3 | 4 | ||
C | 5 | |||
D | 6 | 7 | 8 |
Related Topics
Does SQL Server Support Is Distinct from Clause
Sql Server Equivalent of Postgresql Distinct on ()
How to Perform a Cross Join or Cartesian Product in Excel
How to Insert Data Directly from Excel to Oracle Database
How to Multiply a Single Row with a Number from Column in Sql
Difference Between <> and != in Sql
Sql Dynamic Order by Using Alias
How to Insert N Rows of Default Values into a Table
Sql "If Exists..." Dynamic Query
How to Add Months to a Current_Timestamp in Sql
Merge Two Tables/Concatenate Values into Single Column
Postgresql - Conditional Ordering
On Duplicate Key Update Feature in H2
Why Is There a Scan on My Clustered Index
What Is The Query to Get "Related Tags" Like in
Conditional Unique Constraint with Multiple Fields in Oracle Db