{"id":18900,"date":"2019-09-10T00:11:57","date_gmt":"2019-09-10T03:11:57","guid":{"rendered":"https:\/\/orabr.com\/?p=18900"},"modified":"2019-09-10T00:32:18","modified_gmt":"2019-09-10T03:32:18","slug":"oracle-sql-converter-dinamicamente-flat-comma-em-colunas","status":"publish","type":"post","link":"https:\/\/orabr.virttus.com\/?p=18900","title":{"rendered":"ORACLE SQL – CONVERTER DINAMICAMENTE FLAT COMMA EM COLUNAS"},"content":{"rendered":"\n

\n\nWITH bounds ( id, list, start_pos, end_pos, lvl ) AS (\nSELECT ROWNUM,\ncols,\n1,\nINSTR( cols, ',' ),\n1\nFROM   ( SELECT 'col1' cols FROM DUAL UNION ALL\nSELECT 'val1,val2,val3,val4' cols FROM DUAL UNION ALL\nSELECT 'valA,valB,valC' cols FROM DUAL) data\nUNION ALL\nSELECT id,\nlist,\nend_pos + 1,\nINSTR( list, ',', end_pos + 1 ),\nlvl + 1\nFROM   bounds\nWHERE  end_pos > 0\n),\nitems ( id, item, col ) AS (\nSELECT id,\nSUBSTR(\nlist,\nstart_pos,\nDECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos\n),\nlvl\nFROM   bounds\n)\nSELECT *\nFROM   items\nPIVOT  (\nMAX( item ) FOR col IN (\n1 AS col1,\n2 AS col2,\n3 AS col3,\n4 AS col4\n)\n)\n\nWITH bounds ( id, list, start_pos, end_pos, lvl ) AS (\nSELECT ROWNUM,\ncols,\n1,\nINSTR( cols, ',' ),\n1\nFROM   ( SELECT 'col1' cols FROM DUAL UNION ALL\nSELECT 'val1,val2,val3,val4' cols FROM DUAL UNION ALL\nSELECT 'valA,valB,valC'  cols FROM DUAL\n) data\nUNION ALL\nSELECT id,\nlist,\nend_pos + 1,\nINSTR( list, ',', end_pos + 1 ),\nlvl + 1\nFROM   bounds\nWHERE  end_pos > 0\n)\nSELECT id,\nSUBSTR(\nlist,\nstart_pos,\nDECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos\n) AS item,\nlvl,\nMAX( lvl ) OVER () AS num_columns\nFROM   bounds\nORDER BY id, lvl\n\n\n\n\nCREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);\nCREATE OR REPLACE\nFUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)\n  RETURN t_my_list\nAS\n  l_string VARCHAR2(32767) := p_list || p_sep;\n  l_sep_index PLS_INTEGER;\n  l_index PLS_INTEGER := 1;\n  l_tab t_my_list     := t_my_list();\nBEGIN\n  LOOP\n    l_sep_index := INSTR(l_string, p_sep, l_index);\n    EXIT\n  WHEN l_sep_index = 0;\n    l_tab.EXTEND;\n    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));\n    l_index            := l_sep_index + 1;\n  END LOOP;\n  RETURN l_tab;\nEND cto_table;\n\n\nselect * from (\n  select rownum r , collection.*  \n    from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection\n)\nPIVOT ( \n  LISTAGG(column_value) within group (order by 1) as val \n  for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)\n)\n\n\n\n\n<\/code><\/pre>\n<\/HTML><\/HTML>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"class_list":["post-18900","post","type-post","status-publish","format-standard","hentry","category-base-de-conhecimentos"],"_links":{"self":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18900","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18900"}],"version-history":[{"count":2,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18900\/revisions"}],"predecessor-version":[{"id":18903,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18900\/revisions\/18903"}],"wp:attachment":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18900"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=18900"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=18900"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}