sql - Aggregating IDs within a PARTITION BY query - Stack Overflow

I've got this SELECT :

SELECT OS.returnmessageid,
       OS.tagconfig,
       CASE
         WHEN OP.messagetype = 202
              AND OS.datapointid IN ( 1, 3 ) THEN
         CONVERT(NVARCHAR(max), OS.lookupvalue)
         ELSE Try_convert(Nvarchar(max), OS.value)
       END AS Value,
       OS.unitnumber,
       OD.messageutc,
       OP.messagetype,
       OS.isproccessed,
       OS.id,
       Row_number()
         OVER (
           partition BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype
           ORDER BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype )
           AS RowNum
FROM   ooocommreturnmessagesscaling OS
       LEFT JOIN ooocommreturnmessagesparsing OP
              ON OP.id = OS.returnmessageid
       LEFT JOIN ooocommreturnmessagedetails OD
              ON OP.returnmessageid = OD.id

Example result set:

I'd like to add a column that has all the associated values for OS.Id for the same combination of OS.ReturnMessageId, OS.UnitNumber, OD.MessageUTC, OP.MessageType .

This is the output that I'm looking for:

As you can see that last field IdAggregate has been added as a column separated list of OS.Id for the same combination of OS.ReturnMessageId, OS.UnitNumber, OD.MessageUTC, OP.MessageType

How do I add this IdAggregate column?

I've got this SELECT :

SELECT OS.returnmessageid,
       OS.tagconfig,
       CASE
         WHEN OP.messagetype = 202
              AND OS.datapointid IN ( 1, 3 ) THEN
         CONVERT(NVARCHAR(max), OS.lookupvalue)
         ELSE Try_convert(Nvarchar(max), OS.value)
       END AS Value,
       OS.unitnumber,
       OD.messageutc,
       OP.messagetype,
       OS.isproccessed,
       OS.id,
       Row_number()
         OVER (
           partition BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype
           ORDER BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
         OP.messagetype )
           AS RowNum
FROM   ooocommreturnmessagesscaling OS
       LEFT JOIN ooocommreturnmessagesparsing OP
              ON OP.id = OS.returnmessageid
       LEFT JOIN ooocommreturnmessagedetails OD
              ON OP.returnmessageid = OD.id

Example result set:

I'd like to add a column that has all the associated values for OS.Id for the same combination of OS.ReturnMessageId, OS.UnitNumber, OD.MessageUTC, OP.MessageType .

This is the output that I'm looking for:

As you can see that last field IdAggregate has been added as a column separated list of OS.Id for the same combination of OS.ReturnMessageId, OS.UnitNumber, OD.MessageUTC, OP.MessageType

How do I add this IdAggregate column?

• sql
• t-sql
• azure-sql-database
• aggregate-functions

Share Improve this question Follow edited Jan 22 at 23:17 Alex Gordon asked Jan 22 at 22:56 Alex GordonAlex Gordon 61k305305 gold badges705705 silver badges1.1k1.1k bronze badges 4

• My first thought was STRING_AGG() as in STRING_AGG(OS.id, ',') OVER(PARTITION BY OS.returnmessageid) WITHIN GROUP(ORDER BY OS.id) AS IdAggregate. Unfortunately, this is not supported and results in the message "The function 'STRING_AGG' is not a valid windowing function". (Why?) – T N Commented Jan 23 at 0:02
• appreciate you taking a look ! – Alex Gordon Commented Jan 23 at 0:04
• Side note: Why the Try_convert() in Try_convert(Nvarchar(max), OS.value)? No harm done, but I don't think that would ever fail. (Even a null OS.value input would quietly convert to null.) – T N Commented Jan 23 at 0:26
• As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Commented Jan 23 at 2:13

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

My first thought was to use the STRING_AGG() function as in STRING_AGG(OS.id, ',') OVER(PARTITION BY OS.returnmessageid) WITHIN GROUP(ORDER BY OS.id) AS IdAggregate. Unfortunately, this is not supported and results in the message "The function 'STRING_AGG' is not a valid windowing function".

I believe the solution is to wrap the main query up in a CTE, calculate IdAggregate using GROUP BY returnmessageid in another CTE, and then join the two in the final select.

WITH InitialQuery AS (
    SELECT OS.returnmessageid,
           OS.tagconfig,
           CASE
             WHEN OP.messagetype = 202
                  AND OS.datapointid IN ( 1, 3 ) THEN
             CONVERT(NVARCHAR(max), OS.lookupvalue)
             ELSE Try_convert(Nvarchar(max), OS.value)
           END AS Value,
           OS.unitnumber,
           OD.messageutc,
           OP.messagetype,
           OS.isproccessed,
           OS.id,
           Row_number()
             OVER (
               partition BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
             OP.messagetype
               ORDER BY OS.returnmessageid, OS.unitnumber, OD.messageutc,
             OP.messagetype )
               AS RowNum
    FROM   ooocommreturnmessagesscaling OS
           LEFT JOIN ooocommreturnmessagesparsing OP
                  ON OP.id = OS.returnmessageid
           LEFT JOIN ooocommreturnmessagedetails OD
                  ON OP.returnmessageid = OD.id
),
IdAgg AS (
    SELECT
        Q.returnmessageid,
        STRING_AGG(Q.id, ',') WITHIN GROUP(ORDER BY Q.id) AS IdAggregate
    FROM InitialQuery Q
    GROUP BY Q.returnmessageid
)
SELECT Q.*, I.IdAggregate
FROM InitialQuery Q
JOIN IdAgg I ON I.returnmessageid = Q.returnmessageid

Here are the results from a simplified version with some generated test data:

returnmessageid	OtherData	id	IdAggregate
1	Other Data	101	101,102,103
1	Other Data	102	101,102,103
1	Other Data	103	101,102,103
2	Other Data	104	104,105,106,107,108
2	Other Data	105	104,105,106,107,108
2	Other Data	106	104,105,106,107,108
2	Other Data	107	104,105,106,107,108
2	Other Data	108	104,105,106,107,108
3	Other Data	109	109,110
3	Other Data	110	109,110

See this db<>fiddle for a demo.