Pivot a table to repeat the first column for each following column i'm not sure how to explain so I'll try and write out an example.

I'm currently using an access database with a table in the following format


1 A B C D
2 A B C D


I'm trying to perform a query that will provide me with an output in the following format:


1 A 
1 B
1 C
1 D
2 A
2 B 
....


Currently this will be performed on a Access Db but later on it will need to be converted to a SQL Server query.

I'm not that fluent in SQL so any help would be welcome :)

Thanks!
This will work wit Access and SQL-Server

SELECT ID, A AS Fieldx
FROM tbl 
UNION ALL
SELECT ID, B
FROM tbl 
UNION ALL
SELECT ID, C
FROM tbl
UNION ALL
SELECT ID, D
FROM tbl;


specific for SQL-Server:

SELECT ID, FieldX FROM 
   (SELECT ID,A,B,C,D   FROM tbl) p
UNPIVOT
   (FieldX for dummyID in (A,B,C,D)) as dummyField 


Additional information can be found here