Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer 1

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.