Re: A tricky SQL statement

Anders Hattestad wrote:
>
> I have a table that looks like this:
>
> sident mident dato m00
> M201 B1 07-16-95 2983
> M201 B2 07-16-95 2996

 <snip>
> And I would like to extract it on this form:
>
> Sident B1 B2 L1 RF
> M201 2983 2996 2956 3
> M251 2535 88
> M253 2658
>
> Have any of you a solution on how this sql statement should look like?
>
> Thanks in advances
> Anders

If you know in advance the values for mident, you can do the following:

select

	sident,
	decode(mident,'B1',m00,NULL) "B1",
	decode(mident,'B2',m00,NULL) "B2",
	decode(mident,'L1',m00,NULL) "L1",
	decode(mident,'RF',m00,NULL) "RF"
from	my_table

However, this will not put all of M201 on one line. The problem is that you are trying to combine multiple rows into one row for output. The only way I know to do this is to use the above query and then post-process the output with awk or perl or use PL/SQL or Pro*C to process the rows as they are selected out of the db. I think some tools may be available which do this for you (crosstab query??) but I don't think SQL can do it.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.