Thread: Turning column into row

Turning column into row

From
"Tille, Andreas"
Date:
Hello,

I'm afraid this is a blody beginners question but I have no idea how
to perform a search sanely in the list archive.

I have a Table of certain items
  create table item (      IdItem      int,      Item        varchar(64),      ...  );

a lookuptable which defines some properties
  create table property (      IdProperty  int,      Property    varchar(64)  );

and a table which defines the different properties which are possible for
a certain item:
  create table tlkp_item_property (      IdItem     int,      IdProperty int  );

So I can easily select all the properties of a certain item in a table
where the columns contain the properties.  But I want to have an output
of the kind
   Item 1: Property 1, Property 2, Property 3, ...   Item 2: <Properties of Item 2>      ...

So I have to turn the different properties according to one item into
one field insead of one column.

How to do this sanely?

Kind regards
         Andreas.







Re: Turning column into row

From
Oliver Elphick
Date:
On Wed, 2002-05-22 at 08:35, Tille, Andreas wrote:
>
> So I have to turn the different properties according to one item into
> one field insead of one column.
>
> How to do this sanely?


Use Perl?

According to Joe Celko's book, SQL For Smarties, Sybase provides a
LIST() aggregate function that does this.  But I don't know of anything
in PostgreSQL.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "We are troubled on every side, yet not distressed; we      are perplexed, but not in despair; persecuted, but not
  forsaken; cast down, but not destroyed; Always bearing     about in the body the dying of the Lord Jesus, that
thelife also of Jesus might be made manifest in our      body."        II Corinthians 4:8-10  

Re: Turning column into row

From
"Tille, Andreas"
Date:
On Wed, 22 May 2002, Oliver Elphick wrote:

> Use Perl?
Well, I just started to write a Python script, but it showes to
make some effort to accomplish the same I did in a simple shell script
before.  I use an intermediate output using some PostgreSQL functions
and CASE statements to get a raw ASCII output which was formatted
quite nicely for this purpose by a single SELECT statement into
a proper sized table.  It would cost a fair amount of Perl/Python/Whatever
code to do the same.

I know that this is a problem which is quite hard in plain SQL but
I've thought I could do some magic using plpgsql-FUNCTIONS but I
failed to solve it.

Kind regards
        Andreas.


Re: Turning column into row

From
Gabriel Dovalo Carril
Date:
Hi,

> So I can easily select all the properties of a certain item in a table
> where the columns contain the properties.  But I want to have an output
> of the kind
> 
>     Item 1: Property 1, Property 2, Property 3, ...
>     Item 2: <Properties of Item 2>
>        ...
> 
> So I have to turn the different properties according to one item into
> one field insead of one column.
You need to build dinamically the SQL query.

First you run:

Select * from property Order by idproperty;

And with data obtained you can build a query like this:

Select item.item,  max(case when property.idproperty = 0 then property.property else
''::text end) as p0,  max(case when property.idproperty = 1 then property.property else
''::text end) as p1  
From item, property, tlkp_item_property 
Where item.iditem = tlkp_item_property.iditem and tlkp_item_property.idproperty = property.idproperty 
Group by item.item; 

*-------------------------------
I have tried it with this:

prueba=# select * from item;iditem | item
--------+-------     2 | item2     1 | item1     0 | item0
(3 rows) 

prueba=# select * from property;idproperty | property
------------+----------         0 | pro0         1 | pro1
(2 rows)    

prueba=# select * from tlkp_item_property;iditem | idproperty
--------+------------     0 |          0     1 |          1     1 |          0
(3
rows)

                                                       
 

prueba# select item.item, 
max(case when property.idproperty = 0 then property.property else
''::text end) as p0, 
max(case when property.idproperty = 1 then property.property else
''::text end) as p1  
from item, property, tlkp_item_property 
where item.iditem = tlkp_item_property.iditem and 
tlkp_item_property.idproperty = property.idproperty 
Group by item.item;
item  |  p0  |  p1
-------+------+------item0 | pro0 |item1 | pro0 | pro1
(2 rows)


Re: Turning column into row

