• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

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..
0
rajesh75
Asked:
rajesh75
1 Solution
 
Scott PletcherSenior DBACommented:
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)
0
 
ispalenyCommented:
I know, varchar has low size limit in 6.5 version, but that is all.
I expect I can:
1, select identity into
2, unlimited concatenate in exec
3, use max(case when ...)
4, use derived table
All these things are not needed, only code would be longer.

SELECT  OrderID=identity(int,1,1),ShiftID,ShiftName into #Shifts
FROM           WebSatRotaDetails
WHERE          RotaId= 6
ORDER BY      ShiftName
declare @Name1 varchar(200),@Name2 varchar(200),@Name3 varchar(200),@Name4 varchar(200),@Name5 varchar(200)
       ,@Name6 varchar(200),@Name7 varchar(200),@Name8 varchar(200),@Name9 varchar(200),@Name10 varchar(200)
select  @Name1='',@Name2='',@Name3='',@Name4='',@Name5='',@Name6='',@Name7='',@Name8='',@Name9='',@Name10=''
select @Name1 =' "'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=1
select @Name2 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=2
select @Name3 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=3
select @Name4 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=4
select @Name5 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=5
select @Name6 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=6
select @Name7 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=7
select @Name8 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=8
select @Name9 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=9
select @Name10=',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=10
exec('select "Day",'+@Name1+@Name2+@Name3+@Name4+@Name5+@Name6+@Name7+@Name8+@Name9+@Name10
+' from (
SELECT  "Day"=convert(varchar(20),S.Day,103)
    ,S.ShiftId
    ,"Name"=A.Forename +'' ''+ A.Surname
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    
) x GROUP BY "Day" ORDER BY "Day"')

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now