Re: sql - denormalising ?

From: Jim Wolfe <jim_wolfe_at_sra.com>
Date: Wed, 21 Mar 2001 14:47:12 GMT
Message-ID: <Q93u6.18189$227.1642751@newsread2.prod.itd.earthlink.net>

Carston,
This is a somewhat common problem. Basically, you are trying to pivot a table, transforming columns into rows. One problem is that you might not know how many columns you will need in your result. It's easier to perform this type of retrieval in PL./SQL. However, if you must use a single SQL statement, here is an approach. You need to create a query in which the target table is instantiated for each attribute column you want in your result. Try something like this:

select distinct t1.id, t1.attribute, t2.attribute from <table> t1, <table> t2
where t1.id = t2.id
and t1.attribute > t2.attribute;

Using the '>' operator rather than inequals prevents getting 'duplicate' rows of the form:
1 yellow rough
1 rough yellow

You can build on the above query depending on the maximum number of rows your expect, but then you have to allow for nulls. Lets say that you had another tuple: '2, black'. You could expand the above to allow three and four column reports like this:

select distinct t1.id, t1.attribute, t2.attribute, null from <table> t1, <table> t2, <table> t3
where t1.id = t2.id
and t1.id = t3.id
and count(t3.attribute) = 2
and t1.attribute > t2.attribute
group by t1.id, t1.attribute, t2.attribute union
select distinct t1.id, t1.attribute, t2.attribute, t3.attribute from <table> t1, <table> t2, <table> t3, <table> t4 where t1.id = t2.id

and     t1.id = t3.id
and     t1.id = t4.id

and t1.attribute > t2.attribute
and t2.attribute > t3.attribute
and count(t4.attribute) = 3
group by t1.id, t1.attribute, t2.attribute, t3.attribute;

(I haven't tried this, but it should be close. You may have to include null in the first simple query group by clause and you also may have to include the count terms in the select clauses.)

As you can see, the query will get fairly cumbersome as your expected number of result columns grow. But it should work. Jim Wolfe

"Carsten Jacobs" <carsten.jacobs_at_web.de> wrote in message news:99acun$e8p0$1_at_ID-21443.news.dfncis.de...
> Hi,
>
> I have a table with multiple entries (2) for one entity like
>
> id attribute
> ---------------
> 1 yellow
> 1 rough
> 2 blue
> 2 smooth
>
> How can I get a result like
> 1 yellow rough
> 2 blue smooth
>
> Do I need a procedure for that or can I do it with a single sql statement?
>
> Many thanx for help
>
> Carsten
>
>
>
>
Received on Wed Mar 21 2001 - 08:47:12 CST