Home » SQL & PL/SQL » SQL & PL/SQL » comma separated string to column dynamically
comma separated string to column dynamically [message #668480] Tue, 27 February 2018 08:47 Go to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
I want to separate comma separated string to column dynamically

emp_id  country_list
1       INDIA,CHINA,JAPAN,U.K
2       CHINA,U.K
3       JAPAN,U.K


My expected result set whould be

emp_id  INDIA  CHINA   JAPAN  U.K
1         Y     Y       Y      Y
2         N     Y       N      Y
3         N     N       Y      Y


Could any one help me out.


[Updated on: Tue, 27 February 2018 08:49]

Report message to a moderator

Re: comma separated string to column dynamically [message #668481 is a reply to message #668480] Tue, 27 February 2018 08:52 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
Storing more than 1 value in single column violates Third Normal Form & any person who does so should be terminated immediately for incompetence.
Re: comma separated string to column dynamically [message #668482 is a reply to message #668481] Tue, 27 February 2018 09:55 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
I wonder if this statement will help you. Laughing
WITH
    TAB(EMP_ID, COUNTRY_LIST) AS
        (SELECT 1, 'INDIA,CHINA,JAPAN,U.K' FROM DUAL
         UNION ALL
         SELECT 2, 'CHINA,U.K' FROM DUAL
         UNION ALL
         SELECT 3, 'JAPAN,U.K' FROM DUAL),
    VALUES2ROWS(EMP_ID, COUNTRY, COUNTRY_LIST) AS
        (SELECT EMP_ID, REGEXP_SUBSTR(COUNTRY_LIST, '[^,]+'), REGEXP_REPLACE(COUNTRY_LIST, '[^,]+[,]*(.*)', '\1')
           FROM TAB
         UNION ALL
         SELECT EMP_ID, REGEXP_SUBSTR(COUNTRY_LIST, '[^,]+'), REGEXP_REPLACE(COUNTRY_LIST, '[^,]+[,]*(.*)', '\1')
           FROM VALUES2ROWS
          WHERE COUNTRY_LIST IS NOT NULL),
    RESULT AS
        (SELECT EMP_ID.EMP_ID
               ,COUNTRY.COUNTRY
               ,COALESCE(
                    (SELECT 'Y'
                       FROM VALUES2ROWS
                      WHERE VALUES2ROWS.EMP_ID = EMP_ID.EMP_ID AND VALUES2ROWS.COUNTRY = COUNTRY.COUNTRY)
                   ,'N'
                )
                    EXIST
           FROM (
                    SELECT DISTINCT EMP_ID
                      FROM VALUES2ROWS
                ) EMP_ID
               ,(
                    SELECT DISTINCT COUNTRY
                      FROM VALUES2ROWS
                ) COUNTRY)
SELECT *
  FROM RESULT PIVOT (MAX(EXIST) FOR COUNTRY IN ('INDIA' AS INDIA, 'CHINA' AS CHINA, 'JAPAN' AS JAPAN, 'U.K' AS "U.K"));

[Updated on: Tue, 27 February 2018 10:17]

Report message to a moderator

Re: comma separated string to column dynamically [message #668483 is a reply to message #668482] Tue, 27 February 2018 10:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
Registered: January 2010
Location: Connecticut, USA
Senior Member
Seems like a lot of effort for a simple thing. Your solution is still static and relies on country list containing only INDIA, CHINA, JAPAN or U.K:

WITH TAB(EMP_ID,COUNTRY_LIST)
  AS (
      SELECT 1, 'INDIA,CHINA,JAPAN,U.K' FROM DUAL UNION ALL
      SELECT 2, 'CHINA,U.K' FROM DUAL UNION ALL
      SELECT 3, 'JAPAN,U.K' FROM DUAL
     )
SELECT  EMP_ID,
        CASE INSTR(',' || COUNTRY_LIST || ',',',INDIA,') WHEN 0 THEN 'N' ELSE 'Y' END INDIA,
        CASE INSTR(',' || COUNTRY_LIST || ',',',CHINA,') WHEN 0 THEN 'N' ELSE 'Y' END CHINA,
        CASE INSTR(',' || COUNTRY_LIST || ',',',JAPAN,') WHEN 0 THEN 'N' ELSE 'Y' END JAPAN,
        CASE INSTR(',' || COUNTRY_LIST || ',',',U.K,') WHEN 0 THEN 'N' ELSE 'Y' END UK
  FROM  TAB
/

    EMP_ID INDIA      CHINA      JAPAN      UK
---------- ---------- ---------- ---------- ----------
         1 Y          Y          Y          Y
         2 N          Y          N          Y
         3 N          N          Y          Y

SQL> 

SY.

[Updated on: Tue, 27 February 2018 10:21]

Report message to a moderator

Re: comma separated string to column dynamically [message #668484 is a reply to message #668483] Tue, 27 February 2018 10:25 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
Solomon Yakobson wrote on Tue, 27 February 2018 10:19
Seems like a lot of effort for a simple thing. Your solution is still static and relies on country list containing only INDIA, CHINA, JAPAN or U.K:
I know. I's probably the worst statement I've ever written, but I was just in the mood. In my country we have the saying "just to confuse the russians" if you over complicate things.
Re: comma separated string to column dynamically [message #668499 is a reply to message #668484] Tue, 27 February 2018 13:08 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you take a look at Forum statistics (ZIP file), you'd see that - out of ~112.000 members - only 9 of them are from Russia, which means that you confused us and not the Russians Smile
Re: comma separated string to column dynamically [message #668520 is a reply to message #668480] Tue, 27 February 2018 22:17 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
Hi All,
Thanks for your reply.

In case if I have around 50 countries as comma separated values should I write the case statement 50 times??

Is there any way to write it dynamically.
Re: comma separated string to column dynamically [message #668521 is a reply to message #668520] Tue, 27 February 2018 22:50 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
ramya29p wrote on Tue, 27 February 2018 20:17
Hi All,
Thanks for your reply.

In case if I have around 50 countries as comma separated values should I write the case statement 50 times??

Is there any way to write it dynamically.
Where exactly do you "have around 50 countries"?
You can write SQL to write SQL; assuming that country name reside in table column.
Re: comma separated string to column dynamically [message #668522 is a reply to message #668480] Wed, 28 February 2018 00:01 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
If the Country list is like as below
empid   Country_list
1       INDIA,U.K,JAPAN,CHINA,U.S.A,RUSSIA,CANADA


to get the 7 countries as a column, instead of writing 7 case statement, do we have any dynamic way to get it.?

[Updated on: Wed, 28 February 2018 00:06]

Report message to a moderator

Re: comma separated string to column dynamically [message #668528 is a reply to message #668522] Wed, 28 February 2018 02:32 Go to previous messageGo to next message
quirks
Messages: 81
Registered: October 2014
Member
To pick up Solomon Yakobsons and BlackSwans suggestions, you can use the following statement to generate a new statement:
WITH
    TAB(EMP_ID, COUNTRY_LIST) AS
        (SELECT 1, 'INDIA,CHINA,JAPAN,U.K' FROM DUAL
         UNION ALL
         SELECT 2, 'CHINA,U.K' FROM DUAL
         UNION ALL
         SELECT 3, 'JAPAN,U.K' FROM DUAL),
    VALUES2ROWS(EMP_ID, COUNTRY, COUNTRY_LIST) AS
        (SELECT EMP_ID, REGEXP_SUBSTR(COUNTRY_LIST, '[^,]+'), REGEXP_REPLACE(COUNTRY_LIST, '[^,]+[,]*(.*)', '\1')
           FROM TAB
         UNION ALL
         SELECT EMP_ID, REGEXP_SUBSTR(COUNTRY_LIST, '[^,]+'), REGEXP_REPLACE(COUNTRY_LIST, '[^,]+[,]*(.*)', '\1')
           FROM VALUES2ROWS
          WHERE COUNTRY_LIST IS NOT NULL),
    CASES AS
        (SELECT DISTINCT
                   'CASE INSTR('','' || COUNTRY_LIST || '','','','
                || COUNTRY
                || ','') WHEN 0 THEN ''N'' ELSE ''Y'' END "'
                || COUNTRY
                || '"'
                    AS CASE_STAT
           FROM VALUES2ROWS)
SELECT 'SELECT EMP_ID, ' || LISTAGG(CASE_STAT, ', ') WITHIN GROUP (ORDER BY 1) || ' FROM  TAB' AS FINAL_STATEMENT
  FROM CASES;


FINAL_STATEMENT
---------------
SELECT EMP_ID, CASE INSTR(',' || COUNTRY_LIST || ',',',CHINA,') WHEN 0 THEN 'N' ELSE 'Y' END "CHINA", CASE INSTR(',' || COUNTRY_LIST || ',',',INDIA,') WHEN 0 THEN 'N' ELSE 'Y' END "INDIA", CASE INSTR(',' || COUNTRY_LIST || ',',',JAPAN,') WHEN 0 THEN 'N' ELSE 'Y' END "JAPAN", CASE INSTR(',' || COUNTRY_LIST || ',',',U.K,') WHEN 0 THEN 'N' ELSE 'Y' END "U.K" FROM  TAB
The resulting statement you can use to receive the information you want:
WITH
    TAB(EMP_ID, COUNTRY_LIST) AS
        (SELECT 1, 'INDIA,CHINA,JAPAN,U.K' FROM DUAL
         UNION ALL
         SELECT 2, 'CHINA,U.K' FROM DUAL
         UNION ALL
         SELECT 3, 'JAPAN,U.K' FROM DUAL)
SELECT EMP_ID, CASE INSTR(',' || COUNTRY_LIST || ',',',CHINA,') WHEN 0 THEN 'N' ELSE 'Y' END "CHINA", CASE INSTR(',' || COUNTRY_LIST || ',',',INDIA,') WHEN 0 THEN 'N' ELSE 'Y' END "INDIA", CASE INSTR(',' || COUNTRY_LIST || ',',',JAPAN,') WHEN 0 THEN 'N' ELSE 'Y' END "JAPAN", CASE INSTR(',' || COUNTRY_LIST || ',',',U.K,') WHEN 0 THEN 'N' ELSE 'Y' END "U.K" FROM  TAB

But to answer your question. Since pivot only works with a static list of values I don't know of any other way to "dynamically" generate columns in SQL. But I'm not as educated as the others around. Probably they know.

Have you ever tried PL/SQL?

[Updated on: Wed, 28 February 2018 02:33]

Report message to a moderator

Re: comma separated string to column dynamically [message #668530 is a reply to message #668528] Wed, 28 February 2018 07:35 Go to previous message
Bill B
Messages: 1813
Registered: December 2004
Senior Member
Why are you using such a horrible table design. Instead of doing

emp_id country_list
1 INDIA,CHINA,JAPAN,U.K
2 CHINA,U.K
3 JAPAN,U.K

make a table of the form

emp_id country_list
1 INDIA
1 CHINA
1 JAPAN
1 U.K
2 CHINA
2 U.K
3 JAPAN
3 U.K

Previous Topic: Compare row & column data
Next Topic: help with error executing a view
Goto Forum:
  


Current Time: Tue Nov 13 14:02:23 CST 2018