I have try couples of selection but I failed to get the exact answered I want to show. I wish to get ideas from here. Below is the last temp after I selected into.

What I want the output to be showed as below:

From the above output, I want to get the sum of qty when RID is same row and only calculate for those have SAVID and SAVID must meet the same for each RID so that they only can sum up. As you can see, since only the LOTID is uniquely, so it hardly make qty to be sum up when RID meet the same rows.

My question: is there any best way to query the above select statement?

The result is I want to select all with the sum of qty and list all LOTID as in the result table shown above.

Thanks!

CodePudding user response：

try something like this -

SELECT rid,  lotid = STUFF(
             (SELECT ','   lotid 
              FROM data_table t1
              WHERE t1.rid = t2.rid
              FOR XML PATH (''))
             , 1, 1, '') 
             ,t2.savid
             ,sum(t2.qty ) AS qty
FROM data_table t2
WHERE t2.savid <> ''
GROUP BY rid,t2.savid;

CodePudding user response：

You can just use STRING_AGG along with normal aggregation in newer versions of SQL Server

SELECT
  t.RID,
  LOTID = STRING_AGG(t.LOTID, ','),
  t.SAVID,
  QTY = SUM(t.QTY)
FROM data_table t
WHERE t.SAVID <> ''
GROUP BY
  t.RID,
  t.SAVID;