RE: Inline View

Sure, but I can't know what those are.  I'm just trying to answer the
question as posed.  Obviously, the resulting literal solution could be
horrendous:  Suppose the table has thousands of columns?

The other choice is to maintain a second table in addition to the first
consisting of triples of values, where the term "triple" is meant
loosely.  In addition to a value column, the triples table would have a
tag indicating the column, and a row number.  Because values might be of
different types, the "value column" might actually need to be a data
type indicator, and then a series of columns each of the different
possible types, all of which are NULLable.

The idea is that if a row is written to the master table, "triples"
corresponding to each field in that row are put into the triples table.
The triples table can then be accessed by column tag or row number.
Assembling results into the presentation format does require arbitrary
amounts of intermediate storage, and that's a drawback.

But, like you, if I were doing this, I'd quickly want to know and
understand why.  Sometimes, of course, a hack like this could be merely
an expeditious stopgap to get someone through the next reporting period
until a permanent solution can be devised.

 - jtg


On Fri, 20 Oct 2006 09:19:22 +0930, "Bonnett, John"
<[email protected]> said:
> Yes, I agree you can do it if you really want, but I would be asking
> myself if I am going about this the right way. Good solutions are
> usually not that hard. If you need to do a lot of programming
> gymnastics, it is usually an indication that there is a better way of
> looking at the problem, perhaps involving changing some assumptions
> you made earlier.
>
> John
>
> -----Original Message----- From: [email protected]
> [mailto:[email protected]] Sent: Friday, 20 October 2006 9:11
> AM To: [email protected]
> Cc: Bonnett, John; [email protected] Subject: RE: Inline View
>
> Melissa,
>
> Well, it can be done, but it involves building a MySQL table on the
> fly to do it, perhaps a temporary one. A script in, say, PHP or PERL
> can generate an arbitrary number of columns with stylized names, with
> one perhaps being a VARCHAR bearing the name of the column of an
> associated row, and then the columns can be put into successive rows.
>
> Then, just return the table as you like it.
>
> If multiple users are going to be doing this, then temporary tables
> are almost essential.  Otherwise you'll need to write auxiliary stuff
> to generate non-interfering table names, a central serializer for
> allocating them, and then something for cleaning them up afterwards.
>
>  -jtg
>
> On Fri, 20 Oct 2006 08:59:19 +0930, "Bonnett, John"
> <[email protected]> said:
> > If I understand you correctly you want the the fields to be in the
> > rows and the records to be in the columns not the other way around
> > as it usually is. I don't think you can get MySQL to return data
> > like that directly. You can of course arrange the data returned
> > yourself in
>
> > any way you like by populating a grid or spreadsheet that way.
> >
> > I also tried to figure out what your query is trying to do. If I
> > understand it correctly, I would write it like this and avoid the
> > subqueries.
> >
> > SELECT DISTINCT i1.reportid, i2.ivalue as IPAddress, i3.ivalue as
> > Computer FROM item AS i1, item AS i2, item AS i3 WHERE i2.ifield
> > LIKE '%IP Address%' AND i1.ivalue = i2.ivalue AND i1.ifield =
> > i2.ifield AND
>
> > i3.ifield = 'Computer Name' AND i1.ivalue = i3.ivalue AND i1.ifield
> > = i3.ifield AND i1.reportid = 1 ORDER BY 1
> >
> > This looks a bit odd. You don't really need i1.reportid in the
> > selected columns because the WHERE clause means that it is always
> > 1 anyway. The ORDER BY clause does nothing because it is only
> > sorting on
>
> > a column which always has the same value. That LIKE condition will
> > be inefficient if the item table is big because it cannot take
> > advantage of any index there may be on the ifield column.
> >
> > It might make more sense if I understood the nature of the data
> > in the
>
> > item table. When you do reflexive joins it is often an
> > indication that
>
> > you have one table holding more than one sort of entity and it might
> > be better to break it into more than one table. You might also find
> > then that getting the data out in the way you want becomes
> > straightforward.
> >
> > John Bonnett
> >
> > -----Original Message----- From: Melissa Dougherty [mailto:melissa@cse-
> > corp.com] Sent: Thursday, 19 October 2006 11:47
> AM To:
> > [email protected] Subject: Inline View
> >
> > I'm trying to take table data and display the data horizontal....
> > I need to take the column and show the results in one row.  I
> > have tried
>
> > several different inline view (queries) and get multiple rows.  Here
> > is an example.... It brings back three rows with each column in a
> > different row.
> >
> >
> > select distinct i.reportid, (select ivalue from item where
> > ifield like
>
> > '%IP Address%' and ifield = i.ifield and ivalue = i.ivalue) AS
> > IPAddress, (select ivalue from item where ifield = 'Computer Name'
> > and ifield = i.ifield and ivalue =
> > i.ivalue) AS Computer from item i where i.reportid = 1 order by 1
> >
> >
> > Any suggestions?
> >
> > Thanks, Melissa
>
>
> [snip]

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]