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

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/2...crosstabs.html

Baron

>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...)
>

2007/5/2, Peter Brawley <peter.brawley@earthlink.net>:
> 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

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 <peter.brawley@earthlink.net>:
>> 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);
>
>

2007/5/2, Peter Brawley <peter.brawley@earthlink.net>:
> 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

>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 <peter.brawley@earthlink.net>:
>> 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?
>