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:

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:

Here's a sample Cars table:

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!

• sql
• sql-server
• t-sql
• sql-update

• 5 Don't use sql to achieve these kind of things and you'll be fine, formatting is done in your receiving application if needed – siggemannen Commented Feb 5 at 0:18
• 2 Can you show what you are getting... I can't tell how far off you are. And as you know if you provide the DDL+DML to reproduce it makes it much easier for others to assist. – Dale K Commented Feb 5 at 0:32
• If you're wondering which applications can do that- SSRS and PBI-Paginated-Reports can do that using column groups (in a tablix). – Zorkolot Commented Feb 6 at 20:09

1 Answer 1

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;