Using pivot on multiple columns of an Oracle row
As the documentation shows, you can have multiple aggregate function clauses. So you can do this:
select * from (
select * from tab1
)
pivot (
count(type) as ct, sum(weight) as wt, sum(height) as ht
for type in ('A' as A, 'B' as B, 'C' as C)
);
A_CT A_WT A_HT B_CT B_WT B_HT C_CT C_WT C_HT
---- ---- ---- ---- ---- ---- ---- ---- ----
2 110 22 1 40 8 1 30 15
If you want the columns in the order you showed then add another level of subquery:
select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht
from (
select * from (
select * from tab1
)
pivot (
count(type) as ct, sum(weight) as wt, sum(height) as ht
for type in ('A' as A, 'B' as B, 'C' as C)
)
);
A_CT B_CT C_CT A_WT B_WT C_WT A_HT B_HT C_HT
---- ---- ---- ---- ---- ---- ---- ---- ----
2 1 1 110 40 30 22 8 15
SQL Fiddle.
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.
Pivoting of data using two columns
Here is a way to get the data in the format you want:
SELECT user_id,
max(case when lang = 'FI' THEN org ELSE ' ' END) org_fi,
max(case when lang = 'FI' THEN position ELSE ' ' END) position_fi,
max(case when lang = 'EN' THEN org ELSE ' ' END) org_en,
max(case when lang = 'EN' THEN position ELSE ' ' END) position_en,
max(case when lang = 'SV' THEN org ELSE ' ' END) org_sv,
max(case when lang = 'SV' THEN position ELSE ' ' END) position_sv
FROM source
group by user_id
order by user_id
See SQL Fiddle with Demo
In Oracle, how do I pivot data into multiple columns?
Use the ROW_NUMBER()
analytic function to give each SITE_ID_ALT
a column number for each SITE_ID
and then you can PIVOT
on that:
Query:
INSERT INTO site_id_cd_result ( site_id, site_id_alt_01, site_id_alt_02, site_id_alt_03 )
SELECT site_id,
site_id_alt_01,
site_id_alt_02,
site_id_alt_03
FROM (
SELECT t.*,
ROW_NUMBER() OVER ( PARTITION BY site_id ORDER BY ROWNUM ) AS rn
FROM site_id_cd_test t
-- WHERE site_id <> site_id_alt -- If you don't want the matching value
)
PIVOT ( MAX( site_id_alt ) FOR rn IN (
1 AS site_id_alt_01,
2 AS site_id_alt_02,
3 AS site_id_alt_03
) )
Result:
SELECT * FROM site_id_cd_result;
SITE_ID | SITE_ID_ALT_01 | SITE_ID_ALT_02 | SITE_ID_ALT_03
:------ | :------------- | :------------- | :-------------
1A1-071 | 1A1-071 | 1A1-071O | 030256
1A1-072 | 04268383 | null | null
db<>fiddle here
PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data
You aren't doing anything with the description, which also varies with the tag. It isn't aggregated so it in the implicit 'group by', so you get separate rows in the result set.
You can either capture that too with another (dummy) aggregate:
select * from (
select * from TEST2 where tag in ('LN', 'SN')
)
PIVOT
(
max(value) as value, max(description) as description
for tag in ('LN' as ln, 'SN' as sn)
)
order by category, subcat, item, "Date";
Date SUBCAT CATEGOR IT LN_VALUE LN_DESCRIPTION SN_VALUE SN_DESCRIPTION
--------- ------ ------- -- ----------------- --------------- ----------------- ---------------
24-OCT-13 290223 1219576 25 1105618 Lot Number 3x12mm Serial Number
24-OCT-13 290223 1219576 28 1303757 Lot Number
18-JUN-15 354506 1219576 4 1403114 Lot Number
18-JUN-15 354506 1219576 9 7777777777 Lot Number 9.999999999999E12 Serial Number
Or more likely exclude it from the intermediate result set if you don't want it, by specify the columns you do want instead of using *
:
select * from (
select category, subcat, item, "Date", tag, value
from TEST2 where tag in ('LN', 'SN')
)
PIVOT
(
max(value) for tag in ('LN' as ln, 'SN' as sn)
)
order by category, subcat, item, "Date";
CATEGOR SUBCAT IT Date LN SN
------- ------ -- --------- ----------------- -----------------
1219576 290223 25 24-OCT-13 1105618 3x12mm
1219576 290223 28 24-OCT-13 1303757
1219576 354506 4 18-JUN-15 1403114
1219576 354506 9 18-JUN-15 7777777777 9.999999999999E12
Most efficient way to pivot multiple groups over multiple columns using Oracle SQL?
I would suggest aggregation:
select name,
max(case when meal = 'Lunch' then food end) as lunch_food,
max(case when meal = 'Lunch' then drink end) as lunch_drink,
max(case when meal = 'Dinner' then food end) as dinner_food,
max(case when meal = 'Dinner' then drink end) as dinner_drink
from example
group by name;
However, if you have a large table, you might compare that to:
select name, l.food, l.drink, d.food, d.drink
from (select e.*
from example e
where meal = 'Lunch'
) l full join
(select e.*
from dinner e
where meal = 'Dinner'
) d
using (name);
Oracle has efficient mechanisms for aggregation. It is hard to say off-hand which will be faster, so you should try on your data.
Oracle Pivot Based on Multiple Columns
Try:
select *
from
(
select ISSUEID,ANSWER, ANSWERCOMMENT,QUESTION ,
rownum rn
from issue_survey
WHERE ISSUEID = 6877
) d
pivot
(
max(QUESTION) as question, max(Answer) as answer,
max( ANSWERCOMMENT ) as ANSWERCOMMENT
for rn in ( 1 ,2 , 3 )
) piv;
Demo: http://www.sqlfiddle.com/#!4/e5aba7/6
| ISSUEID | 1_QUESTION | 1_ANSWER | 1_ANSWERCOMMENT | 2_QUESTION | 2_ANSWER | 2_ANSWERCOMMENT | 3_QUESTION | 3_ANSWER | 3_ANSWERCOMMENT |
|---------|-------------------|----------|-----------------|--------------|----------|-----------------|-----------------|----------|-----------------|
| 6877 | Do you wanna wait | YES | TEST1 | How about it | Its okay | TEST2 | Sample question | (null) | TEST3
Related Topics
How to Send Email from SQL Server
Is the Like Operator Case-Sensitive with SQL Server
How to Combine Multiple Rows into a Comma-Delimited List in SQL Server 2005
How to Sort a Varchar Column in SQL Server That Contains Numbers
Solution For: Store Update, Insert, or Delete Statement Affected an Unexpected Number of Rows (0)
Why Are Relational Set-Based Queries Better Than Cursors
SQL Error: Ora-01861: Literal Does Not Match Format String 01861
How to Create Table Using Select Query in SQL Server
Find the Smallest Unused Number in SQL Server
SQL Query - Concatenating Results into One String
Coldfusion Adding Extra Quotes When Constructing Database Queries in Strings
Dynamic Pivot Table in SQL Server
Get Top Row(S) with Highest Value, with Ties
Create Custom Function for Date Difference Excluding Weekends and Holidays in Oracle SQL
How to Import a Large Ms SQL .SQL File
SQL Server Loop - How to Loop Through a Set of Records
Sql: Parse the First, Middle and Last Name from a Fullname Field