Home » SQL & PL/SQL » SQL & PL/SQL » Convert Columns to rows (win 8,Oracle 10g)
Convert Columns to rows [message #628093] Thu, 20 November 2014 03:03 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hello,

I have two table like:-

with item_type as 
( select 101 item_id,'component1' name,1 type from dual
  union all
  select 201 item_id,'component2' name,1 type from dual
  union all
  select 301 item_id,'component3' name,2 type from dual
  union all
  select 401 item_id,'component4' name,2 type from dual
  union all
  select 501 item_id,'component5' name,3 type from dual)
  select * from item_type
  
  with item_values as
  (
  select 1 id,'One' value,101 item_id from dual
  union all
  select 2 id,'Two' value,101 item_id from dual
  union all
  select 3 id,'Three' value,101 item_id from dual
  union all
  select 4 id,'Day1' value,201 item_id from dual
  union all
  select 5 id,'Day2' value,201 item_id from dual
  union all
  select 6 id,'Yes' value,301 item_id from dual
  union all
  select 7 id,'No' value,301 item_id from dual
  union all
  select 8 id,'Others' value,301 item_id from dual
  union all
  select 9 id,'Demo' value,501 item_id from dual
  union all
  select 10 id,'Real' value,501 item_id from dual
  )
  select * from item_values


I want output like :-

 Item_ID   |  Name           |  Value
  
  101         Component1      One,Two,Three
  201         Component2      Day1,Day2
  301         Component3      Yes,No,Others
  501         Component5      Demo,Real



Please help me out..


Thanks,
Xandot

[Updated on: Thu, 20 November 2014 03:04]

Report message to a moderator

Re: Convert Columns to rows [message #628096 is a reply to message #628093] Thu, 20 November 2014 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the test case and formatting output.
As you are not in 11g+ you cannot use the useful LISTAGG function, you can use T. Kyte's STRAGG instead or hierarchical query:
SQL> with item_type as 
  2  ( select 101 item_id,'component1' name,1 type from dual
  3    union all
  4    select 201 item_id,'component2' name,1 type from dual
  5    union all
  6    select 301 item_id,'component3' name,2 type from dual
  7    union all
  8    select 401 item_id,'component4' name,2 type from dual
  9    union all
 10    select 501 item_id,'component5' name,3 type from dual),
 11  item_values as
 12    (
 13    select 1 id,'One' value,101 item_id from dual
 14    union all
 15    select 2 id,'Two' value,101 item_id from dual
 16    union all
 17    select 3 id,'Three' value,101 item_id from dual
 18    union all
 19    select 4 id,'Day1' value,201 item_id from dual
 20    union all
 21    select 5 id,'Day2' value,201 item_id from dual
 22    union all
 23    select 6 id,'Yes' value,301 item_id from dual
 24    union all
 25    select 7 id,'No' value,301 item_id from dual
 26    union all
 27    select 8 id,'Others' value,301 item_id from dual
 28    union all
 29    select 9 id,'Demo' value,501 item_id from dual
 30    union all
 31    select 10 id,'Real' value,501 item_id from dual
 32    ),
 33  item_values_bis as (
 34    select id, value, item_id,
 35           row_number() over (partition by item_id order by id) rn
 36    from item_values
 37    ),
 38  item_values_ter as (
 39    select item_id,
 40           sys_connect_by_path(value,',') vals
 41    from item_values_bis 
 42    where connect_by_isleaf = 1
 43    connect by prior item_id = item_id and prior rn = rn-1
 44    start with rn = 1
 45    )
 46  select t.item_id, t.name, substr(v.vals,2) vals
 47  from item_type t, item_values_ter v
 48  where v.item_id = t.item_id
 49  order by item_id
 50  /
   ITEM_ID NAME       VALS
---------- ---------- --------------------------------------------------
       101 component1 One,Two,Three
       201 component2 Day1,Day2
       301 component3 Yes,No,Others
       501 component5 Demo,Real

Re: Convert Columns to rows [message #628101 is a reply to message #628096] Thu, 20 November 2014 03:20 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Can I use "xmlagg" for the same?
Re: Convert Columns to rows [message #628102 is a reply to message #628101] Thu, 20 November 2014 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think so, if the option is installed, but I'm not familiar with it, you will have to wait for Solomon this evening (for you) to get the answer. Smile

Re: Convert Columns to rows [message #628151 is a reply to message #628102] Thu, 20 November 2014 08:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> (win 8,Oracle 10g)

why is it that you are willing & able to upgrade & use new OS, but stay with unsupported Oracle version V10?
I suspect that this combination is not supported by Oracle.
Re: Convert Columns to rows [message #628214 is a reply to message #628151] Fri, 21 November 2014 05:55 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
I am using 10g DB through Apex.
Re: Convert Columns to rows [message #676936 is a reply to message #628214] Tue, 30 July 2019 02:14 Go to previous messageGo to next message
Sugantha
Messages: 1
Registered: July 2019
Junior Member
You want the count of medals. So medal_count
You want the colour of each medal: medal_colour
The columns to become rows are Gold, Silver and Bronze
This gives an unpivot like:

select * from olympic_medal_tables
unpivot (medal_count for medal_colour in (
gold_medals as 'GOLD',
silver_medals as 'SILVER',
bronze_medals as 'BRONZE'
))
order by noc
fetch first 6 rows only;

NOC MEDAL_COLOUR MEDAL_COUNT
ALG GOLD 0
ALG BRONZE 0
ALG SILVER 2
ARG GOLD 3
ARG BRONZE 0
ARG SILVER 1

Nice and easy. Smile

Re: Convert Columns to rows [message #676937 is a reply to message #676936] Tue, 30 July 2019 02:56 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And completely wrong to solve the problem and not even syntactically correct in 10g, OP's version.

What is your purpose to post an incorrect solution in a 5 years old topic which has already been answered?

Previous Topic: Leading zeros in a spool file
Next Topic: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0)
Goto Forum:
  


Current Time: Thu Mar 28 07:02:34 CDT 2024