Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2013-12-16 : 04:21:58
|
| Hi All,In the below query i would like to convert the startdate column as a column i.e i want to display all the days of the month as column and their respective data as a row.WITH DaysOfMonthCTE AS ( SELECT DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 0) AS StartDate , DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 1) AS EndDate UNION ALL SELECT DATEADD(day, 1, StartDate) , DATEADD(DAY, 1, EndDate) FROM DaysOfMonthCTE WHERE EndDate < DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) ) SELECT scores.FirstName , DaysOfMonthCTE.StartDate , count(student.shiftid) AS NoOfShifts FROM Tbl_Shifts AS student INNER JOIN Tbl_Employee scores ON student.EmployeeId = scores.EmployeeId RIGHT OUTER JOIN DaysOfMonthCTE ON Student.ShiftFromDateTime >= DaysOfMonthCTE.StartDate AND Student.ShiftFromDateTime < DaysOfMonthCTE.EndDate GROUP BY scores.FirstName , DaysOfMonthCTE.StartDatePlease helpmohammad.javeed.ahmed@gmail.com |
|
|
Jayam.cnu
Starting Member
45 Posts |
Posted - 2013-12-16 : 05:09:39
|
| Hi ,We can get the result by using Pivot with Dynamic SQL. 1. Pivot is required to get the columns as rows and rows as columns (Startdate column data as Columns ) . 2. Dynamic SQL is required to get the n number of columns depends on the distinct StartDate colums data. Thanks |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-12-16 : 06:37:14
|
| Thank you for the inputs.I will try it nowmohammad.javeed.ahmed@gmail.com |
|
|
|
|
|
|
|
|
Convert row to column