Thread: Turning column into row
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.
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
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.
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)
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
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.
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.
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
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
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.
> -----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
> -----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
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
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.
> -----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