Блог

У меня есть приведенный ниже запрос, который я хочу повернуть.

  SELECT 
  tbl_track_empHours.track_empHours_hours as emphours
  ,tbl_track_empHours.track_empHours_date  as empHDate
  , tbl_track_jobInfo.track_jobInfo_jobNum AS JobNum
  , tbl_track_jobInfo.track_jobInfo_ProjName AS ProjName
  , tbl_track_jobCodes.track_jobCode_jc AS jc
  , tbl_track_jobCodes.track_jobCode_id_pk AS ts_JobCodeID
  , tbl_track_empHours.track_empHours_main_usr_id_fk
  , tbl_track_jobInfo.track_jobInfo_id_pk AS ts_JobID
 FROM   tbl_track_empHours INNER JOIN
        tbl_track_jobInfo ON  tbl_track_empHours.track_empHours_jobinfo_id_fk=tbl_track_jobInfo.track_jobInfo_id_pk INNER JOIN
       tbl_track_jobCodes ON tbl_track_empHours.track_empHours_jobCode_id_fk =   tbl_track_jobCodes.track_jobCode_id_pk
 WHERE  (tbl_track_empHours.track_empHours_main_usr_id_fk = '268') 
      AND (tbl_track_empHours.track_empHours_date  BETWEEN '05/09/2011'  AND  '05/15/2011')
 ORDER BY tbl_track_jobInfo.track_jobInfo_jobNum, tbl_track_jobCodes.track_jobCode_jc!
  

Результат выглядит следующим образом.

Я хочу повернуть столбец ’emphours’ к остальным столбцам. Сводные столбцы должны иметь даты между ’05/09/2011′ И ‘15.05.2011’

Приветствуется любая помощь.

Результат сводки

Это наверняка могло бы вам помочь.

 Select * from (Select JobNum,ProjName,jc,ts_JobCodeID,mainUsrFk,ts_JobID,
[05/09/2011]     as col1,[05/11/2011] as col2,[05/12/2011] as col3,
[05/13/2011] as col4,[05/14/2011] as col5,[05/15/2011] as col6,[05/10/2011] as col7      from (
SELECT 
            tbl_track_empHours.track_empHours_hours 
            ,tbl_track_empHours.track_empHours_date
            , tbl_track_jobInfo.track_jobInfo_jobNum AS JobNum
            , tbl_track_jobInfo.track_jobInfo_ProjName AS ProjName
            , tbl_track_jobCodes.track_jobCode_jc AS jc
            , tbl_track_jobCodes.track_jobCode_id_pk AS ts_JobCodeID
            , tbl_track_empHours.track_empHours_main_usr_id_fk as mainUsrFk
            , tbl_track_jobInfo.track_jobInfo_id_pk AS ts_JobID
        FROM   tbl_track_empHours INNER JOIN
               tbl_track_jobInfo ON tbl_track_empHours.track_empHours_jobinfo_id_fk = tbl_track_jobInfo.track_jobInfo_id_pk INNER JOIN
               tbl_track_jobCodes ON tbl_track_empHours.track_empHours_jobCode_id_fk = tbl_track_jobCodes.track_jobCode_id_pk
        WHERE  (tbl_track_empHours.track_empHours_main_usr_id_fk = '268') 
        AND (tbl_track_empHours.track_empHours_date BETWEEN '5/09/2011'  AND  '5/15/2011')  
        ) o

PIVOT(      SUM(o.track_empHours_hours)
FOR o.track_empHours_date in ([05/09/2011],[05/10/2011],[05/11/2011],[05/12/2011], 
[05/13/2011],[05/14/2011],[05/15/2011]))p ) as a