Other Recent Articles

Create Summary view of SQL Datatable

By Eranda Ketawalage on Friday, January 20, 2012 0 comments



Combine two rows into one row with same ID


Go through the following SQL table named[EmployeeSales]. Think that you have to create a summary table of monthly sales against each employee ID. 

EmpID
Year
Month
Sales
0001
2012
May
0.5
0001
2012
June
0.8
0001
2012
July
0.7
0002
2012
May
1.1
0002
2012
June
0.9
0003
2012
May
0.4
0003
2012
June
0.5
0003
2012
July
0.5
[EmployeeSales] Table


For that You can write a SQL query as follows.
In that Query I have filter sales records relate to year 2012 and sord according to EmpID




SELECT EmpID
, MAX(CASE WHEN [Month] = 'May' THEN [Sales] ELSE NULL END) AS [May]
, MAX(CASE WHEN [Month] = 'June' THEN [Sales] ELSE NULL END) AS [June]
, MAX(CASE WHEN [Month] = 'July' THEN [Sales] ELSE NULL END) AS [July]
  FROM EmployeeSales  WHERE [YEAR] = 2012

GROUP BY EmpID
ORDER BY EmpID






EmpID
May
June
July
0001
0.5
0.8
0.7
0002
1.1
0.9
Null
0003
0.4
0.5
0.5
[Result] Table

Category: SQL

0 comments:

Post a Comment

Amazon

Share this