sql: need to output data after a sum aggregate function is used

Question

I have 2 conditions on my SQL query that I need to fulfill.

First: I have to count the number of times a person sold a product and then group them by name. I have the below SQL code for this.

select distinct
a.name,
sum(case when m.code = 'A01' then 1  else 0 end) Insulin,
sum(case when m.code = 'S01' then 1  else 0 end) Syringe
from agent a
join medicine m
group by c.name

This results in the below table which is fine.

<table style="text-align:center">
<th>name</th>
<th>insulin</th>
<th>syringe</th>
<tr>
<td>Sarah</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>Mike</td>
<td>2</td>
<td>3</td>
</tr>
</table>

The next condition is where I am stuck.

Second: I need to put in the transaction id's on each product sold. So if Mike sold 2 Insulins, I need to put in the transactions ID's of these all in one cell. So something like below

<table style="text-align:center">
<th>name</th>
<th>insulin</th>
<th>syringe</th>
<th>insulin-transactionID's</th>
<th>syringe-transactionID's</th>
<tr>
<td>Sarah</td>
<td>1</td>
<td>2</td>
<td>2017001</td>
<td>2017002,2017006</td>
</tr>
<tr>
<td>Mike</td>
<td>2</td>
<td>3</td>
<td>2017881,2017899</td>
<td>2017456,2017567,2017898</td>
</tr>
</table>

E.g. I have 3 tables. Agent, Medicine, Transactions. I am open to other solutions. Let's just say that the Medicine table has a transaction field on it

Answer 1

Your query does not compile and your definition of Transaction linkage is incomplete. I will give it a go none the less. I made an assumption on the transaction table because it seemed plausible that it is being used to link your agent to the medicine dispensed.

This could be done using two pivots, however, that would provably be rather verbose and confusing, so I am attempting it with a inner query for concatenation and detail per user/med and outer query with dynamic aggregate fields to sum up into proper buckets.

You need to concatenate the transaction ID's prior to summing or within the same level. The trick is a sub-query to concatenate the values and then just steal the max from each group which yields the same as taking the first.

DECLARE @Medicine TABLE(MedicineID INT,Code NVARCHAR(12))
DECLARE @Agent TABLE(AgentID INT,name NVARCHAR(50))
DECLARE @Transaction TABLE(TransactionID INT,AgentID INT,MedicineID INT)

INSERT @Medicine SELECT 1,'A01'
INSERT @Medicine SELECT 2,'S01'

INSERT @Agent SELECT 1,'Sarah'
INSERT @Agent SELECT 2,'Mike'

INSERT @Transaction SELECT 2017001,1,1
INSERT @Transaction SELECT 2017002,1,2
INSERT @Transaction SELECT 2017006,1,2

INSERT @Transaction SELECT 2017881,2,1
INSERT @Transaction SELECT 2017899,2,1
INSERT @Transaction SELECT 2017456,2,2
INSERT @Transaction SELECT 2017567,2,2
INSERT @Transaction SELECT 2017898,2,2



SELECT
    Detail.name,
    insulin=SUM(CASE WHEN Detail.Code='A01' THEN UsageCount ELSE NULL END),
    syringe=SUM(CASE WHEN Detail.Code='S01' THEN UsageCount ELSE NULL END),
    'insulin-transactionID''s' = MAX(CASE WHEN Detail.Code='A01' THEN TransactionIDs ELSE NULL END),
    'syringe-transactionID''s' = MAX(CASE WHEN Detail.Code='S01' THEN TransactionIDs ELSE NULL END)
FROM
(
    select
        a.name,
        m.code,
        UsageCount=COUNT(*),
        TransactionIDs =STUFF((SELECT ',' + CAST(TransactionID AS NVARCHAR(20)) FROM @Transaction T WHERE T.AgentID = a.AgentID AND T.MedicineID=m.MedicineID FOR XML PATH('')),1,1,'')
    from
        @transaction t
        inner join @agent a on a.AgentID=t.AgentID
        inner join @medicine m on m.medicineID=t.MedicineID 
    where
        m.Code IN('A01','S01')
    group by
        a.name,m.code,a.AgentID,m.MedicineID
)AS Detail
GROUP BY
    Detail.name
ORDER BY 
    Detail.name DESC