Transpose Data

[TopJack]

Hello everyone,

I have a table "TREND" that has data like this :-

WEEK LOCN COST
=== ==== =====
45........A......10
46........A......12
47........A......15
45........B......18
46........B......16
47........B......11
45........C......13
46........C......14
47........C......10

How do I transpose my data to look like this (where the value is COST) :-

WEEK A B C
=== == == ==
45....10..18..13
46....12..16..14
47....15..11..10

Also I do not always know the column names in advance, ie the column names could change next week to A,B, C, D and E.

I'm thinking along the lines of maybe :-

SELECT WEEK AS "WEEK"
     , SUM(DECODE(LOCN, 'A', COST, 0)) AS A
     , SUM(DECODE(LOCN, 'B', COST, 0)) AS B
     , SUM(DECODE(LOCN, 'C', COST, 0)) AS C     
  FROM TREND
 GROUP BY WEEK

But this means I must know the column names in advance. How can I dynamically create the column titles and values ?

Thanks,
Jack.

 

[r937]

dynamically? you cannot

f.y.i. for future questions you might have better luck in the oracle forum (i'm guessing you're using oracle because of the non-standard DECODE function)

this is the ANSI SQL forum

r937.com | rudy.ca

 

[TopJack]

Sorry, I will try there instead.

Thanks.