Rows to Columns 3

[xxing]

Hi I have data in a table as follows:

ID Fruit
1 Apple
1 Pear
1 Orange
2 Apple
2 KiwiFruit
3 Banana
3 Grapes

I want the data to appear as follows:

1 Apple Pear Orange
2 Apple KiwiFruit Null
3 Banana Grapes Null

The information is not concatenated but appears in a new column

Thank you for your help

Regards
Mark

 

[Dagon]

This is quite a common topic and a search of the forum should yield lots of results. One option would be:

select id, wm_concat(name)
from fruit
group by id

For Oracle-related work, contact me through Linked-In.

 

[xxing]

Thanks Dagon

 

[dkyrtata]

Xxing, did Dagon's solution work for you. I ask because the wm_concat() function was not recognized on my side. I am using Oracle 10g. Furthermore, I do not see how the function could create 3 fields from 3 unique ids.

After unsuccessfully trying to find a solution for you, I was hoping that somebody else could come up with something simple, as I am sure I will eventually need to do what you have to do now.

 

[Turkbear]

Hi,
if wm_concat is not part of your database, here is a set of alternatives ( as well as a discussion of wm_concat itself)

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[xxing]

Hi dkyrtata

wm_concat put me on the right track. My code is shown. There maybe a beter ways to do this, but my knowledge of Oracle is minimal as I have a SQL SERVER background.

Thanks to all

select 
             LIST_ID
            ,coalesce(SUBSTR(CONCAT,1,INSTR(CONCAT,',',1,1)-1),CONCAT) as ONE
            ,coalesce(SUBSTR(CONCAT,INSTR(CONCAT,',',1,1)+1,(INSTR(CONCAT,',',1,2)-1) - INSTR(CONCAT,',',1,1)),' ') as TWO
            ,coalesce(SUBSTR(CONCAT,INSTR(CONCAT,',',1,2)+1,(INSTR(CONCAT,',',1,3)-1) - INSTR(CONCAT,',',1,2)),' ') as THREE
            ,coalesce(SUBSTR(CONCAT,INSTR(CONCAT,',',1,3)+1,(INSTR(CONCAT,',',1,4)-1) - INSTR(CONCAT,',',1,3)),' ') as FOUR    
            ,coalesce(SUBSTR(CONCAT,INSTR(CONCAT,',',1,4)+1,(INSTR(CONCAT,',',1,5)-1) - INSTR(CONCAT,',',1,4)),' ') as FIVE  
            ,coalesce(SUBSTR(CONCAT,INSTR(CONCAT,',',1,5)+1,(INSTR(CONCAT,',',1,6)-1) - INSTR(CONCAT,',',1,5)),' ') as SIX 
            ,coalesce(SUBSTR(CONCAT,INSTR(CONCAT,',',1,6)+1,(INSTR(CONCAT,',',1,7)-1) - INSTR(CONCAT,',',1,6)),' ') as SEVEN 
      from
      (
          select 
                  LIST_ID
                  ,WM_CONCAT(num_class || ' ' || class_name) as concat
          from RESP_LIST
          group by 
                  LIST_ID
      )
      order by
            2,3,4,5,6,7;

 

[dkyrtata]

Xxing

Ouch, That solution looks more complex than I hoped - that's not a reflection of you but rather a reflection of what I often find to be SQL's limited capabilities to real-life problems.

However, I thought of a different approach using Data Warehousing SQL features of Oracle. The syntax of these features are difficult to understand, but have saved me many times. I will try again tomorrow but I am not sure if it will be a clean solution yet. I will post again if I come up with anything that is worth considering. I will also double check why I was unable to use WM_CONCAT()

Thanks for your post and reply

 

[dkyrtata]

This solution uses the Data Warehousing OVER(PARTITION BY) features of SQL.

Scroll down to the bottom to view the solution with its output. Then return back here to review the output of each of the embedded SELECTs.

You can cut and paste the solution into a script and run it from the SQL*PLUS prompt. You must have "CREATE/DROP TABLE" privileges to run the script successfully.

Enjoy.

-- CREATE table and INSERT data as given in Xxing's example:

