# 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

-
Thanks for the info! Looking from the examples this looks very promising :) My only concern is with the "generic" query because this would become a really big one depending on the amount of columns. I have read something about a "Transform" function which would be similar to Pivot, do you happen to have knowledge of this for Access? –  Dirk R. Dec 25 '12 at 19:59
You can find examples here ewbi.blogs.com/develops/2005/01/access_sql_tran.html msdn.microsoft.com/en-us/library/office/… but if I got you right you are searching for the opposite. –  bummi Dec 25 '12 at 20:26
That indeed seems to be the opposite of what I'm trying to do. ( Same for Crosstab ) I should be able to do what I need to do with the union all and hope that they move the data to a SQL DB soon :) –  Dirk R. Dec 25 '12 at 22:06