sql query for json output

Question

Using MSSQL 2019. Here is the query:

SELECT       
substring([PIMNumber],1,2) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,2)  
UNION  
SELECT       
substring([PIMNumber],1,5) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,5)  
union  
SELECT       
substring([PIMNumber],1,8) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,8)  
union  
SELECT       
substring([PIMNumber],1,11) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,11)  
union  
SELECT       
substring([PIMNumber],1,14) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,14)  
union  
SELECT       
substring([PIMNumber],1,17) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,17)  
union  
SELECT       
substring([PIMNumber],1,20) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,20)  
union  
SELECT       
substring([PIMNumber],1,23) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,23)  
union  
SELECT       
substring([PIMNumber],1,26) as pim,  
count(PIMNumber) as cnt  
FROM [Contact].[Contact].[Consumer]  
group by substring([PIMNumber],1,26)  
order by pim  

Sample Output:
First question: Is there a better way to write this query?
Second question: How to output as json so that pim 01 contains all 01.xxxxx, 01.01 contains 01.01.xxxxx, 02 contains 02.xxx, etc?
Sample json: [{"pim": "01", "cnt": 1045}, [{"pim":"01.01","cnt":245},[{"pim":"01.01.01","cnt":35}], [{"pim":"01.02","cnt":135}]]], [{"pim":"02","cnt":2345}]
(brackets may be misplaced)
Last question: Is there a way to write the query if the total length of PIMNumber is unknown? Each new level would be .##

Thanks.
Bryan

Answer 1

As for the first question, here is an outline that avoids repeated scans of the table:

   ; WITH CTE AS (  
       SELECT substring(PIMNumber, 1, 2) AS "P1-2", substring(PIMNumber, 3, 6) "P3-8", substring(PIMnumber, 9, 3) AS "P9-11"  
       FROM   tbl  
   ), aggr AS (  
       SELECT "P1-2", "P3-8", "P9-11", COUNT(*) AS cnt  
       FROM   CTE  
       GROUP  BY GROUPING SETS (("P1-2", "P3-8", "P9-11"),  
                                ("P1-2", "P3-8"),  
                                ("P1-2"))  
   )  
   SELECT concat("P1-2", "P3-8", "P9-11") AS str, cnt  
   FROM aggr  

But I need to add the disclaimer since I don't have your data, I have not been able to test it.

As for the JSON part, I pass for now.

And for the last question: to that end you will need use dynamic SQL to generate a query.

Answer 2

Thank you Erland.

I think I need to think of a different solution though. My query, and yours, return rows with duplicate PIMNumbers.

From the query:
pim - cnt

1. 01 - 34
2. 01 - 596

Both of those are correct, in a sense. There are 34 that exactly match 01.01. There are 596 that are like 01.01%. All I need is the 596 value. I'd like to take all these values, export it to json, then put it in a tree map so that you can then drill down and see how many customers are at each level. I'll have to look into our charting program to see if it can query as you drill down through the tree map instead of supplying it with all the data up front.

Thank you for the help.