SQL - query moving column to a row

[Kamil]

Hi.
I have a table with fields:
KEY;a;b

I would like to create a query which will move the data from field "b"
to next row:
KEY;a
KEY;b.

Example:
ID;phone1;phone2
=>
ID;phone1
ID;phone2

Is it possible?
Best regards,
Kamil

 

[Allen Browne]

Use a UNION query, like this:

SELECT Table1.[KEY] AS TheKey,
[A] AS ThePhone
"Phone1" AS TheSource
FROM Table1
WHERE [A] Is Not Null
UNION ALL
SELECT Table1.[KEY] AS TheKey,
AS ThePhone
"Phone2" AS TheSource
FROM Table1
WHERE Is Not Null
ORDER BY TheKey;

Results will be read-only.

TheSource will be meaningful only if you have different types of numbers in
your existing columns.

Use UNION rather than UNION ALL if you want Access to de-duplicate. (Takes
longer to run.)

I aliased your KEY field, as KEY is a reserved word. It's probably not your
real field name, but here's a list of names to avoid when designing tables:
http://allenbrowne.com/AppIssueBadWord.html

Ultimately, you might consider creating a related table to hold the phone
numbers for your KEYs, so they are actually stored in the way you requested
(which is correctly normalized.)