Transform dates from table into columns in MS Access query

Question

I have a table like below:

type_id	date	order
20	2021-06-23	123
20	2021-06-23	217
35	2021-06-23	121
35	2021-06-24	128
20	2021-06-24	55
35	2021-06-25	77
20	2021-06-26	72
20	2021-06-26	71

and want to create a query only where type_id=20 likie this:

2021-06-23	2021-06-24	2021-06-25	2021-06-25
123	55		72
217			71

is it possible to do this with sql without vba?
if vba needed do I need to create a extra table and every time add/delete a new columns ?

Thnak You for any idea

I think you might be looking into something similar to this here https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Rishi, Jun 20 '21 at 16:51
Thanks, I thought I searched all helpful topics but didn't reach this ... — Kert Lopper, Jun 20 '21 at 16:55
Keep in mind there is is a limit of 255 columns. Review http://allenbrowne.com/ser-67.html — June7, Jun 20 '21 at 17:12
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — June7, Jun 20 '21 at 17:15

score 0 · Accepted Answer · answered Jun 20 '21 at 18:03

You can use conditional aggregation. But this is a pain in MS Access because you need a sequential value. You can calculate one:

select max(iif(date = "2021-06-23", order, null)) as val_2021_06_23,
       max(iif(date = "2021-06-24", order, null)) as val_2021_06_24,
       max(iif(date = "2021-06-25", order, null)) as val_2021_06_25,
       max(iif(date = "2021-06-26", order, null)) as val_2021_06_26       
from (select t.*,
             (select count(*)
              from t as t2
              where t2.type_id = t.type_id and t2.date = t.date and t2.order <= t.order
             ) as seqnum
      from t
      where type_id = 20
     ) t
group by seqnum;

score 0 · Answer 2 · answered Jun 20 '21 at 18:57

Thank You, it works ! (only one change in the code needed "2021-06-23" ----> #2021-06-23#

In the meantime I found a other solution but this need add a new field into the table. The field is a numeric field which contain sequence numer for each day from 1 to n. In my project it's even helpful because in this case I can control order by in columns

here is the code. maybe helpful for someone in future

TRANSFORM
First ([tabela1].[order])
SELECT  [tabela1].[sequence]
FROM  [tabela1]
WHERE [tabela1].[type_id] = 20
GROUP BY [tabela1].[sequence]
PIVOT  [tabela1].[date]

Transform dates from table into columns in MS Access query

2 Answers2