Select multiple values from multiple rows

Question

Cant figure out how to get this result:

ProductID       Variantno
53121           5197, 5198,5199

From this data collection.

ProductID       Variantno
53121           5197
53121           5198
53121           5199

Tried with group by but no good result, total sql noob...

which rdbms you are dealing with? mysql or sql-server? or you need a cross-rdbms solution? — Paolo
– Paolo, Commented Nov 17, 2014 at 11:02

SonalPM · Accepted Answer · 2014-11-17 11:00:48Z

2

Try this..

SELECT 
  ProductID,
  GROUP_CONCAT(Variantno)
FROM tbl
GROUP BY ProductID

answered Nov 17, 2014 at 11:00

SonalPM

1,3338 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Over a year ago

Consider that the result returned by GROUP_CONCAT is a string (just as a note)

SonalPM Over a year ago

Don't forget to Select as Answer :P

Dimag Kharab · Accepted Answer · 2014-11-17 11:01:42Z

0

select ProductID , group_concat(Variantno)
from table
group by ProductID

answered Nov 17, 2014 at 11:01

Dimag Kharab

4,5391 gold badge27 silver badges47 bronze badges

Comments

Paresh J · Accepted Answer · 2014-11-17 11:21:27Z

0

Try this:

WITH cte AS (
    SELECT 
        ProductID,
        CAST('<r>' + REPLACE(variantNo, ',', '</r><r>') + '</r>' AS XML) AS VariantNos
    FROM TestTable
)
SELECT 
    ProductID,
    xTable.xColumn.value('.', 'VARCHAR(MAX)') AS VariantNo
FROM cte
CROSS APPLY VariantNos.nodes('//r') AS xTable(xColumn)

answered Nov 17, 2014 at 11:21

Paresh J

2,4293 gold badges26 silver badges32 bronze badges

Comments

Sectona · Accepted Answer · 2014-11-17 11:36:05Z

Without showing your code and database. I assumed that you are working with php and mysql. I have run and tested this code and it works. that means it will work for you. my connection is PDO. Give me a shout if are still having issues

<?php



// pdo connection



$db = new PDO (

    'mysql:host=localhost;dbname=sectona_db;charset=utf8', 

    'root', // username



    'root90' // password

);

?>




                    <?php

Echo 'Data Output:<br>';
include("pdo.php");

$result = $db->prepare("SELECT * FROM product where id='123'");
        $result->execute(array());



while ($r = $result->fetch()) 
                       {
//$data = htmlentities($r['product'], ENT_QUOTES, "UTF-8");






?>

<?php echo htmlentities($r['product'], ENT_QUOTES, "UTF-8");?>,



<?php } ?>

Collectives™ on Stack Overflow

Select multiple values from multiple rows

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related