How to transform fact table only by SQL?

[Martin Mrazek]

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

fatTable
year type val
97 a 1
97 b 2
97 c 3
98 a 4
98 b 5
98 c 6
....

year type_a type_b type_c
97 1 2 3
98 4 5 6
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

[David Portas]

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

--
David Portas
SQL Server MVP
--

[Simon Hayes]

mr****@compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dc**************************@posting.google. com>...

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

fatTable
year type val
97 a 1
97 b 2
97 c 3
98 a 4
98 b 5
98 c 6
...

year type_a type_b type_c
97 1 2 3
98 4 5 6
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/A...608/15608.html

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

Simon