Hi Des,

If both relational SQL Server 2005 and AS 2005 are an option, then maybe
you can create a named query for the fact table in the AS 2005 Data
Source View, which uses the new TSQL PIVOT command to transpose the
data:

http://msdn2.microsoft.com/en-us/library/ms177410.aspx

You can use the PIVOT and UNPIVOT relational operators to manipulate a
table-valued expression into another table. PIVOT rotates a table-valued
expression by turning the unique values from one column in the
expression into multiple columns in the output, and performs
aggregations where necessary on any remaining column values that are
desired in the final output.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak


Thanks for the response..

Unfortunately I dont have any control over the box, and am bound to using
SQL2000 and AS2000.


Regards
Des Norton

You can still do Pivots in SQL 2k, it just involves a lot more code than
the PIVOT command.

One such example using dynamic SQL can be found here
http://www.sqlteam.com/item.asp?ItemID=2955, in fact this code is more
flexible than the PIVOT command as it will dynamically work out the
columns, PIVOT in SQL2k5 requires a static list of values.

I'm sure a search on google could turn up many more. I know there are
methods using case statements which require a static column list, but do
not use dynamic sql.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell



In article <ObpATIz7FHA.2676 (AT) TK2MSFTNGP15 (DOT) phx.gbl>,
desREMOVEnortonUPPERCASE (AT) gmail (DOT) com says...

Thanks Darren

With some mods for specific requirements, workes like a charm.

Regards
Des Norton


"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote