pivot table Oracle - how to change row items into columns
Try the query below;-
select * from
(select cars, item,value from carTable)
pivot(max(value) for item in ('wheels', 'color', 'price'))
How to convert Rows to Columns in Oracle?
If you are using Oracle 10g, you can use the DECODE function to pivot the rows into columns:
CREATE TABLE doc_tab (
loan_number VARCHAR2(20),
document_type VARCHAR2(20),
document_id VARCHAR2(20)
);
INSERT INTO doc_tab VALUES('992452533663', 'Voters ID', 'XPD0355636');
INSERT INTO doc_tab VALUES('992452533663', 'Pan card', 'CHXPS5522D');
INSERT INTO doc_tab VALUES('992452533663', 'Drivers licence', 'DL-0420110141769');
COMMIT;
SELECT
loan_number,
MAX(DECODE(document_type, 'Voters ID', document_id)) AS voters_id,
MAX(DECODE(document_type, 'Pan card', document_id)) AS pan_card,
MAX(DECODE(document_type, 'Drivers licence', document_id)) AS drivers_licence
FROM
doc_tab
GROUP BY loan_number
ORDER BY loan_number;
Output:
LOAN_NUMBER VOTERS_ID PAN_CARD DRIVERS_LICENCE
------------- -------------------- -------------------- --------------------
992452533663 XPD0355636 CHXPS5522D DL-0420110141769
You can achieve the same using Oracle PIVOT clause, introduced in 11g:
SELECT *
FROM doc_tab
PIVOT (
MAX(document_id) FOR document_type IN ('Voters ID','Pan card','Drivers licence')
);
SQLFiddle example with both solutions: SQLFiddle example
Read more about pivoting here: Pivot In Oracle by Tim Hall
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
Oracle SQL - Rows into Columns
Assuming you have one text per language per id, you can use max or min on the text column in pivot.
select * from tablename
pivot
(max(text) for language in ('english' as ENGLISH,'german' as GERMAN))
EDIT: Based on OP's comment I'm having trouble figuring out how the SELECT query works if I don't want to select everything in that case. SELECT something, text FROM tablename or similar is not working
Remember you should select all the columns you are pivoting.
select * from
(select text,language,id,something from tablename) t
pivot
(max(text) for language in ('english' as ENGLISH,'german' as GERMAN))
Edit:
To unpivot use
select *
from (
select * from
(select text,language,id,something from tablename) t
pivot
(max(text) for language in ('english' as ENGLISH,'german' as GERMAN))
) p
unpivot (text for language in (ENGLISH, GERMAN))
Efficiently convert rows to columns in sql server
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, Amount, PostalCode, LastName, AccountNumber)
) piv;
See Demo.
Pivot with unknown number of columnnames
If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from yourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
See Demo.
Using an aggregate function
If you do not want to use the PIVOT function, then you can use an aggregate function with a CASE expression:
select
max(case when columnname = 'FirstName' then value end) Firstname,
max(case when columnname = 'Amount' then value end) Amount,
max(case when columnname = 'PostalCode' then value end) PostalCode,
max(case when columnname = 'LastName' then value end) LastName,
max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable
See Demo.
Using multiple joins
This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:
select fn.value as FirstName,
a.value as Amount,
pc.value as PostalCode,
ln.value as LastName,
an.value as AccountNumber
from yourtable fn
left join yourtable a
on fn.somecol = a.somecol
and a.columnname = 'Amount'
left join yourtable pc
on fn.somecol = pc.somecol
and pc.columnname = 'PostalCode'
left join yourtable ln
on fn.somecol = ln.somecol
and ln.columnname = 'LastName'
left join yourtable an
on fn.somecol = an.somecol
and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'
How can I convert Row to Table Column in Oracle SQL Output?
You can do Pivot using row number so that you don't lose any value in your grouping while using max.
with cte as (
select 'Shell' as Item, '296.5' as Resultn from dual union all
select 'Shell' as Item, '299.8' as Resultn from dual union all
select 'Shell' as Item, '311' as Resultn from dual union all
select 'Shell' as Item, '289' as Resultn from dual union all
select 'Lining' as Item, '301.33' as Resultn from dual union all
select 'Lining' as Item, '296.5' as Resultn from dual union all
select 'Lining' as Item, '299' as Resultn from dual )
select nvl(Shell,0) Shell, nvl(Lining,0) Lining from (
select c.item, c.Resultn, row_number() over (partition by Item order by Resultn) rownumn
from cte c
) z
pivot
(
max(Resultn)
for item in ('Shell' as Shell ,'Lining' as Lining ));
Output:
SHELL LINING
289 296.5
296.5 299
311 0
299.8 301.33
Oracle Pivot query gives columns with quotes around the column names. What?
you can provide aliases to the new columns in the pivot statement's IN clause.
(NB: This is different from the standard where clause IN() which does not allow aliases.)
with testdata as
(
select 'Fred' First_Name, 10 Items from dual
union
select 'John' First_Name, 5 Items from dual
union
select 'Jane' First_Name, 12 Items from dual
union
select 'Fred' First_Name, 15 Items from dual
)
select * from testdata
pivot (
sum(Items)
for First_Name
in ('Fred' as fred,'John' as john,'Jane' as jane)
)
and also for your aggregate clause which is necessary if you have multiple clauses..
with testdata as
(
select 'Fred' First_Name, 10 Items from dual
union
select 'John' First_Name, 5 Items from dual
union
select 'Jane' First_Name, 12 Items from dual
union
select 'Fred' First_Name, 15 Items from dual
)
select * from testdata
pivot (
sum(Items) itmsum,
count(Items) itmcnt
for First_Name
in ('Fred' as fred,'John' as john,'Jane' as jane)
)
returns
FRED_ITMSUM FRED_ITMCNT JOHN_ITMSUM JOHN_ITMCNT JANE_ITMSUM JANE_ITMCNT
----------- ----------- ----------- ----------- ----------- -----------
25 2 5 1 12 1
Of course you can then go full circle and use standard oracle aliasing and rename them to whatever you like including putting quotes back in again..
with testdata as
(
select 'Fred' First_Name, 10 Items from dual
union
select 'John' First_Name, 5 Items from dual
union
select 'Jane' First_Name, 12 Items from dual
union
select 'Fred' First_Name, 15 Items from dual
)
select FRED_ITMSUM "Fred's Sum", FRED_ITMCNT "Fred's Count"
, JOHN_ITMSUM "John's Sum", JOHN_ITMCNT "John's Count"
, JANE_ITMSUM "Janes's Sum", JANE_ITMCNT "Janes's Count"
from testdata
pivot (
sum(Items) itmsum,
count(Items) itmcnt
for First_Name
in ('Fred' as fred,'John' as john,'Jane' as jane)
)
gives
Fred's Sum Fred's Count John's Sum John's Count Janes's Sum Janes's Count
---------- ------------ ---------- ------------ ----------- -------------
25 2 5 1 12 1
Related Topics
How to Get Max(Date) from Given Set of Data Grouped by Some Fields Using Pyspark
Creating a Composite Foreign Key in SQL Server 2008
How to Get a Value from Previous Result Row of a Select Statement
How to Specify in Clause in a Dynamic Query Using a Variable
The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal
Create Computed Column Using Data from Another Table
Intersection of Multiple Arrays in Postgresql
Split Date Range into One Row Per Month in SQL Server
Convert Number to Words - First, Second, Third and So On
Update and Select in One Query
How to Find Out Whether a Table Has Some Unique Columns
Using 'Like' in Ssrs Expressions
Concatenate One Field After Group By