From
"Wm. G. Urquhart"
Date:
On Wed, 22 May 2002, Tille, Andreas wrote:

> Hello,
>
> I'm afraid this is a blody beginners question but I have no idea how
> to perform a search sanely in the list archive.
>
> I have a Table of certain items
>
>    create table item (
>        IdItem      int,
>        Item        varchar(64),
>        ...
>    );
>
> a lookuptable which defines some properties
>
>    create table property (
>        IdProperty  int,
>        Property    varchar(64)
>    );
>
> and a table which defines the different properties which are possible for
> a certain item:
>
>    create table tlkp_item_property (
>        IdItem     int,
>        IdProperty int
>    );
>
> So I can easily select all the properties of a certain item in a table
> where the columns contain the properties.  But I want to have an output
> of the kind
>
>     Item 1: Property 1, Property 2, Property 3, ...
>     Item 2: <Properties of Item 2>
>        ...
>
> So I have to turn the different properties according to one item into
> one field insead of one column.
>
> How to do this sanely?
>
> Kind regards
>
>           Andreas.
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Depending on how many properties you had you could include these in the
first table and remove the other two table. Using a hash value pair or
sommit similar (Value=Desc, Value=Desc) you could then do bitwise
operations on the column to extract the information you need.

Just a thought.

-- 
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk




Re: Turning column into row

From
"Tille, Andreas"
Date:
On Wed, 22 May 2002, Wm. G. Urquhart wrote:
> Depending on how many properties you had you could include these in the
> first table and remove the other two table. Using a hash value pair or
> sommit similar (Value=Desc, Value=Desc) you could then do bitwise
> operations on the column to extract the information you need.
Well, there are not much but how to insert a new Property with this
approach.  Quite impossible.

Anyway, thanks for the thought
        Andreas.


Re: Turning column into row

From
"Tille, Andreas"
Date:
On Wed, 22 May 2002, Gabriel Dovalo Carril wrote:

> Select item.item,
>   max(case when property.idproperty = 0 then property.property else
> ''::text end) as p0,
>   max(case when property.idproperty = 1 then property.property else
> ''::text end) as p1
> >From item, property, tlkp_item_property
> Where item.iditem = tlkp_item_property.iditem and
>     tlkp_item_property.idproperty = property.idproperty
> Group by item.item;

Thanks a lot.  This helped much.  Any hints if there are online
documents available which do explain those problems?

Kind regards
          Andreas.


Re: Turning column into row

From
"Wm. G. Urquhart"
Date:
On Wed, 22 May 2002, Tille, Andreas wrote:

> On Wed, 22 May 2002, Wm. G. Urquhart wrote:
> > Depending on how many properties you had you could include these in the
> > first table and remove the other two table. Using a hash value pair or
> > sommit similar (Value=Desc, Value=Desc) you could then do bitwise
> > operations on the column to extract the information you need.
> Well, there are not much but how to insert a new Property with this
> approach.  Quite impossible.
>
> Anyway, thanks for the thought
>
>          Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Not at all if you have for example 6 properties:

Prop 1 = 1 ;
Prop 2 = 2 ;
Prop 3 = 4 ;
Prop 4 = 8 ;
Prop 5 = 16 ;
Prop 6 = 32 ;

The user will select the properties applicable to the Item; so for this
example the new item has Properties 1, 3 and 6. These property values are
added to give a total of 37. This is the value stored in the table column.

Once you have the rows you can then && the Properties column to see if a
property is set. I know this works as I've done it before in Oracle,
Oracle even supports bitwise predicates. But I'm not sure if PostgreSQL
does.

if (Item.Column && Prop1)   Do this ;
else  Property Not supported... ;
end if ;

HTH

-- 
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk




Re: Turning column into row (More...)

From
"Wm. G. Urquhart"
Date:
On Wed, 22 May 2002, Tille, Andreas wrote:

> On Wed, 22 May 2002, Wm. G. Urquhart wrote:
> > Depending on how many properties you had you could include these in the
> > first table and remove the other two table. Using a hash value pair or
> > sommit similar (Value=Desc, Value=Desc) you could then do bitwise
> > operations on the column to extract the information you need.
> Well, there are not much but how to insert a new Property with this
> approach.  Quite impossible.
>
> Anyway, thanks for the thought
>
>          Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

