MAhmad February 2016

SQL Query for Max records for Multiple dates

Hi I am looking for an SQL Query to select max records based on specific criteria for customers. the current situation is as below

Customer            Activity            Date
1                   1                   01/02/2015
1                   1                   02/02/2015
1                   1                   03/02/2015
1                   2                   05/02/2015
1                   2                   07/02/2015
1                   3                   06/02/2015
2                   1                   01/01/2015
2                   2                   05/01/2015
2                   3                   11/01/2015

My Required Resultset should look something like

Customer        Activity1Maxdate            Activity2Maxdate        Activity3MaxDate
1               03/02/2015                  07/02/2015              06/02/2015
2               01/01/2015                  05/01/2015              11/01/2015

The activities here are limited to 4 or 5 therefore ther e is no requirement of dynamic column making

Any Help Please Cheers

Answers


Zohar Peled February 2016

What you are asking is called a Pivot table.
Sql server has a relational operator for pivot and you can read the documentation for it here.

Try this:

SELECT Customer, 
  [0] As Activity1Maxdate
, [1] As Activity2Maxdate
, [2] As Activity3Maxdate
, [3] As Activity4Maxdate
, [4] As Activity5Maxdate
FROM YourTable
PIVOT
(
MAX(Date)
FOR Activity IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Post Status

Asked in February 2016
Viewed 3,299 times
Voted 9
Answered 1 times

Search




Leave an answer