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
[email protected]


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/[email protected]