SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) 
 Jan 23 2020 21:00:04 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE [dbo].[products](
	[products_id] [nvarchar](50) NOT NULL,
	[products_category_id] [nvarchar](50) NOT NULL,
	[products_title] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[products_spec](
	[spec_id] [nvarchar](50) NOT NULL,
	[products_id] [nvarchar](50) NOT NULL,
	[spec_title] [nvarchar](50) NOT NULL,
	[spec_price] [int] NOT NULL)
INSERT INTO [dbo].[products] ([products_id], [products_category_id], [products_title]) VALUES (N'1', N'1', N'XX商品')

INSERT INTO [dbo].[products] ([products_id], [products_category_id], [products_title]) VALUES (N'2', N'1', N'OO商品')

INSERT INTO [dbo].[products] ([products_id], [products_category_id], [products_title]) VALUES (N'3', N'1', N'AA商品')

INSERT INTO [dbo].[products] ([products_id], [products_category_id], [products_title]) VALUES (N'4', N'2', N'PP商品')

INSERT INTO [dbo].[products] ([products_id], [products_category_id], [products_title]) VALUES (N'5', N'2', N'UU商品')

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'1', N'1', N'規格1', 1500)

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'2', N'1', N'規格2', 1200)

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'3', N'2', N'規格1', 800)

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'4', N'2', N'規格2', 500)

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'5', N'3', N'規格1', 1200)

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'6', N'4', N'規格1', 500)

INSERT INTO [dbo].[products_spec] ([spec_id], [products_id], [spec_title], [spec_price]) VALUES (N'7', N'5', N'規格1', 300)

12 rows affected

SELECT T.[products_category_id] AS category_id,
STRING_AGG(T.[products_id],',') AS products_id,
STRING_AGG(T.spec_price,',') AS spec_price 
FROM (SELECT  ROW_NUMBER() OVER(PARTITION BY P1.[products_category_id] ,P1.[products_id] ORDER BY PS.spec_price ) AS RNO ,
P1.[products_category_id] ,P1.[products_id] ,PS.spec_price
  FROM [dbo].[products] P1
  LEFT JOIN [dbo].[products_spec] PS
  ON P1.products_id=PS.products_id ) T WHERE T.RNO=1
GROUP BY T.[products_category_id]  ORDER BY T.[products_category_id] 


category_id
products_id
spec_price


1
1,2,3
1200,500,1200

2
4,5
500,300