Inline View

------=_NextPart_000_000F_01C6F303.13CBC290
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 =3D =
i.ifield and ivalue =3D i.ivalue) AS IPAddress,
(select ivalue from item where ifield =3D 'Computer Name' and ifield =3D =
i.ifield and ivalue =3D i.ivalue) AS Computer
from item i
where i.reportid =3D 1
order by 1


Any suggestions?

Thanks,
Melissa
------=_NextPart_000_000F_01C6F303.13CBC290--

RE: Inline View

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 =3D i2.ivalue=20
AND i1.ifield =3D i2.ifield
AND i3.ifield =3D 'Computer Name'
AND i1.ivalue =3D i3.ivalue
AND i1.ifield =3D i3.ifield
AND i1.reportid =3D 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]=20
Sent: Thursday, 19 October 2006 11:47 AM
To: win32@lists.mysql.com
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 =3D
i.ifield and ivalue =3D i.ivalue) AS IPAddress, (select ivalue from item
where ifield =3D 'Computer Name' and ifield =3D i.ifield and ivalue =3D
i.ivalue) AS Computer from item i where i.reportid =3D 1 order by 1


Any suggestions?

Thanks,
Melissa=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

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"
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:
> win32@lists.mysql.com 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/win32?unsub=gcdmw-win32@m.gmane.org

RE: Inline View

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: jtgalkowski@alum.mit.edu [mailto:jtgalkowski@alum.mit.edu]=20
Sent: Friday, 20 October 2006 9:11 AM
To: win32@lists.mysql.com
Cc: Bonnett, John; melissa@cse-corp.com
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"
said:
> If I understand you correctly you want the the fields to be in the=20
> rows and the records to be in the columns not the other way around as=20
> it usually is. I don't think you can get MySQL to return data like=20
> 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=20
> understand it correctly, I would write it like this and avoid the=20
> subqueries.
>
> SELECT DISTINCT i1.reportid, i2.ivalue as IPAddress, i3.ivalue as=20
> Computer FROM item AS i1, item AS i2, item AS i3 WHERE i2.ifield LIKE=20
> '%IP Address%' AND i1.ivalue =3D i2.ivalue AND i1.ifield =3D i2.ifield =
AND

> i3.ifield =3D 'Computer Name' AND i1.ivalue =3D i3.ivalue AND =
i1.ifield =
> i3.ifield AND i1.reportid =3D 1 ORDER BY 1
>
> This looks a bit odd. You don't really need i1.reportid in the=20
> selected columns because the WHERE clause means that it is always 1=20
> 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=20
> inefficient if the item table is big because it cannot take advantage=20
> 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=20
> be better to break it into more than one table. You might also find=20
> then that getting the data out in the way you want becomes=20
> straightforward.
>
> John Bonnett
>
> -----Original Message----- From: Melissa Dougherty=20
> [mailto:melissa@cse- corp.com] Sent: Thursday, 19 October 2006 11:47
AM To:
> win32@lists.mysql.com Subject: Inline View
>
> I'm trying to take table data and display the data horizontal.... I=20
> 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=20
> is an example.... It brings back three rows with each column in a=20
> different row.
>
>
> select distinct i.reportid, (select ivalue from item where ifield like

> '%IP Address%' and ifield =3D i.ifield and ivalue =3D i.ivalue) AS=20
> IPAddress, (select ivalue from item
> where ifield =3D 'Computer Name' and ifield =3D i.ifield and ivalue =
=3D
> i.ivalue) AS Computer from item i where i.reportid =3D 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/win32?unsub=3Dgcdmw-win32@m.gmane.org

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"
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: jtgalkowski@alum.mit.edu
> [mailto:jtgalkowski@alum.mit.edu] Sent: Friday, 20 October 2006 9:11
> AM To: win32@lists.mysql.com
> Cc: Bonnett, John; melissa@cse-corp.com 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"
> 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:
> > win32@lists.mysql.com 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/win32?unsub=gcdmw-win32@m.gmane.org

Re: Inline View

Melissa,

There actually is a way to do this if you only need to list one single
row for each query. Try something like this:

SET @rownumber=0;
select @rownumber:=@rownumber+1,
CASE @rownumber
WHEN 1 THEN 'col_1'
WHEN 2 THEN 'col_2'
WHEN 3 THEN 'col_3'
WHEN 4 THEN 'col_4'
WHEN 5 THEN 'col_5'
WHEN 6 THEN 'col_6'
WHEN 7 THEN 'col_7'
WHEN 8 THEN 'col_8'
WHEN 9 THEN 'col_9'
WHEN 10 THEN 'col_10'
WHEN 11 THEN 'col_11'
WHEN 12 THEN 'col_12'
END as Col_Names,
CASE @rownumber
WHEN 1 THEN col_1
WHEN 2 THEN col_2
WHEN 3 THEN col_3
WHEN 4 THEN col_4
WHEN 5 THEN col_5
WHEN 6 THEN col_6
WHEN 7 THEN col_7
WHEN 8 THEN col_8
WHEN 9 THEN col_9
WHEN 10 THEN col_10
WHEN 11 THEN col_11
WHEN 12 THEN col_12
END as data
FROM item
WHERE @rownumber<=12 and reportid = 1;

This works for one of my tables, although I have replaced the column and
table names. Replace the 'col_?' with your actual column names. In the
where clause, @rownumber should be <= number _of_columns_in_your_table.

If you are running multiple queries in the same session, be sure to
reset @rownumber between each subsequent query.

Please let me know how this works for you.

Randy Clamons
Systems Programming
randy@novaspace.com


Melissa Dougherty wrote:
> 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

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org