?
Solved

Crosstab Query??????HELP

Posted on 2003-02-27
2
Medium Priority
?
189 Views
Last Modified: 2006-11-17
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
Comment
Question by:rajesh75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8036524
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
 
LVL 13

Accepted Solution

by:
ispaleny earned 1200 total points
ID: 8096295
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question