how to generate SQL SELECT pivot table string

[mensanator at aol.com]

McBooCzech wrote:
> Hallo all,
>
> I am trying to generate SQL SELECT command which will return pivot
> table. The number of column in the pivot table depends on the data
> stored in the database. It means I do not know in advance how many
> columns the pivot table will have.
>
> For example I will test the database as following:
> SELECT DISTINCT T1.YEAR FROM T1
>
> The SELECT command will return:
> 2002
> 2003
> 2004
> 2005
>
> So I would like to construct following select:
>
> select T1.WEEK,
> SUM (case T1.YEAR when '2002' then T1.PRICE else 0 END) Y_02,
> SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
> SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
> SUM (case T1.YEAR when '2005' then T1.PRICE else 0 END) Y_05
> from T1
> group by T1.week
>
> which will return pivot table with 5 columns:
> WEEK, Y_02, Y_03, Y_04, Y_05,
>
> but if the command "SELECT DISTINCT T1.YEAR FROM T1" returns:
> 2003
> 2004
>
> I have to construct only following string:
>
> select T1.WEEK,
> SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
> SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
> from T1
> group by T1.week
>
> which will return pivot table with 3 columns:
> WEEK, Y_03, Y_04
>
> Can anyone help and give me a hand or just direct me, how to write a
> code which will generate SELECT string depending on the data stored
in
> the database as I described?
>
> Thanks
>
> Petr McBooCzech

In MS-Access, the appropriate SQL statement is

TRANSFORM Sum(T1.price) AS SumOfprice
SELECT T1.week
FROM T1
GROUP BY T1.week
PIVOT T1.year;

Note the keywords TRANSFORM and PIVOT. These change an
ordinary SELECT query into what Access calls a CROSSTAB
query. In such a query, the number of columns is created
automatically (subject to the limitation of a maximum of
256 columns).

For example, if your T1 table contains:

year	week	price
2002	1	123
2002	10	456
2002	20	254
2002	30	253
2002	40	325
2002	50	111
2003	1	254
2003	10	256
2003	20	854
2003	30	125
2003	40	845
2003	50	562
2004	1	425
2004	10	123
2004	20	212
2004	30	555
2004	40	412
2004	50	852


The query shown will output:

week	2002	2003	2004
1	123	254	425
10	456	256	123
20	254	854	212
30	253	125	555
40	325	845	412
50	111	562	852


Now, if you add another year's worth of data,

year	week	price
2005	1	666
2005	10	555
2005	20	444
2005	30	333
2005	40	222
2005	50	111

the query (without any modiification) will now output

week	2002	2003	2004	2005
1	123	254	425	666
10	456	256	123	555
20	254	854	212	444
30	253	125	555	333
40	325	845	412	222
50	111	562	852	111


If you had multiple records for each year/week you would, of course,
see the aggregate result (in this case, Sum). Watch what happens
when I duplicate the 2005 records:

week	2002	2003	2004	2005
1	123	254	425	1332
10	456	256	123	1110
20	254	854	212	888
30	253	125	555	666
40	325	845	412	444
50	111	562	852	222