View select results

[spacemarc]

Hi
my table have three fields that, if selected, are shown like:

area1, value_one, thing_one
area1, value_two, thing_32
area1, value_three, thing_ dd
area2, value_ten, thing_6w
area2, value_ff, thing_l



can I obtain a recordset like this?

area1, value_one, thing_one
  //,   value_two, thing_32
  //,   value_three, thing_ dd
area2, value_ten, thing_6w
  //,   value_ff, thing_l

So, do not repeat more times the value of the first column (area1, area2...)

--
http://www.spacemarc.it

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View select results

[Baron Schwartz]

Hi,

spacemarc wrote:

Hi
my table have three fields that, if selected, are shown like:

area1, value_one, thing_one
area1, value_two, thing_32
area1, value_three, thing_ dd
area2, value_ten, thing_6w
area2, value_ff, thing_l



can I obtain a recordset like this?

area1, value_one, thing_one
  //,   value_two, thing_32
  //,   value_three, thing_ dd
area2, value_ten, thing_6w
  //,   value_ff, thing_l

So, do not repeat more times the value of the first column (area1, 
area2...)


Giuseppe Maxia wrote a great article on this some time ago.  The technique is called 
cross-tabulation or pivot tables.  Here is a link:

http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html

Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View select results

[Peter Brawley]

can I obtain a recordset like this?
area1, value_one, thing_one
  //,   value_two, thing_32
  //,   value_three, thing_ dd
area2, value_ten, thing_6w
  //,   value_ff, thing_l

SET @prev='';
SELECT
 IF(area = @prev, '', @prev := area) AS area,
 ... other columns ...
FROM c ...

PB


spacemarc wrote:

Hi
my table have three fields that, if selected, are shown like:

area1, value_one, thing_one
area1, value_two, thing_32
area1, value_three, thing_ dd
area2, value_ten, thing_6w
area2, value_ff, thing_l



can I obtain a recordset like this?

area1, value_one, thing_one
  //,   value_two, thing_32
  //,   value_three, thing_ dd
area2, value_ten, thing_6w
  //,   value_ff, thing_l

So, do not repeat more times the value of the first column (area1, 
area2...)




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View select results

[spacemarc]

2007/5/2, Peter Brawley [EMAIL PROTECTED]:

Works for me. Please post a CREATE TABLE stmt  enough INSERTs to
demonstrate the problem.


This is the dump (MySQL: 5.0.38): the table is not final version, just
to test the query.

CREATE TABLE `products` (
`area` varchar(25) NOT NULL,
`text` varchar(25) NOT NULL,
`amount` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `products` (`area`, `text`, `amount`) VALUES
('area1', 'some text', 12),
('area1', 'other text here', 13),
('area3', 'example...', 22),
('area2', 'things', 123),
('area1', 'bla bla...', 24),
('area2', 'others again', 231),
('area1', 'english language..', 44),
('area1', 'server database', 53),
('area3', 'php language...', 22),
('area2', 'linux box', 951),
('area1', 'developer tools', 4),
('area2', 'others words', 1);



--
http://www.spacemarc.it

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View select results

[Peter Brawley]

Right, give the computed column an alias differeing from the column name, eg

SET @prev='';
SELECT 
 IF(area = @prev, '', @prev := area) AS AreaHdr,

 text,amount
FROM products
ORDER BY area DESC;

PB

spacemarc wrote:

2007/5/2, Peter Brawley [EMAIL PROTECTED]:

Works for me. Please post a CREATE TABLE stmt  enough INSERTs to
demonstrate the problem.


This is the dump (MySQL: 5.0.38): the table is not final version, just
to test the query.

CREATE TABLE `products` (
 `area` varchar(25) NOT NULL,
 `text` varchar(25) NOT NULL,
 `amount` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `products` (`area`, `text`, `amount`) VALUES
('area1', 'some text', 12),
('area1', 'other text here', 13),
('area3', 'example...', 22),
('area2', 'things', 123),
('area1', 'bla bla...', 24),
('area2', 'others again', 231),
('area1', 'english language..', 44),
('area1', 'server database', 53),
('area3', 'php language...', 22),
('area2', 'linux box', 951),
('area1', 'developer tools', 4),
('area2', 'others words', 1);




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View select results

[spacemarc]

2007/5/2, Peter Brawley [EMAIL PROTECTED]:

Right, give the computed column an alias differeing from the column name, eg

SET @prev='';
SELECT
  IF(area = @prev, '', @prev := area) AS AreaHdr,
  text,amount
FROM products
ORDER BY area DESC;


ok, now it works! thanks!

One last thing: you set, at first, a parameter called @prev with Null
(' ') value: right?

And, after, you use, instead IF ELSE statement, another syntax: is it
trinary operator? if yes, why it not is in the online MySQL manual?

--
http://www.spacemarc.it

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View select results

[Peter Brawley]

One last thing: you set, at first, a parameter called @prev with Null
(' ') value: right?

No, I set it to a string containing one space char. Use anything that 
does not occur as data in the column.


And, after, you use, instead IF ELSE statement, another syntax: is it
trinary operator? if yes, why it not is in the online MySQL manual?

I used the IF() function, see 'Control Flow Functions' under 'Functions 
and Operators' in the manual


PB

--

spacemarc wrote:

2007/5/2, Peter Brawley [EMAIL PROTECTED]:
Right, give the computed column an alias differeing from the column 
name, eg


SET @prev='';
SELECT
  IF(area = @prev, '', @prev := area) AS AreaHdr,
  text,amount
FROM products
ORDER BY area DESC;


ok, now it works! thanks!

One last thing: you set, at first, a parameter called @prev with Null
(' ') value: right?

And, after, you use, instead IF ELSE statement, another syntax: is it
trinary operator? if yes, why it not is in the online MySQL manual?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]