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 |
ramya29p
Messages: 146 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 #668482 is a reply to message #668481] |
Tue, 27 February 2018 09:55 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
I wonder if this statement will help you.
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 |
Solomon Yakobson
Messages: 3286 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 #668522 is a reply to message #668480] |
Wed, 28 February 2018 00:01 |
ramya29p
Messages: 146 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 |
|
quirks
Messages: 82 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 |
Bill B
Messages: 1971 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 12:00:01 CDT 2024
|