CODEWITHSUNDEEP
mysqlsql
frednoah
frednoah

Reputation: 13

Select multiple values from multiple rows

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

Upvotes: 0

Views: 86

Answers (4)

Sectona
Sectona

Reputation: 98

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 } ?>

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

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)

Upvotes: 0

Dimag Kharab
Dimag Kharab

Reputation: 4519

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

Upvotes: 0

SonalPM
SonalPM

Reputation: 1335

Try this..

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

Upvotes: 2

Related Questions