SQL Server: Group rows and collect columns in JSON

Databases
#1

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.

#2

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?

#3

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

1 Like
#4

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.

#5

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

#6

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?

#7

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.

#8

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:

alexrosp77-2021-07-24 14-43-40

#9

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.