First things first, when you visit a technical forum it is always a good idea to provide as much information as possible and to make it as easy as possible for people to help you. In a SQL forum, that means providing DDL and readily consumable sample data so that anyone wanting to help you can quickly knock up a nice quick copy of your data. In your case, this is enough: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
What you're asking isn't an overly simple thing. With that in mind, this is the answer: -
SELECT Sale_Type, SUM(Amount) AS Amount, MAX(Sno_Retained) AS Sno_Retained
FROM #Table1 a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1 b
WHERE a.Sale_Type = b.Sale_Type
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
) c(Sno_Retained)
GROUP BY Sale_Type;
That produces: -
Sale_Type Amount Sno_Retained
--------- ----------- --------------
Drug 40 1, 4, 7, 10
Fruit 60 2, 5, 8
Groceries 90 3, 6, 9
If all you were interested in is an answer, you can leave now 😛
Otherwise, let's take a look at what we're doing here.
First, set your results to text instead of the default grid. It'll make it easier for me to explain.
Execute this: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT Sno
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE;
You'll get back something like this: -
--------------------------------------------------------------------------------------------------------------------------
<Sno>1</Sno><Sno>2</Sno><Sno>3</Sno><Sno>4</Sno><Sno>5</Sno><Sno>6</Sno><Sno>7</Sno><Sno>8</Sno><Sno>9</Sno><Sno>10</Sno>
So that has concatenated all of your "Sno" into one long XML string.
Next, we'll add in the comma separators and remove the tags. We do this like this: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE;
You'll get back something like this: -
---------------------------------
, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Now, we want to remove the comma that appears at the start of the string.
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
);
You'll get something like this: -
-------------------------------
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Obviously, this is everything rather than added to the specific Sale_Type. Well, we do that with the CROSS APPLY.
We bring in your original query: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
--SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
-- FROM #Table1
-- ORDER BY Sno
-- FOR XML PATH(''), TYPE
-- ).value('.','NVARCHAR(MAX)'),1,2,''
-- );
SELECT Sale_Type, SUM(Amount) AS Amount
FROM #Table1 a
GROUP BY Sale_Type;
Which returns this: -
Sale_Type Amount
--------- -----------
Drug 40
Fruit 60
Groceries 90
We can then apply our XML query to your query, but we need to add a WHERE condition to the XML query to match the Sale_Type of each. This ensures that we only apply the XML query to gather the Sno for the individual Sale_Type.
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT Sale_Type, SUM(Amount) AS Amount
FROM #Table1 a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
)c(Sno_Retained)
GROUP BY Sale_Type;
This still returns: -
Sale_Type Amount
--------- -----------
Drug 40
Fruit 60
Groceries 90
If we reference the c.Sno_Retained straight into the query, we'd get: -
Msg 8120, Level 16, State 1, Line 12
Column 'c.Sno_Retained' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is because we're aggregating the query. We could solve this in two ways, we either aggregate the c.Sno_Retained or we move the first aggregation into a subquery that we reference. So, here are our two options: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
--Option 1
SELECT Sale_Type, SUM(Amount) AS Amount, MAX(c.Sno_Retained) AS Sno_Retained
FROM #Table1 a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
)c(Sno_Retained)
GROUP BY Sale_Type;
--Option 2
SELECT Sale_Type, Amount, Sno_Retained
FROM (SELECT Sale_Type, SUM(Amount) AS Amount
FROM #Table1
GROUP BY Sale_Type
)a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
)c(Sno_Retained);
Both will return the same values and almost identical execution plans (that's a guess, I haven't checked but I imagine that we'd just move the stream aggregate).