How to convert columns data into rows

Hello All,
   Need some help in converting columns data into rows.
 
The data is something like this
  patient, date_modified,firstname,lastname, assessment, instr, misc, objective
   1,02/02/2008,John,Doe I,J,K,L
   2,02/05/2007, Marvin,Garcia K,L,M,N

Now I want my results to be like the below by concatenating some strings in front of the columns.I want my results to be in the format below

Patient: 1
Last Modified : 02/02/2008
EnteredBy: John Doe
Assessment: I
Instr: J
misc: k
Objective:L

Patient: 2
Last Modified : 02/05/2008
EnteredBy: Marvin Garcia
Assessment: k
Instr: L
misc: M
Objective:N

I tried to write an initial sql to concate the strings
SELECT 'Patient:' || pn.Patient, 
	'Last Modified'|| pn.LastModified, 
	 case when pn.PIM = 'A' THEN 'Assessment:'||pn.Assessment ELSE '' END Assessment,
	'EnteredBy:'|| ecm.firstname||' '|| ecm.lastname as Ecm_name,
	'Instr:'||pn.instr,
	'misc:'|| pn.misc,
	'objective:'||pn.objective
	from Patient p
 	left join tableECM ecm 
 	on pn.patient= ecm.User;

Open in new window

cutie_smilyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anandmahajanCommented:
I looked around on web and got something like this:

create table MAIN_TBL ( magazine varchar2(10), region varchar2(5), quantity int );

insert into MAIN_TBL values ( 'Playboy', 'Nord', 1 );
insert into MAIN_TBL values ( 'Playboy', 'East', 2 );
insert into MAIN_TBL values ( 'AutoWeek', 'Nord', 3 );
insert into MAIN_TBL values ( 'AutoWeek', 'West', 4 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 5 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 6 );
insert into MAIN_TBL values ( 'Wired', 'West', 7 );
insert into MAIN_TBL values ( 'Wired', 'East', 8 );
insert into MAIN_TBL values ( 'Wired', 'South', 9 );

declare
report_exists number;
report_owner varchar(30) := 'FRODO' ;
--
report_name varchar(30) := 'REPORT_TBL' ;
query_main varchar(16000) := 'create table ' || report_owner || '.' || report_name || ' as select MAGAZINE ' ;
--
query_part varchar(1024) ;
my_var varchar2(5);

cursor cur_region is select distinct REGION from MAIN_TBL order by region;
begin

select count(*) into report_exists
from dba_tables
where table_name = report_name and owner = report_owner;

if ( report_exists = 1 ) then
execute immediate 'drop table ' || report_owner || '.' || report_name ;
end if;

open cur_region ;
loop
fetch cur_region into my_var ;
exit when cur_region%NOTFOUND;
query_part := 'select nvl(sum(quantity),0) from MAIN_TBL x where x.magazine = main.magazine and x.region='''||my_var||'''' ;
query_main := query_main || chr(10) || ',(' || query_part || ')"' || my_var || '"';
end loop;
close cur_region ;
query_main := query_main || ' from (select distinct MAGAZINE from MAIN_TBL ) main' ;

execute immediate query_main ;

end;
/
select * from FRODO.REPORT_TBL
/

Output:
MAGAZINE East Nord South West
---------- ----- ----- ------ -------
AutoWeek 0 3 0 4
Playboy 2 1 0 0
Wired 8 11 9 7

http://code.techinterviews.com/

Anand
0
Jinesh KamdarCommented:
Anand, the code u provided is converting rows into colmns whereas the asker wants to do the opposite.

Cutie, is PL/SQL an option or it has to be a single SELECT query?
0
Fekrat El WehediInfrastructure Architect / Oracle DBACommented:
If you are only interested in the results only then why don't you use a cursor in plsql and write the data to a file which would obviously produce the same output you requested.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
This should do:
select case when temp_col = 1 then 'Patient: '||val
            when temp_col = 2 then 'Last Modified: '||val
            when temp_col = 3 then 'Entered By: '||val
            when temp_col = 4 then 'Assessment: '||val
            when temp_col = 5 then 'Instr: '||val
            when temp_col = 6 then 'Misc: '||val
            when temp_col = 7 then 'Objective: '||val
       else null end str
from
(
select patient id, 1 temp_col, to_char(patient) val from Patient
union all
select patient id, 2 temp_col, to_char(date_modified, 'dd/mm/yyyy') val from Patient
union all
select patient id, 3 temp_col, to_char(firstname||' '||lastname) val 
from Patient p
left join tableECM ecm 
on pn.patient= ecm.User
union all
select patient id, 4 temp_col, to_char(assessment) val from Patient
union all
select patient id, 5 temp_col, to_char(instr) val from Patient
union all
select patient id, 6 temp_col, to_char(misc) val from Patient
union all
select patient id, 7 temp_col, to_char(objective) val from Patient
union all
select patient id, 8 temp_col, null val from Patient
)
order by id, temp_col
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cutie_smilyAuthor Commented:
I am just interested in result. PL/SQL can be used..
0
cutie_smilyAuthor Commented:
Thanks for the inputs. I want this to be done in sql and also I cannot create the table, Let me run the SQL and get back.

Thanks
0
cutie_smilyAuthor Commented:
Apologize for getting back late.
sujith80: Your result seems to be working for me except that I am getting duplicate records. I guess it is because of the union all.

I raised the points.
Is it possible to pick the entire row based on the max date_modified. For one patient there are more than two modified dates. I want to pick the max date_modified and pick the other columns based on this date.
Something like max(date_modified || assessment || instr || objective || misc) where we get all with max modified date.
Thanks for all the inputs.
0
Jinesh KamdarCommented:
You mean something like the below ? Must say Sujith has written a really smart query there :)
select case when temp_col = 1 then 'Patient: '||val
            when temp_col = 2 then 'Last Modified: '||val
            when temp_col = 3 then 'Entered By: '||val
            when temp_col = 4 then 'Assessment: '||val
            when temp_col = 5 then 'Instr: '||val
            when temp_col = 6 then 'Misc: '||val
            when temp_col = 7 then 'Objective: '||val
       else null end str
from
(
select patient id, 1 temp_col, to_char(patient) val from Patient
union all
select patient id, 2 temp_col, to_char(MAX(date_modified), 'dd/mm/yyyy') val from Patient GROUP BY patient
union all
select patient id, 3 temp_col, to_char(firstname||' '||lastname) val 
from Patient p
left join tableECM ecm 
on pn.patient= ecm.User
union all
select patient id, 4 temp_col, to_char(assessment) val from Patient pt
WHERE date_modified = (SELECT MAX(date_modified) FROM patient WHERE patient = pt.patient)
union all
select patient id, 5 temp_col, to_char(instr) val from Patient pt
WHERE date_modified = (SELECT MAX(date_modified) FROM patient WHERE patient = pt.patient)
union all
select patient id, 6 temp_col, to_char(misc) val from Patient pt
WHERE date_modified = (SELECT MAX(date_modified) FROM patient WHERE patient = pt.patient)
union all
select patient id, 7 temp_col, to_char(objective) val from Patient pt
WHERE date_modified = (SELECT MAX(date_modified) FROM patient WHERE patient = pt.patient)
union all
select patient id, 8 temp_col, null val from Patient
)
order by id, temp_col

Open in new window

0
cutie_smilyAuthor Commented:
Thank you all for the inputs. The problem is solved using most of Sujith's solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.