Re: create columns from rows in a query

Alberto wrote:

> I have a table like
>
> Date Code N
> 01-01-2002 F1 4
> 01-01-2002 F2 7
> 01-01-2002 F3 8
> 01-02-2002 F1 1
> 01-02-2002 F2 3
> 01-02-2002 F3 9
>
> I need to write a query that create this structure:
>
> Date F1_N F2_N F3_N
> 01-01-2002 4 7 8
> 01-02-2002 1 3 9
>
> That is, I need to put the information in column instead that rows.
>
> I do it whit this query:
>
> select date,
> sum(decode(code,'F1',N,null)) F1_N,
> sum(decode(code,'F2',N,null)) F2_N,
> sum(decode(code,'F3',N,null)) F3_N
> from table
> group by date
>
> Question is: can I optain this result using some SQL instruction I
> don't know?, without having to DECODE->SUM->Group by ?

You can do it with CASE and TRANSLATE but the circumstances under which they would work will be substantially different. I'd say stick with DECODE unless you have a compelling reason to need the other functionality.

Daniel Morgan Received on Thu Feb 13 2003 - 08:41:31 CST