Link to home
Start Free TrialLog in
Avatar of rajesh75
rajesh75

asked on

Crosstab Query??????HELP

hi,

 I have 2 queries like this

1)SELECT  convert(varchar(20),S.Day,103)
     ,S.ShiftId
     ,A.Forename +''+ A.Surname  -- Doctor Name
     ,S.Comment
FROM      Applicants A
     ,WebSatRotaschedule S
     ,WebSatRotaDetails D
     ,WebSatRota R
WHERE     S.Day BETWEEN CONVERT(DATETIME, '02/21/2003') and CONVERT(DATETIME, '02/28/2003')
     AND     S.RotaId  = D.RotaId    
     AND     S.ShiftId  = D.ShiftId      
     AND     D.RotaId  = R.RotaId  
     AND     R.RotaId  = 6      
     AND     A.ApplicantID = S.DoctorID    
ORDER BY  S.Day


returns the following data:


---------------------------------------------
ShiftId          ShiftName     Description
----------------------------------------------
1          aaa          aaa    
2          bbb          bbb    
3          ccc          ccc    



2)SELECT       ShiftId,
          ShiftName,
          Description
FROM           WebSatRotaDetails
WHERE          RotaId                = 6
ORDER BY      ShiftName


returns the following data:
----------------------------------------------------------
Day          ShiftId          CustomerName     Comment
-------------------------------------------------------
24/02/2003     2          John           .....
24/02/2003     3          Tony          ....
27/02/2003     1          John          ....
27/02/2003     3          John          .....


I want to somehow combine the above 2 queries and get this as the result...using a stored procedure

-------------------------------------------------------
Day          aaa          bbb          ccc
---------------------------------------------------------
24/02/2003               John          Tony
27/02/2003     John                    John
----------------------------------------------------------

rows as columns and neat like a grid...Is it possible???I am using SQL server 6.5...thanks in advance..
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

I don't see any way to get the ShiftNames into the SQL except thru dynamic execution.  In fact, you'll also need a cursor to build the statement to execute.  Maybe something like this (I have NOT tested this code yet):

DECLARE @sql VARCHAR(4000)
DECLARE shiftCsr CURSOR FOR
SELECT DISTINCT S.ShiftId, D.ShiftName
FROM WebSatRotaschedule S
    ,WebSatRotaDetails D
WHERE     S.Day BETWEEN CONVERT(DATETIME, '02/21/2003') and CONVERT(DATETIME, '02/28/2003')
    AND     S.RotaId  = 6      
    AND     D.RotaId  = 6      
    AND     S.RotaId  = D.RotaId    
    AND     S.ShiftId  = D.ShiftId      
ORDER BY S.ShiftId
FOR READ ONLY
DECLARE @ShiftId INT  --change to match orig column on table
DECLARE @ShiftName VARCHAR(40)  --change to match orig column on table

SET @sql = 'SELECT CONVERT(VARCHAR(20), S.Day, 103) AS Day  --could shorten length of VARCHAR?'
OPEN shiftCsr
FETCH NEXT FROM shiftCsr INTO @ShiftId, @ShiftName
WHILE @@FETCH_STATUS <> 0
BEGIN
     SET @sql = @sql +
     ', MAX(CASE WHEN D.ShiftName = ''' + @ShiftName + ''' THEN A.Forename +''''+ A.Surname ELSE '''' END) AS ''' + @ShiftName + ''''
END --WHILE

SET @sql = @sql +
'FROM      Applicants A' +
'    ,WebSatRotaschedule S' +
'    ,WebSatRotaDetails D' +
'    ,WebSatRota R' +
'WHERE     S.Day BETWEEN CONVERT(DATETIME, ''02/21/2003'') and CONVERT(DATETIME, ''02/28/2003'')' +
'    AND     S.RotaId  = D.RotaId     ' +
'    AND     S.ShiftId  = D.ShiftId      ' +
'    AND     D.RotaId  = R.RotaId  ' +
'    AND     R.RotaId  = 6      ' +
'    AND     A.ApplicantID = S.DoctorID     ' +
'GROUP BY CONVERT(VARCHAR(20), S.Day, 103)' +
'ORDER BY CONVERT(VARCHAR(20), S.Day, 103)'
PRINT @sql
--EXEC(@sql)
ASKER CERTIFIED SOLUTION
Avatar of ispaleny
ispaleny
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial