SQL Query to view data split out by rows instead of columns

[jolofomo]

Hello,

I have a large table which contains details of contacts with different companies. However it's not in a normalised state. So instead of having one row to one contact- I have one row to each company and then multiple contacts listed. I need to know how to write some sql to convert the table to the new layout so that I can normalise the database.

ID	Email	Name	Email1	Name1	Email2	Name2	DateIn
1	a@a.com	a	b@a.com	b	c@a.com	c	24/06/2022

So I want the above to become-

ID	Email	Name
1	a@a.com	a
2	b@a.com	b
3	c@a.com	c

How can I convert this sequel to achieve this outcome?-
SELECT Table1.[ID], Table1.[Email], Table1.[Name], Table1.[Email1], Table1.[Name1], Table1.[Email2], Table1.[Name2], Table1.[DateIn]
FROM Table1;

The ID is an autonumber.

[Welshgasman]

Use a union query.
One query line for each repeating field.
Hoewver you are going to need to relink any relationships, as b is now 2, not 1 anymore?
You could keep that field as OldID?

[June7]

SELECT [ID] AS OldID, [Email], [Name] AS ContactName, [DateIn] FROM Table1
UNION SELECT [ID], [Email1], [Name1], [DateIn] FROM Table1
UNION SELECT [ID], [Email2], [Name2], [DateIn] FROM Table1;

There is no query designer for UNION, must type or copy/paste in SQLView.
There is a limit of 50 SELECT lines.
First SELECT line defines data types and field names.

[jolofomo]

excellent. Thanks all. Sorted now.