patternsqlMinor
Need to export table into 2 columns, one repeating, one with rest stacked
Viewed 0 times
restcolumnsneedwithintorepeatingoneexportstackedtable
Problem
Have a table that has one column I want to duplicate, corresponding to several columns I want to stack on top of each other. I have a table like this (foo):
And I want to create a table like this (foo2):
This is as far as I got, this only stacks into one column:
|col1 |col2 |col3 |
| 1 | A | F |
| 2 | B | G |
| 3 | C | H |
| 4 | D | I |
| 5 | E | J |And I want to create a table like this (foo2):
|col_a |col_b |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 1 | F |
| 2 | G |
| 3 | H |
| 4 | I |
| 5 | J |This is as far as I got, this only stacks into one column:
DROP TABLE IF EXISTS foo2 CASCADE;
select col_a INTO foo2 from (
select col1 as col_a from foo
union all
select col2 as col_a from foo
union all
select col3 as col_a from foo
) as myview;Solution
select col_a, col_b INTO foo2 from (
select col1 as col_a, col2 as col_b from chandlerV2
union all
select col1 as col_a, col3 as col_B from chandlerV2
) as myview;Each part of subquery myview select main column (col1) and one of two secondary column (col2 or col3 respectively). Both part united by UNION ALL clause.
As variant you can CREATE TABLE and after that run INSERT INTO ... SELECT ...
Code Snippets
select col_a, col_b INTO foo2 from (
select col1 as col_a, col2 as col_b from chandlerV2
union all
select col1 as col_a, col3 as col_B from chandlerV2
) as myview;Context
StackExchange Database Administrators Q#162688, answer score: 2
Revisions (0)
No revisions yet.