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]