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