I've been tasked with grouping a column codes with unittype, problem is that there's more than a thousand entries for each unit type, so when attempting the following, it gives me an error of too many entries exceeded the limit of 8k bytes (using SSMS). I'd also like to group these entries by 100.
| unitType | Code |
|---|---|
| 100Kilograms | J90 |
| Kilogram | A701, Y02 |
| Kilogram | AY08,R809,LY10, AY11, UY12 |
| No Unit | OP14M DY16 |
When running a standard piece of code such as
SELECT [UnitType],
CONCAT('[',STRING_AGG(CONCAT('"',Code,'"'),','),']') AS Codes
FROM [Table]
GROUP BY [UnitType]
It gives me:
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.
The ideal solution is to group them by hundreds, shown by the example above. For example if there's 250 codes for 100 kilograms, I'd like there to be 3 entries with 100 codes for the first two rows and 50 for the last row. This is quite important for the next stage. Any help is appreciated, thanks!
You can use window functions to enumerate the records and group them by batches of 100:
SELECT [UnitType],
CONCAT('[', STRING_AGG(CONCAT('"', Code, '"'), ', ') ,']') AS Codes
FROM (
SELECT t.*, ROW_NUMBER() OVER(PARTITION BY [UnitType] ORDER BY [id]) rn
FROM [Table] t
) t
GROUP BY [UnitType], (rn -1) / 100
I assumed that a column called id can be used to order the records; you might need to adapt this the actual structure of your table.