CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
asmgx
asmgx

Reputation: 8044

Format SQL query output into a report text structure for LLM use

Question:

I have a query that lists the following columns: repid, Car, Model, Fault, and cnt (a count of occurrences). The current output of my query looks something like this:

Query:

SELECT repid, Car, Model, Fault, COUNT(*) AS cnt
FROM Cars
GROUP BY repid, Car, Model, Fault
ORDER BY repid, Car, Model, COUNT(*) DESC;

Current Output:

repid | Car  | Model | Fault | cnt
-----------------------------------------------
1     | Car1 | Model1 | Text1         | 5
1     | Car1 | Model1 | Text2         | 4
1     | Car1 | Model1 | Text3         | 3
1     | Car1 | Model2 | Text4         | 2
1     | Car1 | Model2 | Text5         | 1
2     | Car2 | Model1 | Text6         | 6
2     | Car2 | Model1 | Text7         | 4

I want the output to be more report-like, with repid, Car, and Model appearing only once per group and the rest of the rows showing just the Fault and cnt. For example:

Desired Output is a text for each report:


Report: RepID 1
-----------------
Car 1
    Model 1
        Fault1 repeated Cnt1 times
        Fault2 repeated Cnt2 times
        Fault3 repeated Cnt3 times

    Model 2
        Fault4 repeated Cnt4 times
        Fault5 repeated Cnt5 times
Car 2
    Model 3
        Fault6 repeated Cnt6 times
        Fault7 repeated Cnt7 times

    Model 4
        Fault8 repeated Cnt8 times
        Fault9 repeated Cnt9 times

How can I modify my query to achieve this kind of report-style output and store it in Reps table in RepsText

Sample Data:

Here's a sample Reps table:

repid RepsText
1 NULL
2 NULL
3 NULL
4 NULL

Here's a sample Cars table:

repid Car Model Fault
1 Car1 Model1 Text1
1 Car1 Model1 Text2
1 Car1 Model1 Text3
1 Car1 Model2 Text4
1 Car1 Model2 Text5
2 Car2 Model1 Text6
2 Car2 Model1 Text7

What I've Tried:

I've tried using ROW_NUMBER() and CASE expressions to hide duplicate values for repid, Car, and Model, but I'm struggling to get the output exactly as I want. Here is one attempt using CTEs:

WITH RankedData AS (
    SELECT 
        repid,
        Car,
        Model,
        Fault,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY repid, Car, Model ORDER BY COUNT(*) DESC) AS row_num
    FROM Cars
    GROUP BY repid, Car, Model, Fault
)
SELECT 
    CASE WHEN row_num = 1 THEN CAST(repid AS VARCHAR) ELSE '' END AS RepID,
    CASE WHEN row_num = 1 THEN Car ELSE '' END AS Car,
    CASE WHEN row_num = 1 THEN Model ELSE '' END AS Model,
    Fault AS Fault,
    cnt AS Count
FROM RankedData
ORDER BY repid, Car, Model, cnt DESC;

This partially works but does not give me exactly what I'm looking for. Is there a better way to format SQL query results to achieve this report-style output?

Any help would be appreciated! Thanks!

Upvotes: 1

Views: 70

Answers (1)

Charlieface
Charlieface

Reputation: 72194

I have no idea why you'd ever want to do this in SQL, rather than in your application.

But it can be done, by using GROUPING SETS and some conditions. The GROUPING() function returns 1 if the column has been aggregated, otherwise 0. And since you want to repeat the rows by the number of rows in the final grouping, it makes more sense not to group that up at all, and so we add the PK into the final grouping set.

SELECT
  CASE
    WHEN GROUPING(Car) = 1 THEN
      CONCAT('Report: RepID 
-----------------', repid)
    WHEN GROUPING(Model) = 1 THEN
      CONCAT('Car ', Car)
    WHEN GROUPING(Fault) = 1 THEN
      CONCAT('    Model ', Model)
    ELSE
      CONCAT('        Fault ', Fault)
    END
FROM Cars
GROUP BY GROUPING SETS (
    (repid),
    (repid, Car),
    (repid, Car, Model),
    (repid, Car, Model, Fault, YourPrimaryKey)
)
ORDER BY
  repid,
  GROUPING(Car) DESC,
  Car,
  GROUPING(Model) DESC,
  Model,
  GROUPING(Fault) DESC,
  COUNT(*) OVER (PARTITION BY repid, Car, Model, Fault) DESC;

Upvotes: 0

Related Questions