sqlsql-serverazure-logic-appsazure-sql-managed-instancesql-server-2022

SQL query to group parameters into same column where ID is the same


I am trying to write a query that will groups several columns under one main column type called parameters instead of creating a record per parameter. This what I currently have in my table called cars

car_id car_model car_lot spec_name displayMin displayMax state
102 Toyota 2 Battery 100 1 1
102 Toyota 2 Engine 1000 25 1
102 Toyota 2 Oil 90 9 1
103 BMW 2 Engine 2000 50 1
103 BMW 2 Throttle 20 5 0
103 BMW 2 Battery 100 10 0

What I am expecting is this:

car_id car_model car_lot car_params
102 Toyota 2 params {(Battery:100,1,1), (Engine:1000,25,1),(Oil:90,9,1)}
103 BMW 2 params {(Engine:2000,50,1), (Throttle:20,5,0),(Battery:100,10,0)}

I am unsure how to write this query, I have tried using intersect but that did not work correctly.

I am writing this query so that it will be able to loop through this is a logic app on Azure.


Solution

  • Here are a couple of ideas:

    SELECT  car_id, car_model, car_lot, 'params {' + string_agg(concat('(', spec_name, ':', displayMin, ',', displayMax, ',', state, ')'), ',') + '}'
    FROM    (
        VALUES  (102, N'Toyota', 2, N'Battery', 100, 1, 1)
        ,   (102, N'Toyota', 2, N'Engine', 1000, 25, 1)
        ,   (102, N'Toyota', 2, N'Oil', 90, 9, 1)
        ,   (103, N'BMW', 2, N'Engine', 2000, 50, 1)
        ,   (103, N'BMW', 2, N'Throttle', 20, 5, 0)
        ,   (103, N'BMW', 2, N'Battery', 100, 10, 0)
    ) t (car_id,car_model,car_lot,spec_name,displayMin,displayMax,state)
    GROUP BY car_id, car_model, car_lot
    
    
    ;WITH data AS (
        SELECT  *
        FROM    (
        VALUES  (102, N'Toyota', 2, N'Battery', 100, 1, 1)
        ,   (102, N'Toyota', 2, N'Engine', 1000, 25, 1)
        ,   (102, N'Toyota', 2, N'Oil', 90, 9, 1)
        ,   (103, N'BMW', 2, N'Engine', 2000, 50, 1)
        ,   (103, N'BMW', 2, N'Throttle', 20, 5, 0)
        ,   (103, N'BMW', 2, N'Battery', 100, 10, 0)
    ) t (car_id,car_model,car_lot,spec_name,displayMin,displayMax,state)
    )
    SELECT  car_id, car_model, car_lot, (
        SELECT  spec_name, displayMin, displayMax, state
        FROM    data dOuter
        WHERE   dOuter.car_id = d.car_id
        FOR json path, root('params')
        )
    FROM    data d
    GROUP BY car_id, car_model, car_lot
    

    The first one uses STRING_AGG to aggregate the params into your "old" style params string.

    The second one does a json version of above.