I want to create a table that has multiple rows of the same attribute with a different column

Question

Sorry I should have been more specific with my question:

This is my current issue

I have a table that has this:

Customer_ID        H2_1      H2_2      H2_3     H2_4      H2_5
123                 7         3         13       5         2
456                 7         3         13       5         2
789                 7         3         13       5         2

and I need it to look like this:

Customer_ID         H2_Code
123                    7
123                    3
123                   13
123                    5
123                    2
456                    7
456                    3
456                   13
456                    5
456                    2
789                    7
789                    3
789                   13
789                    5

Are you *creating* a table with these two columns, as the question title states? Or are you *querying* one or more other tables to get these results? It's not really clear what the issue is here. What specific problem have you encountered? — David, Jan 09 '19 at 13:41
@Cid Creating tables does not have any thing to do with rows. — TiyebM, Jan 09 '19 at 13:46

Yogesh Sharma · Answer 1 · 2019-01-09T14:04:51.280

1

You need UNION ALL to combine all columns :

SELECT Customer_ID, H2_1 AS H2_Code
FROM table t
UNION ALL
SELECT Customer_ID, H2_2
FROM table t
UNION ALL
SELECT Customer_ID, H2_3
FROM table t 
UNION ALL
SELECT Customer_ID, H2_4
FROM table t 
UNION ALL
SELECT Customer_ID, H2_5
FROM table t

edited Jan 09 '19 at 14:04

answered Jan 09 '19 at 13:39

Yogesh Sharma

49,870
5
26
52

@Ciccotelli412. . . This is not right place to comment on this instead you edit the question. – Yogesh Sharma Jan 09 '19 at 13:49
In what sense did the 'old' version not support cross join !?! – Strawberry Jan 09 '19 at 14:02

score 1 · Answer 2 · answered Jan 09 '19 at 13:51

You seem to want union all:

select Customer_ID, H2_1 as h2_code from t
union all
select Customer_ID, H2_2 as h2_code from t
union all
select Customer_ID, H2_3 as h2_code from t
union all
select Customer_ID, H2_4 as h2_code from t
union all
select Customer_ID, H2_5 as h2_code from t;

Note that union all requires scanning the table 5 times. For small or medium sized tables, that should not be an issue. For larger tables, there are slightly more complicated mechanisms that would only scan the table once.

How would you do this in 1 scan? Using a cursor? – Cid Jan 09 '19 at 13:59 — Cid, Jan 09 '19 at 13:59

score 0 · Answer 3 · answered Jan 09 '19 at 13:54

You might want to union the differents queries that fetch each H2_something, then order by customer_id

Schema (MySQL v5.7)

CREATE TABLE tableName (
  `Customer_ID` INTEGER,
  `H2_1` INTEGER,
  `H2_2` INTEGER,
  `H2_3` INTEGER,
  `H2_4` INTEGER,
  `H2_5` INTEGER
);

INSERT INTO tableName
  (`Customer_ID`, `H2_1`, `H2_2`, `H2_3`, `H2_4`, `H2_5`)
VALUES
  ('123', '7', '3', '13', '5', '2'),
  ('456', '7', '3', '13', '5', '2'),
  ('789', '7', '3', '13', '5', '2');

Query #1

SELECT customer_Id, H2_1 AS H2_Code FROM tableName
UNION ALL
SELECT customer_Id, H2_2 FROM tableName
UNION ALL
SELECT customer_Id, H2_3 FROM tableName
UNION ALL
SELECT customer_Id, H2_4 FROM tableName
UNION ALL
SELECT customer_Id, H2_5 FROM tableName
ORDER BY customer_Id;

| customer_Id | H2_Code |
| ----------- | ------- |
| 123         | 3       |
| 123         | 13      |
| 123         | 5       |
| 123         | 2       |
| 123         | 7       |
| 456         | 3       |
| 456         | 13      |
| 456         | 5       |
| 456         | 2       |
| 456         | 7       |
| 789         | 7       |
| 789         | 3       |
| 789         | 13      |
| 789         | 5       |
| 789         | 2       |

View on DB Fiddle

score 0 · Answer 4 · answered Jan 09 '19 at 14:01

Note that a normalised schema would typically look something like this:

Customer_ID    H2    val    
123             1      7     
123             2      3     
123             3     13     
123             4      5     
123             5      2
456             1      7     
456             2      3     
456             3     13     
456             4      5     
456             5      2
789             1      7     
789             2      3     
789             3     13     
789             4      5     
789             5      2

I want to create a table that has multiple rows of the same attribute with a different column

4 Answers4