HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Need to export table into 2 columns, one repeating, one with rest stacked

Submitted by: @import:stackexchange-dba··
0
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):

|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.