My query output is the below.
But I need the result to be in the below format.
-
Google for "pivot query SQL".Tim Biegeleisen– Tim Biegeleisen2018-01-18 02:17:07 +00:00Commented Jan 18, 2018 at 2:17
-
Use a sql client like squirrel that structure the results in good fashion.Vikram Palakurthi– Vikram Palakurthi2018-01-18 02:53:18 +00:00Commented Jan 18, 2018 at 2:53
Add a comment
|
3 Answers
The output can be achieved as follows -
Setup:-
CREATE TABLE TAB1
(
ID NUMBER,
ATTRNAME VARCHAR2(1024),
ATTRVALUE VARCHAR2(1024)
);
insert into tab1 values (1, 'Name', 'Mark');
insert into tab1 values (1, 'Email', '[email protected]');
insert into tab1 values (1, 'Phone num', '12234');
insert into tab1 values (2, 'Name', 'Julie');
insert into tab1 values (2, 'Email', '[email protected]');
insert into tab1 values (2, 'Phone num', '12234');
Query:-
select * from (
select * from tab1)
pivot(
max(attrvalue) for attrname in ('Name', 'Email','Phone num')
) order by id;
Result:-
ID 'Name' 'Email' 'Phone num'
1 Mark [email protected] 12234
2 Julie [email protected] 12234
Comments
This can be resolved by using the below query..
select ID,
MAX(DECODE(usr.attrname, 'NAME' , usr.ATTRVAL)) AS NAME,
MAX(DECODE(usr.attrname, 'EMAIL' , usr.ATTRVAL)) AS EMAIL,
MAX(DECODE(usr.attrname, 'Phonenum' , usr.ATTRVAL)) AS Phonenum
from usr
group by ID order by 1;
Comments
select * from employee pivot ( max(attribute) for your column in('NAME','EMAIL','Phone'));
