First, I'm relatively new to SQL, so please bare with me.
I'm working on a project for a report where already a query is displaying information from some tables.
prod_id | name | sale_price |
1 | chair | 2
1 | chair | 4
2 | table | 5
2 | table | 10
,COUNT(*) as totalRecords
,AVG(sale_price) as Priceaverage
group by name, prod_id
Declare @business_table table (prod_id int,name varchar(50),sale_price money) Insert Into @business_table values (1,'chair',2), (1,'chair',4), (2,'table',5), (2,'table',10) ;with cteBase as ( Select name ,prod_id ,totalRecords = COUNT(*) ,Priceaverage = AVG(sale_price) From @business_table A Group By name, prod_id ) Select A.* ,B.Detail From cteBase A Cross Apply (Select Detail=Stuff((Select ',' + cast(sale_price as varchar(25)) From @business_table Where prod_id=A.prod_id For XML Path ('')),1,1,'') ) B
name prod_id totalRecords Priceaverage Detail chair 1 2 3.00 2.00,4.00 table 2 2 7.50 5.00,10.00