SQL Server: Group rows and collect columns in JSON

[alexandrosp77]

Hello,

I have an SQL Server database that looks like this (simplified):

productcode	title	barcode	material	price	stock
DA-0000	Ring	58320	Gold Κ14	100.00	0
DA-0154	Bracelet	32340	Gold Κ18	200.00	1
DA-0154	Bracelet	32341	Gold Κ14	100.00	0
DA-0154	Bracelet	NULL	Silver	100.00	1

I want to group items that have the same productcode, turning them into a ‘main’ product, and have the rest as ‘variants’ of the main products. Items with a NULL barcode should be listed after the ones with a specified barcode. Example result:

|productcode|title|variants|
|—|—|—|—|—|—|
|DA-0000|Ring|[{“barcode”: 58320, “material”: “Gold K14”, “price”: “100.00”, “stock”: 0}]
|DA-0154|Bracelet|[{“barcode”: 32340, “material”: “Gold K18”, “price”: “200.00”, “stock”: 1}, {“barcode”: 32341, “material”: “Gold K14”, “price”: “100.00”, “stock”: 0}, {“barcode”: NULL, “material”: “Silver”, “price”: “100.00”, “stock”: 1}]

My SQL skills are not up to this task, so I would appreciate any help.

[wake689]

Welcome to the forum.

I only know a little SQL. I don’t know whether your level of expertise is much above, the same or below mine so apologies if I pitch this at the wrong level.

Firstly, I didn’t know it was possible to have multiple rows in one cell like that. But even given that it is then I suspect it may not be a wise move? I would imagine making queries of that could be very complicated and problematic.

What do you ultimately want to do with the database? I suspect whatever you wish to do could be better and more easily done by keeping the database as it is and querying it.

I note that you don’t have a primary key column?

[r937]

i’ve never needed to work with JSON data, but i do know that SQL Server has built-in JSON functionality

see if this helps – https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

[alexandrosp77]

@wake689

Welcome to the forum.

Thank you @wake689. I am not really sure I am approaching the problem in a wise way, as you said.
This database comes from an ERP that I have minimal control over. Its content must be plugged to a system that expects an associative array (php) of products and its variants.

At first I thought of doing a query to get the distinct ‘main’ products, and then query each one in a loop to get its variants. Then I learned that queries in loops are a bad idea.

My problem in short is how to build this array of products and variants from the flat database rows.

[wake689]

You should run a query and then use PHP loops on the resulting array.

[alexandrosp77]

Do you mean getting the db rows as they are, and perform grouping, variant extracting etc with PHP instead of SQL? Any implications on performance?

[wake689]

Not necessarily. I just meant that I think it would be better to just perform one SQL query and then use PHP to obtain the PHP array that you need, rather than doing many SQL queries inside PHP loops.

I’m afraid I don’t have enough SQL knowledge to know how far you could get with a SQL query before starting with the PHP.

I’m trying to visualise the form that the PHP array that you need would take, by looking at the table you showed. Would it be a three dimensional array?

I hope someone with greater knowledge turns up soon to help out.

[John_Betong]

Try this:

Group Rows
productcode 	title 	barcode 	material 	price 	stock
DA-0000 	Ring 	58320 	Gold Κ14 	100 	0
DA-0154 	Bracelet 	32340 	Gold Κ18 	200 	1
	Bracelet 	32341 	Gold Κ14 	100 	0
	Bracelet 	NULL 	Silver 	100 	1

Source:
<?php declare(strict_types=1);

$rows = [
['DA-0000',    'Ring',          '58320', 'Gold Κ14', 100.00, 0 ],
['DA-0154',    'Bracelet', '32340', 'Gold Κ18', 200.00, 1 ],
['DA-0154',    'Bracelet',  '32341', 'Gold Κ14', 100.00, 0 ],
['DA-0154',    'Bracelet',     NULL,      'Silver',      100.00, 1 ],
];

$titles = "
    <tr>
    <th> productcode </th>
    <th> title </th>
    <th> barcode </th>
    <th> material </th>
    <th> price </th>
    <th> stock </th>
    </tr>
";
$table = '<table>' .$titles;
    $last = FALSE; 
    foreach($rows as $key => $row) :
        # echo '<pre>'; print_r($row); echo '</pre>';
        $row[2] = $row[2] ?? 'NULL';
        $group     = $row[0];
        if( $last && $group === $last) : 
            $group = '';
        endif;    
        # echo '<br>$last ==> ' .$last .' ==> ' .$group;
        $last      = $row[0];

        $table .= $tmp = <<< ____EOT
            <tr>
                <td> {$group} </td>
                <td> {$row[1]} </td>
                <td> {$row[2]} </td>
                <td> {$row[3]} </td>
                <td> {$row[4]} </td>
                <td> {$row[5]} </td>
            </tr>
____EOT;
    endforeach;
$table .= '</table>';

?><!doctype html><html lang="en-GB">
<head>
<style>
table tr td {padding: 0.12em 0.88em;}
div {width: 88%; margin: 0 auto; border: solid 1px #ccc;}
</style>
<title> Group Rows </title>
</head>
<body> 
    <h1> Group Rows </h1>
    <?php
        echo $table;
        echo '<pre>'; 
            // print_r($rows); 
        echo '</pre>';

        echo '<h2> Source: </h2>';
        echo '<div>';
            highlight_file(__file__);
        echo '</div>';    

    ?>    
</body>
</html>

Output:

[alexandrosp77]

Thanks @John_Betong, but I don’t want to just display the items, I must create an array structure.
I came up with the following solution using PHP:

$query = '
  SELECT
    product.productcode product,
    product.barcode barcode,
    title
    material,  
    product.pricextotal price,
    product.stock

  FROM dbo.product product
  LEFT JOIN dbo.prdnames title ON product.barcode = title.id
  LEFT JOIN dbo.prdalloys material ON product.alloyid = material.id
  
  ORDER BY product;
';

$statement = $pdo->query($query);
$db_products = $statement->fetchAll(PDO::FETCH_ASSOC);
$products = [];

$ids = array_map(function($item) use ($db_products) {
  return $item['product'];
}, $db_products);

$variations_count = array_count_values($ids);
$index = 0;

if ($db_products) {
  foreach ($variations_count as $id => $variations) {
    $children = [];

    for ($i = $index; $i < $index + $variations; $i++) {
      $children[] = [
        'slug' => Str::slug($db_products[$i]['product'] . '-' . $db_products[$i]['barcode']),
        'template' => 'variation',
        'content' => [
          'title' => $db_products[$i]['title'] . '/' . $db_products[$i]['material'],
          'price' => $db_products[$i]['price'],
          'stock' => $db_products[$i]['stock']
        ]
      ];
    }

    $product = $db_products[$index];
      $products[] = [
      'slug' => Str::slug($id),
      'template' => 'product',
      'content' => [
          'item_title' => $product['title'],
      ],
      'children' => $children
    ];

    $index += $variations;
  }
}

My only concern is if this PHP code is slower (products and variations will probably be thousands) than SQL.