In fact on further investigation :

select ?, ?, ? from ? where (column & value = value) ;

Returns the rows with the appropriate bits set. So this idea will work in
PostgreSQL.

HTH.

-- 
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk




Re: Turning column into row

From
"Tille, Andreas"
Date:
On Wed, 22 May 2002, Wm. G. Urquhart wrote:

> Not at all if you have for example 6 properties:
>
> Prop 1 = 1 ;
> Prop 2 = 2 ;
> Prop 3 = 4 ;
> Prop 4 = 8 ;
> Prop 5 = 16 ;
> Prop 6 = 32 ;
You are right, I have exactly 6 properties ;-) and actually for this
kind of application it is perfectly all right to proceed this way.
I do not put all of them not in separate columns but into one
column with the concatenated string   Prop 1/Prop 3/Prop 6/
(and strip the remaining '/' afterwards).
While this solution is kind of a hack it fits my needs so far.

> The user will select the properties applicable to the Item; so for this
> example the new item has Properties 1, 3 and 6. These property values are
> added to give a total of 37. This is the value stored in the table column.
>
> Once you have the rows you can then && the Properties column to see if a
> property is set. I know this works as I've done it before in Oracle,
> Oracle even supports bitwise predicates. But I'm not sure if PostgreSQL
> does.
>
> if (Item.Column && Prop1)
>     Do this ;
> else
>    Property Not supported... ;
> end if ;
I see no reason why this should not work - but I even see no real
advantage to store a bitfield instead of just the concatenated string
in a temporary table which is removed afterwards anyway.

Thanks for the further hint
            Andreas.


Re: Turning column into row

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Oliver Elphick
> Sent: Wednesday, May 22, 2002 5:57 AM
> To: Tille, Andreas
> Cc: PostgreSQL SQL
> Subject: Re: [SQL] Turning column into row
>
>
> On Wed, 2002-05-22 at 08:35, Tille, Andreas wrote:
> >
> > So I have to turn the different properties according to one item into
> > one field insead of one column.
> >
> > How to do this sanely?
>
>
> Use Perl?
>
> According to Joe Celko's book, SQL For Smarties, Sybase provides a
> LIST() aggregate function that does this.  But I don't know of anything
> in PostgreSQL.

http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=13
9 will do as a LIST() replacement.

- J



Re: Turning column into row

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Wm. G. Urquhart
> Sent: Wednesday, May 22, 2002 8:41 AM
> To: Tille, Andreas
> Cc: PostgreSQL SQL
> Subject: Re: [SQL] Turning column into row
>
>
> On Wed, 22 May 2002, Tille, Andreas wrote:
>
> > I have a Table of certain items
> >
> >    create table item (
> >        IdItem      int,
> >        Item        varchar(64),
> >        ...
> >    );
> >
> > a lookuptable which defines some properties
> >
> >    create table property (
> >        IdProperty  int,
> >        Property    varchar(64)
> >    );
> >
> > and a table which defines the different properties which are
> possible for
> > a certain item:
> >
> >    create table tlkp_item_property (
> >        IdItem     int,
> >        IdProperty int
> >    );
> >
> > So I can easily select all the properties of a certain item in a table
> > where the columns contain the properties.  But I want to have an output
> > of the kind
> >
> >     Item 1: Property 1, Property 2, Property 3, ...
> >     Item 2: <Properties of Item 2>
> >        ...
> >
> > So I have to turn the different properties according to one item into
> > one field insead of one column.

If you can have the properties listed in one field, rather than in separate
fields, you can use a list aggregator, such as the one at:
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=13
9

SELECT Item.iditem, comma(property) FROM Item JOIN Tlkpitem_Property USING
(iditem) JOIN Property USING (idproperty);

If this won't work, let us know. There are SELECT statements that will
create the output in separate fields, they're just either slow or require
minor modifications to your tables.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant



Re: Turning column into row

