SQL Help - skunkworks - Skunkworks Site

newer
Orange Livebox Problems?

SQL Help

older
[SKUNKWORKS] .ru domain

julianne anyim

7 Nov 2012 7 Nov '12

10:07 a.m.

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,

Attachments:

attachment.html (text/html — 420 bytes)

Reply

Sign in to reply online Use email software

Show replies by date

Kibui Maina

7 Nov 7 Nov

11:40 a.m.

New subject: [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

Reply

Sign in to reply online Use email software

Arthur Buliva

12:48 p.m.

New subject: [Skunkworks] SQL Help

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 B: kit-b.com T: @mainakibui S: kenneth.kibui Q: Quora 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

Reply

Sign in to reply online Use email software

Victor Kisovi

1 p.m.

New subject: [Skunkworks] SQL Help

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

Reply

Sign in to reply online Use email software

julianne anyim

3:37 p.m.

New subject: [Skunkworks] SQL Help

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

Reply

Sign in to reply online Use email software

4557

Age (days ago)

4557

Last active (days ago)

Download

4 comments

4 participants

tags

participants (4)

Arthur Buliva
julianne anyim
Kibui Maina
Victor Kisovi