MySQL Forums
Forum List  »  Views

variable rows to columns in a dynamic view
Posted by: Miguel Martin
Date: June 16, 2011 03:34AM

Hi,

I have seen this topic about spinning rows to columns http://forums.mysql.com/read.php?20,75357. I would like to go a little bit further:

I have a table of samples:

sId-----sNmbr-----date
--------------------------
1-----M1-----2011-02-10
2-----M2-----2011-01-19
3-----M2-----2011-01-20
4-----M3-----2010-08-18

and a table of properties for those samples:

pId-----sId-----property-----value
----------------------------------
1-----1-----color -----gray
2-----1-----factor-----89
3-----2-----image -----001.jpg
4-----2-----factor-----92
5-----2-----color -----gray
6-----3-----color -----blue
7-----4-----factor-----92
8-----4-----image -----002.jpg

The users can create any kind of property with any kind of value, so properties are not static.

Let's say the user wants to see sNmbr M2.
He would see something like:

sId-----sNmbr-----date-----color-----factor-----image
-------------------------------------------------------------
2-----M2-----2011-02-10-----gray-----92-----001.jpg
3-----M2-----2011-01-19-----blue-----<null>-----<null>

Let's say the user wants to see samples with color=gray.
He would see something like this:

sId-----sNmbr-----date-----color-----factor-----image
-------------------------------------------------------------
1-----M1-----2011-01-19-----gray-----89-----<null>
2-----M2-----2011-01-20-----gray-----92-----001.jpg


How can I do this?

Options: ReplyQuote


Subject
Views
Written By
Posted
variable rows to columns in a dynamic view
7496
June 16, 2011 03:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.