From
"Wm. G. Urquhart"
Date:
On Wed, 22 May 2002, Tille, Andreas wrote:

<snip/>
> I see no reason why this should not work - but I even see no real
> advantage to store a bitfield instead of just the concatenated string
> in a temporary table which is removed afterwards anyway.
>
> Thanks for the further hint
>
>              Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

For what it's worth the string contat option in MY opinion is a quite a
clumsy solution. The advantage of using the bitfield approach is one of
compactness, elegance and speed.

Operations on integers are inherently faster than operations on strings,
regardless of the language used. I think you should give this option some
more consideration. Having said that, you are the one developing the
application and not me.

:-)
--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk




Re: Turning column into row

From
"Tille, Andreas"
Date:
On Wed, 22 May 2002, Joel Burton wrote:

> http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=13
> 9 will do as a LIST() replacement.
While beeing a great hint it has a syntactical mistake.

The correct syntax would be:

-- creat function to comma-ify a list

create function comma_aggregate(text,text) returns text as '
begin if (length($1) > 0 ) then   return $1 || '', '' || $2;   /* note the '' here !! */ else   return $2; end if;
end;
' language 'plpgsql';


A further question:  Is there any possibility to add a further flag in the
way:
  select fname, lname, comma(hobbies,SPORT_FLAG) from people join hobbies on (personid) group by personid, fname,
lname,SPORT_FLAG;
 

So that we get only those hobbies listed which have SPORT_FLAG = 1
or something else?

Kind regards
       Andreas.


Re: Turning column into row

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tille, Andreas
> Sent: Thursday, May 23, 2002 5:24 AM
> To: rmello@fslc.usu.edu
> Cc: PostgreSQL SQL
> Subject: Re: [SQL] Turning column into row
>
>
> On Wed, 22 May 2002, Joel Burton wrote:
>
> >
> http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?re
> cipe_id=13
> > 9 will do as a LIST() replacement.
> While beeing a great hint it has a syntactical mistake.
>
> The correct syntax would be:
>
> -- creat function to comma-ify a list
>
> create function comma_aggregate(text,text) returns text as '
> begin
>   if (length($1) > 0 ) then
>     return $1 || '', '' || $2;   /* note the '' here !! */
>   else
>     return $2;
>   end if;
> end;
> ' language 'plpgsql';

Yep. When you get the function back from \df+, it drops the doubling of
single-quotes.

> A further question:  Is there any possibility to add a further flag in the
> way:
>
>    select fname, lname, comma(hobbies,SPORT_FLAG) from people
> join hobbies on (personid) group by personid, fname, lname, SPORT_FLAG;
>
> So that we get only those hobbies listed which have SPORT_FLAG = 1
> or something else?

Not like that--aggregate functions can only take one argument. You could,
however, do something like:

SELECT fname, lname, comma ( SPORT_FLAG::char || hobby ) ..

and have the function examine the first character. If 0, don't add the thing
at all; if 1, drop the 1 and add as usual. I think that's way ugly and
hackish, but it would work.

A more SQL-ish way would be something like:
  SELECT fname,         lname,         comma(hobby)    FROM people    JOIN hobbies   USING (personid)   WHERE
sport_flag=1GROUPBY personid, fname, lname;
 

But that would drop everyone that has hobbies but none that are sport_flag
hobbies.

If you wanted the people with hobbies but without sport_flag hobbies, you
could UNION them in at the end.

Alternatively, you could write the whole thing differently:
  SELECT fname,         lname,         (    SELECT comma(hobby)                FROM hobbies               WHERE
sport_flag=1                AND h.personid=p1.personid         ) AS hobbies    FROM people AS p0
 

This would show all people, regardless of whether they had any hobbies or
not.

In recent versions (7.2+, IIRC), this would probably be a good deal faster.
To see people w/o sports hobbies, you could change the join to an outer
join, and kick out people with count(hobbies)=0.
  SELECT fname,         lname,         comma(hobby)    FROM people as P0,    JOIN ( SELECT * FROM hobbies WHERE
sport_flag=1) AS h0   USING (personid)GROUP BY  personid, fname, lname;
 

HTH.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant