0

I have a dataset in the below format:

 ID   A  B  C  D 
 1    x  x  x  x
 2    x  x  x  x 
 3    x  x  x  x
 4    x  x  x  x

I want to convert it into a format where ID is duplicated with the values of the rows, e.g. to look something like below:

ID    Letter    Value
1       A         x
1       B         x
1       C         x
1       D         x
2       A         x
2       B         x 
2       C         x
2       D         X

and so on....

Many thanks in advance.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
mIT
  • 48
  • 6

2 Answers2

0

useful link:

Columns to Rows in MS Access

piscu
  • 84
  • 7
0

The UNION statement will allow you to do that:

SELECT * FROM (
    SELECT [ID], "A" As [Letter], [A] As [Value] FROM [yourTable]
    UNION ALL
    SELECT [ID], "B" As [Letter], [B] As [Value] FROM [yourTable]
    UNION ALL
    SELECT [ID], "C" As [Letter], [C] As [Value] FROM [yourTable]
    UNION ALL
    SELECT [ID], "D" As [Letter], [D] As [Value] FROM [yourTable]
) ORDER BY [ID], [Letter]
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Thank you very much, the union seems to be the right thing to do, however, this does not get the columns titles (A, B, C, and D) as values in column Letter but rather have the value itself for column Letter as well as column Value, any ideas why that might be the case? – mIT Nov 15 '17 at 10:04
  • @Mahmoud Are you sure you put the `"` marks around `A` (etc) in the bit saying `"A" As [Letter]`? – YowE3K Nov 15 '17 at 18:14