DROP TABLE fruit_tbl PURGE;
CREATE TABLE fruit_tbl (
ID NUMBER,
fruit VARCHAR2(15)
);
INSERT INTO fruit_tbl VALUES(1,'Apple');
INSERT INTO fruit_tbl VALUES(1,'Pear');
INSERT INTO fruit_tbl VALUES(1,'Orange');
INSERT INTO fruit_tbl VALUES(2,'Apple');
INSERT INTO fruit_tbl VALUES(2,'KiwiFruit');
INSERT INTO fruit_tbl VALUES(3,'Banana');
INSERT INTO fruit_tbl VALUES(3,'Grapes');

SELECT
fruit,
id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY fruit) rownumber
FROM fruit_tbl
ORDER BY id, fruit,id
;
-- Above SELECT adds a row-number field for each fruit within an id
-- Fruit Id Rownumber
-- ----- -- ---------
-- Apple 1 1
-- Pear 1 2
-- Orange 1 3
-- Apple 2 1
-- KiwiFruit 2 2
-- Banana 3 1
-- Grapes 3 2

-- Embed above SELECT into the FROM clause of the SELECT below without the ORDER BY
SELECT -- Create 3 fields - 1 field for each possible rownumber
id,
DECODE(MAX(rownumber),1,fruit,NULL) Fruit1,
DECODE(MAX(rownumber),2,fruit,NULL) Fruit2,
DECODE(MAX(rownumber),3,fruit,NULL) Fruit3
FROM(SELECT -- Add a row-number field for each fruit within an id
fruit,
id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY fruit) rownumber
FROM fruit_tbl
)
GROUP BY id,fruit
ORDER BY id
;
-- Above SELECT creates 3 fields. Only 1 field of each row has a value while other 2 are NULL
--
-- ID FRUIT1 FRUIT2 FRUIT3
----------- ------- -------- ----------
-- 1 Apple
-- 1 Orange
-- 1 Pear
-- 2 Apple
-- 2 KiwiFruit
-- 3 Banana
-- 3 Grapes

-- Embed above SELECT into the FROM clause of the SELECT below without the ORDER BY
SELECT id,
MAX(fruit1) fruit1,
MAX(fruit2) fruit2,
MAX(fruit3) fruit3
FROM(SELECT -- Create 3 fields - 1 field for each possible rownumber
id,
DECODE(MAX(rownumber),1,fruit,NULL) Fruit1,
DECODE(MAX(rownumber),2,fruit,NULL) Fruit2,
DECODE(MAX(rownumber),3,fruit,NULL) Fruit3
FROM(SELECT -- Add a row-number field for each fruit within an id
fruit,
id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY fruit) rownumber
FROM fruit_tbl
)
GROUP BY id,fruit
)
GROUP BY id
;

-- Above SELECT uses the MAX() function to filter out the NULL values in each field
--
-- ID FRUIT1 FRUIT2 FRUIT3
-- --------- -------- --------- ---------------
-- 1 Apple Orange Pear
-- 2 Apple KiwiFruit
-- 3 Banana Grapes


DROP TABLE fruit_tbl PURGE;

 

[xxing]

Thanks dkyrtata

For future reference here is a slight amendment to my code. This solution works, but dkyrtata solution is better.

 SELECT LIST_ID ,
    COALESCE(SUBSTR(CONCAT,1,INSTR(CONCAT,',',1,1)-1),CONCAT)                                                AS ONE ,
    COALESCE(SUBSTR(CONCAT,INSTR(CONCAT,',',1,1)  +1,(INSTR(CONCAT,',',1,2)-1) - INSTR(CONCAT,',',1,1)),' ') AS TWO ,
    COALESCE(SUBSTR(CONCAT,INSTR(CONCAT,',',1,2)  +1,(INSTR(CONCAT,',',1,3)-1) - INSTR(CONCAT,',',1,2)),' ') AS THREE ,
    COALESCE(SUBSTR(CONCAT,INSTR(CONCAT,',',1,3)  +1,(INSTR(CONCAT,',',1,4)-1) - INSTR(CONCAT,',',1,3)),' ') AS FOUR ,
    COALESCE(SUBSTR(CONCAT,INSTR(CONCAT,',',1,4)  +1,(INSTR(CONCAT,',',1,5)-1) - INSTR(CONCAT,',',1,4)),' ') AS FIVE ,
    COALESCE(SUBSTR(CONCAT,INSTR(CONCAT,',',1,5)  +1,(INSTR(CONCAT,',',1,6)-1) - INSTR(CONCAT,',',1,5)),' ') AS SIX ,
    COALESCE(SUBSTR(CONCAT,INSTR(CONCAT,',',1,6)  +1,(INSTR(CONCAT,',',1,7)-1) - INSTR(CONCAT,',',1,6)),' ') AS SEVEN
  FROM
    (SELECT LIST_ID ,
      WM_CONCAT(num_class
      || ' '
      || class_name) || ',' AS concat
    FROM RESP_LIST
    group by LIST_ID
    )
    order by 
          2,3,4,5,6,7

