crosstab without aggregate function

[kunai]

Hi,

I'm wondering if it's possible to pivot a table without having to use aggregate functions?

I'm using MS SQL 2000. Basically, i want to have my table normalized but use a query to pivot it.

e.g.:

Table in database:

name A B
---- ---- ----
X 1 1
Y 1 0
Z 0 0

I want to use a query to get the data as:

X Y Z ...
A 1 1 0 ...
B 1 0 0 ...

I can only find examples of such queries using an aggregate function like SUM which ends up adding up the 1's in my table.

Need it to be independent of the number of columns/rows too.

Any suggestions?

Thanks

 

[NoCoolHandle]

SQL 2005 has a "crosstab" clause that will do that

Free beta

 

[vongrunt]

This is transposition (flipping over diagonal axis), not pivoting.

The best all-around way to do that would be two routines:

- one for unpivoting to get normalized data (y-category, x-category, pivot value)
- another for pivoting, called with swapped categories

Both routines are highly spreadsheet-like, so here comes simple question: why?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kunai]

Yeah, i know SQL2005 has such a function. But I don't have it and it still uses aggregate functions.

As for why, vongrunt, I find adding and updating data in rows would be easier than having tons of columns in my table.

i.e. Instead of having a table with 100 columns, it's easier to view/add/update if it's in rows and "transpose" it only when retrieving the data.

Maybe it just makes sense in my head