Transposing an sql result so that one column goes onto multiple columns
Consider the following demo:
CREATE TEMP TABLE qa (id int, usr int, question_id int, answer_id int);
INSERT INTO qa VALUES
(1,1,1,1)
,(2,1,2,9)
,(3,1,3,15)
,(4,2,1,2)
,(5,2,2,12)
,(6,2,3,20);
SELECT *
FROM crosstab('
SELECT usr::text
,question_id
,answer_id
FROM qa
ORDER BY 1,2')
AS ct (
usr text
,q1 int
,q2 int
,q3 int);
Result:
usr | q1 | q2 | q3
-----+----+----+----
1 | 1 | 9 | 15
2 | 2 | 12 | 20
(2 rows)
user
is a reserved word. Don't use it as column name! I renamed it to usr
.
You need to install the additional module tablefunc which provides the function crosstab()
. Note that this operation is strictly per database.
In PostgreSQL 9.1 you can simply:
CREATE EXTENSION tablefunc;
For older version you would execute a shell-script supplied in your contrib
directory. In Debian, for PostgreSQL 8.4, that would be:
psql mydb -f /usr/share/postgresql/8.4/contrib/tablefunc.sql
Transpose group of rows into multiple columns
We can try doing a pivot query with the help of ROW_NUMBER
:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY CatCode, CatDesc) rn
FROM CntTyp
)
SELECT
ContactID,
MAX(CASE WHEN rn = 1 THEN CatCode END) AS CatCode1,
MAX(CASE WHEN rn = 1 THEN CatDesc END) AS CatDesc1,
MAX(CASE WHEN rn = 2 THEN CatCode END) AS CatCode2,
MAX(CASE WHEN rn = 2 THEN CatDesc END) AS CatDesc2,
MAX(CASE WHEN rn = 3 THEN CatCode END) AS CatCode3,
MAX(CASE WHEN rn = 3 THEN CatDesc END) AS CatDesc3
FROM cte
GROUP BY
ContactID;
Simple way to transpose columns and rows in SQL?
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 using UNION ALL
to UNPIVOT
and then an aggregate function with a CASE
statement to PIVOT
:
Create Table:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Union All, Aggregate and CASE Version:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
See SQL Fiddle with Demo
The UNION ALL
performs the UNPIVOT
of the data by transforming the columns Paul, John, Tim, Eric
into separate rows. Then you apply the aggregate function sum()
with the case
statement to get the new columns for each color
.
Unpivot and Pivot Static Version:
Both the UNPIVOT
and PIVOT
functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
See SQL Fiddle with Demo
The inner query with the UNPIVOT
performs the same function as the UNION ALL
. It takes the list of columns and turns it into rows, the PIVOT
then performs the final transformation into columns.
Dynamic Pivot Version:
If you have an unknown number of columns (Paul, John, Tim, Eric
in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT
and then PIVOT
:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
See SQL Fiddle with Demo
The dynamic version queries both yourtable
and then the sys.columns
table to generate the list of items to UNPIVOT
and PIVOT
. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors
and/or names
this will generate the list at run-time.
All three queries will produce the same result:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
SQL Transpose Multiple Columns to Rows
select Division, Period, 'Total Cost' as Description, TotalCost as Value
from MyTable
union
select Division, Period, 'Discount' as Description, Discount as Value
from MyTable
union
select Division, Period, 'Net Sales' as Description, NetSales as Value
from MyTable
union
select Division, Period, 'Net Returns' as Description, NetReturns as Value
from MyTable
union
select Division, Period, 'Gross Sales' as Description, GrossSales as Value
from MyTable
union
select Division, Period, 'Gross Returns' as Description, GrossReturns as Value
from MyTable
You can also wrap this in a view, then do a select on the view with whatever ordering or filtering that you need
Oracle SQL Transposing Only One column values as separate columns in the output
Try PIVOT
SELECT * FROM (
SELECT
created_date,
response_status,
nvl(count,0) as count
FROM
t_bvs_txn_response_summary
WHERE
created_date > SYSDATE - 1 / 24
AND response_status IN (
'successful',
'Disconnection'
)
)
PIVOT
(
SUM(count)
FOR response_status IN ('successful', 'Disconnection')
)
ORDER BY
1;
Related Topics
Ssis Source Format Implicit Conversion for Datetime
Does Oracle Store Trailing Zeroes for Number Data Type
Postgres - Function to Return the Intersection of 2 Arrays
How to Set Ignore_Dup_Key on for a Primary Key
Ora-00907: Missing Right Parenthesis
How to See the Values of a Table Variable at Debug Time in T-Sql
How to Import .SQL Files into SQLite 3
Insert All Values of a Table into Another Table in SQL
How to Convert Postgresql 9.4's JSONb Type to Float
How to Create a Pivot Query in SQL Server Without Aggregate Function
Generating Random Strings with T-Sql
Select a Random Sample of Results from a Query Result
When to Use Except as Opposed to Not Exists in Transact SQL
SQL Query to Get Most Recent Row for Each Instance of a Given Key
How to Alter a Postgresql Table and Make a Column Unique
Query Runs Slow with Date Expression, But Fast with String Literal
SQL Query: Simulating an "And" Over Several Rows Instead of Sub-Querying