Thank you for every one who helped on this topic

 

[dkyrtata]

Xxing

I was just looking at your solution again and noticed that you are are parsing a string much like I was trying to do last Friday. After abandoning the SUBSTR()/INSTR() approach, I came up with a rather clean-looking regular expression. If anybody is interested, I can post it when I return to work tomorrow.

 

[xxing]

Hi Dkyrtata

Please do post your solution. I am all in favour of better methods.

Thank you
xxing

 

[dkyrtata]

Below is the regular expression function. For clarity I used the semicolon as the delimiter instead of the comma. You can cautiously change it back.

To simplify the expression and to ensure that it can easily parse the first and last fields, I added a leading delimiter and a trailing field (i.e ";x") to your Oracle string-column, "concat". This way all the fields you want to parse out are in the middle of the string.

The example will parse out the first (real) field, indicated by "{1}". The second field would require a "{2}", etc.

Since I am running under UNIX, I must reference the "\3" with an extra backslash as "\\3". I do not know how Windows behaves. Try either case.

 REGEXP_REPLACE(';' || concat || ';x',  '^(;)(([^;]+);){1}(.+)$',  '\\3')

The regex reads something like this:
the field "concat" starts with a semicolon followed by one or more characters that is not a semicolon followed by a simi-colon, ending with an "x"

If you try to parse out beyond the number of fields that exist, the function will return the original string. You can wrap it around NULLIF() or DECODE()/SUBSTR() to null it out

So here is the revised SELECT using the semicolon as the delimiter. I added the dummy fields to your inner SELECT to make the REGEXP_REPLACE() function look less cluttered

I cannot test it, because your WM_CONCAT() function is an Oracle 11 feature:

 SELECT list_id,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){1}(.+)$', '\\3'), concat) AS one,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){2}(.+)$', '\\3'), concat) AS two,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){3}(.+)$', '\\3'), concat) AS three,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){4}(.+)$', '\\3'), concat) AS four,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){5}(.+)$', '\\3'), concat) AS five,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){6}(.+)$', '\\3'), concat) AS six,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){7}(.+)$', '\\3'), concat) AS seven,
    NULLIF(REGEXP_REPLACE(concat, '^(;)(([^;]+);){8}(.+)$', '\\3'), concat) AS eight
  FROM(SELECT list_id,
              ';' || WM_CONCAT(num_class || ' ' || class_name) || ';x' AS concat
         FROM RESP_LIST
        GROUP BY list_id
      )
 ORDER BY 2,3,4,5,6,7

Let me know if it works

 

[cmmrfrds]

Here is another way using oracles
sys_connect_by_path function
and the connect by (recursive query capability).

You could model after this example.

/* Concatenate multiple child rows into 1 column */
/* In the example DESCRIPTION has many lines of description per METRIC_ID */
WITH MY_HIERARCHY AS
(
SELECT METRIC_ID, DESCRIPTION,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY LINE_COUNT) rn,
COUNT (*) OVER (PARTITION BY METRIC_ID) cnt
FROM clarity.ACCESS_LOG_DESC
)

SELECT METRIC_ID,
REPLACE (SYS_CONNECT_BY_PATH (DESCRIPTION, ']'), ']', ' ') full_description
FROM MY_HIERARCHY
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR METRIC_ID = METRIC_ID AND PRIOR rn = rn - 1
ORDER BY METRIC_ID