Pivot a table to repeat the first column for each following column

Question

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

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

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!

bummi · Accepted Answer · 2012-12-25 19:14:58Z

up vote 2 down vote accepted

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

answered Dec 25 '12 at 19:14

bummi
5111210

	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

1 Answer