--Creating Table

Create Table Ex
(Product varchar(10),
 Region varchar(10),
 Sales int,
 DeliveryDate Date)


--Inserting Sample Data

Insert Into Ex Values('Cheese', 'US', 500, '2012/06/01')
Insert Into Ex Values('Eggs', 'US', 1100, '2012/06/10')
Insert Into Ex Values('Eggs', 'Canada', 1000, '2012/06/09')


--Query For Your Requirement

Select x.Product, Sum(x.US_Sales), SUM(x.Canada_Sales), MAX(x.US_DeliveryDate), MAX(x.Canada_DeliveryDate) From
	(Select a.Product, a.US_Sales, a.Canada_Sales, b.US_DeliveryDate, b.Canada_DeliveryDate, Row_Number() Over (Partition By a.US_Sales Order By (Select NULL)) As rn From
		(Select Product, [US] As US_Sales, [Canada] As Canada_Sales From Ex
		 Pivot
		(Max(Sales) For Region In ([US], [Canada])) As pvt) As a
		JOIN
		(Select Product, [US] As US_DeliveryDate, [Canada] As Canada_DeliveryDate From Ex
		 Pivot
		(Max(DeliveryDate) For Region In ([US], [Canada])) As pvt) As b ON a.Product = b.Product   ) As x
Where x.rn = 1
Group By x.Product

quote:

---

Originally posted by imadiamond2016

I can't use CASE statement here as I don't know about the column list and user will be adding more and more column lists. What i presented here is just an example, actually I may have N number of columns list i.e. Counteries.

---

quote:

---

Originally posted by imadiamond2016

I can't use CASE statement here as I don't know about the column list and user will be adding more and more column lists. What i presented here is just an example, actually I may have N number of columns list i.e. Counteries.

---