create dynamic column from a column with multi variables

Question

Looking to Split Hobby column to N multiple columns in PostgreSQL 9.4, where each column header shows the Hobby.

Original table

Name	Hobby
Rene	Python, Monkey Bars
CJ	Trading, Python
Herlinda	Fashion
DJ	Consulting, Sales
Martha	Social Media, Teaching
Doug	Leadership, Management
Mathew	Finance, Emp Engagement
Meyers	Sleeping, Coding, CrossFit
Mike	YouTube, Athletics
Peter	Eat, Sleep, Python
Thomas	Read, Trading, Sales

notes: without using crosstab()

Desire table

desire table result image

Name	Hobby	Python	Monkey Bars	Trading	Fashion	Consutling	Sales	Social Media	Teaching	Leadership	Management	Finance	Emp Engagement	Sleeping	Coding	Crossfit	YouTube	Athletics	Eat	Sleep	Read
Rene	Python, Monkey Bars	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
CJ	Trading, Python	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Herlinda	Fashion	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
DJ	Consulting, Sales	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Martha	Social Media, Teaching	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Doug	Leadership, Management	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Mathew	Finance, Emp Engagement	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Meyers	Sleeping, Coding, CrossFit	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE
Mike	YouTube, Athletics	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE
Peter	Eat, Sleep, Python	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE
Thomas	Read, Trading, Sales	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE

---removing the 1NF removing the 1NF

CREATE TABLE ws_bi.split_clm(
  id integer PRIMARY KEY,
  name text,
  hobby text
);
INSERT INTO ws_bi.split_clm (id, name, hobby) VALUES
(1, 'Rene', 'Python, Monkey Bars'),
(2, 'CJ', 'Trading, Python'),
(3, 'Herlinda', 'Fashion'),
(4, 'DJ', 'Consulting, Sales'),
(5, 'Martha', 'Social Media, Teaching'),
(6, 'Doug', 'Leadership, Management'),
(7, 'Mathew', 'Finance, Emp Engagement'),
(8, 'Meyers', 'Sleeping, Coding, CrossFit'),
(9, 'Mike', 'YouTube, Athletics'),
(10, 'Peter', 'Eat, Sleep, Python'),
(11, 'Thomas', 'Read, Trading, Sales');
/***query****/
SELECT id, unnest(string_to_array(hobby, ', ')) AS values 
FROM ws_bi.split_clm
ORDER BY id;

result image result image

---update March.2.2023. by using this solution: stackoverflow.com/questions/50299360

code creator: L. Rodgers

DB FIDDLE (UK): https://dbfiddle.uk/Sn7iO4zL

Making some adjustment from the L. Rodgers solution for some reason still falling... maybe because Json function are something very new for me. image result tmpMoToJson

     ---sample data 
DROP TABLE IF EXISTS ws_bi.split_clm;
CREATE TABLE ws_bi.split_clm(
  id integer PRIMARY KEY,
  name text,
  hobby text, 
  value int
);
INSERT INTO ws_bi.split_clm (id, name, hobby,value) VALUES
(1, 'Rene', 'Python, Monkey_Bars','5'),
(2, 'CJ', 'Trading, Python','25'),
(3, 'Herlinda', 'Fashion','15'),
(4, 'DJ', 'Consutling, Sales','35'),
(5, 'Martha', 'Social_Media, Teaching','45'),
(6, 'Doug', 'Leadership, Management','55'),
(7, 'Mathew', 'Finance, Emp_Engagement','65'),
(8, 'Mayers', 'Sleeping, Coding, Crossfit','75'),
(9, 'Mike', 'YouTube, Athletics','85'),
(10, 'Peter', 'Eat, Sleep, Python','95'),
(11, 'Thomas', 'Read, Trading, Sales','105');
/****query****/
--1NF <done>
--DROP TABLE IF EXISTS ws_bi.split_clm_Nor;
  CREATE     TABLE     ws_bi.split_clm_Nor  AS  (
  SELECT id, name, unnest(string_to_array(hobby, ', ')) AS Ivalues , value
  FROM ws_bi.split_clm
  ORDER BY id
--;
) with data
    DISTRIBUTED BY (id) ;
 Analyze  ws_bi.split_clm_Nor;
 --Select * from   ws_bi.split_clm_Nor limit 6; ---
 ---ver 2.0
 --DROP TABLE IF EXISTS ws_bi.split_clm_Nor2;
  CREATE     TABLE     ws_bi.split_clm_Nor2  AS (
  SELECT id, name, lower(unnest(string_to_array(hobby, ', '))) AS Ivalues , value,count(1)  as "Case_Volume"
  FROM ws_bi.split_clm
  GROUP BY 1,2,3,4
  ORDER BY id
--;
) with data
    DISTRIBUTED BY (id) ;
 Analyze  ws_bi.split_clm_Nor2;
 --Select * from   ws_bi.split_clm_Nor2 limit 6; 
 ---------------------------

 DROP TABLE IF EXISTS  ws_bi.tmpTblTyp2 CASCADE ; 
