Skip to content

Instantly share code, notes, and snippets.

@VegarRingdalAibel

VegarRingdalAibel/columns to rows.sql

Created March 3, 2023 06:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save VegarRingdalAibel/88fb199dcc38ed8c7b633d52b12a2743 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save VegarRingdalAibel/88fb199dcc38ed8c7b633d52b12a2743 to your computer and use it in GitHub Desktop.
Download ZIP
columns to rows
Raw
columns to rows.sql
WITH ht_lines_and_docs AS (
--------------------------------
-- input data
--------------------------------
SELECT
tag tag_ref,
lines lines_ref,
docs docs_ref
FROM
test
), ht_lines_columns AS (
--------------------------------
-- column data
--------------------------------
SELECT
tag_ref,
lines_ref
FROM
ht_lines_and_docs
), ht_lines_row AS (
--------------------------------
-- row data (magic*)
--------------------------------
SELECT
tag_ref,
TRIM(regexp_substr(lines_ref, '[^,]+', 1, colno)) line,
colno
FROM
ht_lines_columns
CROSS JOIN LATERAL (
SELECT
level colno
FROM
dual
CONNECT BY
level <= length(lines_ref) - length(replace(lines_ref, ',')) + 1
)
), ht_docs_columns AS (
--------------------------------
-- column data
--------------------------------
SELECT
tag_ref,
docs_ref
FROM
ht_lines_and_docs
), ht_docs_row AS (
--------------------------------
-- row data (magic*)
--------------------------------
SELECT
tag_ref,
TRIM(regexp_substr(docs_ref, '[^,]+', 1, colno)) doc,
colno
FROM
ht_docs_columns
CROSS JOIN LATERAL (
SELECT
level colno
FROM
dual
CONNECT BY
level <= length(docs_ref) - length(replace(docs_ref, ',')) + 1
)
), ht_colno AS (
--------------------------------
-- unique data
--------------------------------
SELECT DISTINCT
tag_ref,
colno
FROM
(
SELECT
tag_ref,
colno
FROM
ht_lines_row
UNION ALL
SELECT
tag_ref,
colno
FROM
ht_docs_row
)
), ht_lines_docs AS (
--------------------------------
-- combine it all
--------------------------------
SELECT
a.tag_ref,
a.colno,
b.line,
c.doc
FROM
ht_colno a
LEFT JOIN ht_lines_row b ON b.tag_ref = a.tag_ref
AND b.colno = a.colno
LEFT JOIN ht_docs_row c ON c.tag_ref = a.tag_ref
AND c.colno = a.colno
)
SELECT
*
FROM
ht_lines_docs
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment