Write an SQL query to find for each date the
number of different products sold and their names. The sold products names for
each date should be sorted lexicographically. Return the result table ordered
by sell_date.
table name: Activities
Solution:
select a1.sell_date,count( a1.product) as num_sold,
STRING_AGG(a1.product,',') within group (order by a1.product asc) as Products
from
(
select distinct
product,sell_date from Activities
) a1
group by a1.sell_date
Output:
SQL Script:
CREATE TABLE [dbo].[Activities](
[sell_date][date] NULL,
[product][varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-05-30' AS Date), N'Headphone')
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-06-01' AS Date), N'Pencil')
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-06-02' AS Date), N'Mask')
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-05-30' AS Date), N'Basketball')
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-06-01' AS Date), N'Bible')
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-06-02' AS Date), N'Mask')
GO
INSERT [dbo].[Activities] ([sell_date], [product]) VALUES (CAST(N'2020-05-30' AS Date), N'T-Shirt')
GO