DO LANGUAGE plpgsql $$ 
DECLARE v_sqlstring VARCHAR  = ''; 
BEGIN 
v_sqlstring := CONCAT( 'CREATE  TABLE ws_bi.tmpTblTyp2 AS SELECT '   
                       ,(SELECT  STRING_AGG( CONCAT('NULL::int AS ' , ivalues )::TEXT , ' ,' 
                            ORDER BY ivalues                        
                           )::TEXT
                           FROM
                           (SELECT DISTINCT ivalues  FROM ws_bi.split_clm_Nor2 )a
                        )
                      ,' LIMIT 0 '    
                       ) ; -- RAISE NOTICE '%', v_sqlstring ;  
EXECUTE( v_sqlstring ) ; END $$; 
 --------------------------------------------
 DROP TABLE IF EXISTS ws_bi.tmpMoJson ;
CREATE     TABLE     ws_bi.tmpMoJson  AS    (
--CREATE TEMP TABLE tmpMoJson AS
      SELECT 
         name AS name
         ,(json_build_array( mivalues )) AS js_mivalues_arr
         ,json_populate_recordset ( NULL::ws_bi.tmpTblTyp2 /** use temp table as a record type!!*/
                                   , json_build_array( mivalues )  /** builds row-type column that can be expanded with (jprs).* */
                                  ) jprs /**no error with wrong JSON values result under jprs**/
      FROM ( SELECT name
             ,json_object_agg(ivalues,value) AS mivalues 
             FROM ws_bi.split_clm_Nor2
             GROUP BY 1
             ORDER BY 1
            ) a
--;
) with data
    DISTRIBUTED BY (name) ;
 Analyze  ws_bi.tmpMoJson;
--Select * from   ws_bi.tmpMoJson;  
 SELECT  
  name
,(ROW((jprs).*)::ws_bi.tmpTblTyp2).* -- explode the composite type row
FROM ws_bi.tmpMoJson ;

DB FIDDLE (UK) before : https://dbfiddle.uk/BoyKmDrT

DB FIDDLE (UK) after : https://dbfiddle.uk/gP0kAnZ5

You're going in the wrong direction. Multiple keys in a column violate [first-normal form](https://en.wikipedia.org/wiki/First_normal_form) and are going to cause you unending problems. You need to [normalize your database](https://towardsdatascience.com/a-complete-guide-to-database-normalization-in-sql-6b16544deb0) — Corvus, Feb 28 '23 at 15:47
I'm new to using this site, so I'm terribly sorry if I accidentally erased some of the discussions. Because I see some of those conversations are gone and I don't see how to recover it — C'perota, Mar 02 '23 at 19:58
Without an error code, I am still unable to determine why 'jprs' shows only a series of comma symbols instead of values with their comma symbol. json_populate_recordset (null::ws_svc_gsa_bi.tmpTblTyp2, json_build_array( mivalues ) ) jprs — C'perota, Mar 02 '23 at 20:52
What will be the best approach to find out the reason to get no error with wrong JSON values result? — C'perota, Mar 02 '23 at 22:13
for reference: https://stackoverflow.com/questions/15506199/ https://stackoverflow.com/questions/50299360 https://www.postgresql.org/docs/9.4/functions-json.html — C'perota, Mar 02 '23 at 22:17

score 0 · Answer 1 · answered Mar 01 '23 at 23:32

0

I am not trying to use tablefunc/crosstab.

 ---ver 2.0
 --DROP TABLE IF EXISTS ws_bi.split_clm_Nor2;
  CREATE     TABLE     ws_bi.split_clm_Nor2  AS (
  SELECT id, name, unnest(string_to_array(hobby, ', ')) AS Ivalues 
  FROM ws_bi.split_clm
  GROUP BY 1,2,3
  ORDER BY id
--;
) with data
    DISTRIBUTED BY (id) ;
 Analyze  ws_bi.split_clm_Nor2;
 --Select * from   ws_bi.split_clm_Nor2 limit 6;

---result of this last query in the image

SELECT  name
,json_object_agg(Ivalues, 'TRUE') AS Mains 
FROM   ws_bi.split_clm_Nor2
GROUP BY name
ORDER BY  name;

picture result

answered Mar 01 '23 at 23:32

C'perota

3
4

I'm going to try this https://stackoverflow.com/questions/50299360/ – C'perota Mar 02 '23 at 16:13
I'm new to using this site, so I'm terribly sorry if I accidentally erased some of the discussions. Because I see some of those conversations are gone and I don't see how to recover it . – C'perota Mar 02 '23 at 19:58
lol.. the issue is related to case sensitive :) – C'perota Mar 03 '23 at 18:55

score 0 · Accepted Answer · answered Mar 03 '23 at 20:18

0

to get it work , just change to lower caps the column Ivalues (hobby)

DB FIDDLE (UK) after : https://dbfiddle.uk/gP0kAnZ5

answered Mar 03 '23 at 20:18

C'perota

3
4

create dynamic column from a column with multi variables

2 Answers2

Linked