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..
I have 2 queries like this
1)SELECT convert(varchar(20),S.Day,
,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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)