How to transform fact table only by SQL?

Hi,
this is easy with OLAP tools, but I need to do it just with MS-SQL
server:

fatTable
yeartypeval
97a1
97b2
97c3
98a4
98b5
98c6
...

yeartype_atype_btype_c
97123
98456
99...

The problem is number of different types - not just 3 like a,b,c but
more than 100, so I don't want to do it manually like

select
year, a.val, b.val, c.val
from
(select year, val from factTable where type='a') a
full join (select year, val from factTable where type='b') b
on a.year = b.year
full join (select year, val from factTable where type='c') c
on a.year = c.year

is it possible somehow with DTS or otherwise? I just need to present
the data in spreadsheet in more readable form, but I cannot find any
way how to export the result from MS-SQLserverOLAPservices to Excel...

Martinhttp://www.winnetmag.com/SQLServer/...5608/15608.html
http://www.sqlteam.com/item.asp?ItemID=2955

--
David Portas
SQL Server MVP
--|||mrazek@.compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dcd39e84.0405271815.13dd8e97@.posting.google.com>...
> Hi,
> this is easy with OLAP tools, but I need to do it just with MS-SQL
> server:
> fatTable
> yeartypeval
> 97a1
> 97b2
> 97c3
> 98a4
> 98b5
> 98c6
> ...
> yeartype_atype_btype_c
> 97123
> 98456
> 99...
> The problem is number of different types - not just 3 like a,b,c but
> more than 100, so I don't want to do it manually like
> select
> year, a.val, b.val, c.val
> from
> (select year, val from factTable where type='a') a
> full join (select year, val from factTable where type='b') b
> on a.year = b.year
> full join (select year, val from factTable where type='c') c
> on a.year = c.year
> is it possible somehow with DTS or otherwise? I just need to present
> the data in spreadsheet in more readable form, but I cannot find any
> way how to export the result from MS-SQLserverOLAPservices to Excel...
> Martin

It sounds like you're looking for a dynamic crosstab query:

http://www.aspfaq.com/show.asp?id=2462
http://www.winnetmag.com/SQLServer/...5608/15608.html

You might find it easier to build the query string in a client
program, rather than in pure TSQL.

Simon