sql - Access 2013: Pivot multiple columns -

i have strange access database table need pivot before using tableau. i'm using access 2013. data totally fake structure right.

there 200 files, 1500 runs each. have 50 years of metrics. have 10 metrics per file/run.

+--------------------------------------------------------+ | datatable                                              | +-------------+--------------+------+------+------+------+ | fileidrunid |    metric    | 1999 | 2000 | 2001 | 2002 | +-------------+--------------+------+------+------+------+ | 00000100001 | breakfast    | 45   | 47   | 48   | 49   | | 00000100001 | lunch        | 27   | 37   | 50   | 99   | | 00000100002 | breakfast    | 45   | 47   | 48   | 49   | | 00000100002 | lunch        | 27   | 37   | 50   | 99   | | 00000200001 | breakfast    | 45   | 47   | 48   | 49   | | 00000200001 | lunch        | 27   | 37   | 50   | 99   | | 00000200002 | breakfast    | 45   | 47   | 48   | 49   | | 00000200002 | lunch        | 27   | 37   | 50   | 99   | +-------------+--------------+------+------+------+------+ 

i pivot , have metrics columns , years individual rows.

+------------------------------------------------+ | datatableview                                  | +-------------+--------------+-----------+-------+ | fileidrunid |    year      | breakfast | lunch | +-------------+--------------+-----------+-------+ | 00000100001 | 1999         | 45        | 47    | | 00000100001 | 2000         | 27        | 37    |  | 00000100002 | 1999         | 45        | 47    |  | 00000100002 | 2000         | 27        | 37    | | 00000200001 | 1999         | 45        | 47    |  | 00000200001 | 2000         | 27        | 37    |  | 00000200002 | 1999         | 45        | 47    |  | 00000200002 | 2000         | 27        | 37    |  +-------------+--------------+-----------+-------+ 

i managed pivot 1 year.

transform first(datatable.[1999]) select fileidrunid, '1999' year datatable group fileidrunid pivot datatable.metric 

i think next step union each year manually. when try next year though error.

transform first(datatable.[1999]) select fileidrunid, '1999' year datatable group fileidrunid pivot datatable.metric union transform first(datatable.[2000]) select fileidrunid, '2000' year datatable group fileidrunid pivot datatable.metric 

syntax error (missing operator) in query expression 'datatable.metric union transform first(datatable.[2000]) select fileid, runid, '2000' year datatable group fileidrunid pivot datatable.metric'

i'm hoping here has better idea :).

transform can only occur @ start of sql statement.

first unpivot years , pivot metric. now, ms access apparently has limits when comes complex queries (many unions), best pass via intermediate table:

select  fileidrunid, metric, year, value    (             select      fileidrunid, metric, 1999 year, [1999] value                    datatable             union             select      fileidrunid, metric, 2000 year, [2000] value                    datatable             union             select      fileidrunid, metric, 2001 year, [2001] value                    datatable             union             select      fileidrunid, metric, 2002 year, [2002] value                    datatable         ) mytemptable; 

i have union-ed 4 years, should check how far can go before ms access refuses statement being complex. then, add more years, same number of unions:

insert mytemptable (fileidrunid, metric, year, value) select  fileidrunid, metric, year, value    (             select      fileidrunid, metric, 2003 year, [2003] value                    datatable             union             select      fileidrunid, metric, 2004 year, [2004] value                    datatable             union             select      fileidrunid, metric, 2005 year, [2005] value                    datatable             union             select      fileidrunid, metric, 2006 year, [2006] value                    datatable         ); 

etc... this:

transform first(value) select   fileidrunid,          year     mytemptable group fileidrunid,          year pivot    metric 

and drop temporary table , compress database original size back. if possible, redesign database use unpivoted structure in temporary table.