Thanks peeps. I used the MAX function n it worked.. On Wed, Nov 7, 2012 at 2:00 AM, Victor Kisovi <victor.kisovi@gmail.com>wrote:
I bet there must be another way to achieve this but this still works:
select colN0, max(col1), max(col2), max(col3) from test_sql group by colN0 union select colN0, max(col1), max(col2), max(col3) from test_sql group by colN0;
+-------+-----------+-----------+-----------+ | colN0 | max(col1) | max(col2) | max(col3) | +-------+-----------+-----------+-----------+ | 1 | A | B | C | | 2 | E | F | G | +-------+-----------+-----------+-----------+ 2 rows in set (0.00 sec)
On Wed, Nov 7, 2012 at 12:48 PM, Arthur Buliva <arthurbuliva@hotmail.com>wrote:
mysql> select * from skunk; +----+--------+--------+--------+ | id | valueA | valueB | valueC | +----+--------+--------+--------+
| 1 | A | 0 | 0 | | 1 | 0 | B | 0 | | 1 | 0 | 0 | C | | 2 | E | 0 | 0 | | 2 | 0 | F | 0 | | 2 | 0 | 0 | G | +----+--------+--------+--------+ 6 rows in set (0.00 sec)
mysql> select id, max(valueA), max(valueB), max(valueC) from skunk group by id; +----+-------------+-------------+-------------+ | id | max(valueA) | max(valueB) | max(valueC) | +----+-------------+-------------+-------------+
| 1 | A | B | C | | 2 | E | F | G | +----+-------------+-------------+-------------+ 2 rows in set (0.00 sec)
mysql>
------------------------------ From: mainakibui@gmail.com Date: Wed, 7 Nov 2012 11:40:22 +0300 To: skunkworks@lists.my.co.ke Subject: Re: [Skunkworks] SQL Help
Use arrays
<?php
/* <Language> PHP </Language> <Solution> create a multidimensional array </Solution> <Author> @mainakibui </Author>
$OurWebsites = array(www.360tours.co.ke, www.anza.co.ke);
*/
/*Begin by creating a multidimensional array from your query result*/
$queryResult=array(1=>array(array(A,0,0),array(0,B,0),array(0,0,C)),2=>array(array(E,0,0),array(0,F,0),array(0,0,G)));
echo "<br>View Your multidimensional array<br>";
/*what you have*/ foreach ($queryResult as $values): foreach ($values as $value=>$result): print_r($result); echo "<br>"; endforeach; endforeach;
echo "<br>";
/*Create a new multidimensional array*/ foreach ($queryResult as $xvalue=>$values):
foreach ($values as $results): foreach($results as $nvalue=>$result): if($result != '0'): $newarray[$xvalue][$nvalue]=$result; endif; endforeach; endforeach; endforeach;
echo "<br>Your final resulting array<br>";
/*This will give you (You can)*/ foreach($newarray as $value=>$array): echo $value." "; print_r($newarray[$value]); echo "<br>"; endforeach; ?>
Rgds, Kibui Kenneth Maina,* *
* * *T:* +254 (0) 721 173 780 *W:* Opticom Solutions<http://opticomsolutions.blogspot.com/> *B*: kit-b.com <http://www.kit-b.com> *T:* @mainakibui<https://twitter.com/#%21/mainakibui> *S:* kenneth.kibui <http://www.skype.com/intl/en/home> *Q*: Quora<http://www.quora.com/Kibui-Kenneth-Maina>
* * *Tools I Use:* PHP, Javascript, JQuery, MYSQL, SQLite, Android app dev, Joomla, Amazon cloud hosting, WHM, Cpanel, Adobe CS2-5, Dreamweaver, OSx, Ubuntu, Cent OS, Linux, Windows, Networking, Hardware, VM Ware.
On Wed, Nov 7, 2012 at 10:07 AM, julianne anyim <julianneanyim@gmail.com>wrote:
Hello,
I have a query that generates records in this form.
1 A 0 0 1 0 B 0 1 0 0 C 2 E 0 0 2 0 F 0 2 0 0 G
I want the records in the following format.
1 A B C 2 E F G
The query doesn't have an aggregate function (eg SUM, COUNT etc) so i can't GROUP BY.
Anyone please :)
Regards,
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Victor Kisovi +254 726 723 360
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke