epicazo
asked on
Help with CrossTab Store Procedure in SQL2000
I am trying to create a crosstab using the following reference: http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx but I don't quite understand how to call the store procedure...
This is my query.....
SELECT TOP 100 PERCENT dbo.VW_HPPATMFL.ccGrpName, dbo.VW_HPPATMFL.ccYRMO, dbo.VW_MasterDoctorPt.PDSP CL,
dbo.VW_MasterDoctorPt.PDNA ME AS DoctorName, COUNT(*) AS TotalCount
FROM dbo.VW_HPPATMFL INNER JOIN
dbo.VW_MasterDoctorPt ON dbo.VW_HPPATMFL.PPGRPno = dbo.VW_MasterDoctorPt.PJGR P# AND
dbo.VW_HPPATMFL.PPPATno = dbo.VW_MasterDoctorPt.PJPA T#
WHERE (dbo.VW_HPPATMFL.PPTYPE = 'IP') AND (dbo.VW_HPPATMFL.PPMTCD <> 'D') AND (dbo.VW_MasterDoctorPt.PJA FTP = '2') AND
(dbo.VW_HPPATMFL.ccAdmitDT BETWEEN CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-12-31 00:00:00', 102))
GROUP BY dbo.VW_HPPATMFL.ccGrpName, dbo.VW_HPPATMFL.ccYRMO, dbo.VW_MasterDoctorPt.PDSP CL, dbo.VW_MasterDoctorPt.PDNA ME
HAVING (dbo.VW_MasterDoctorPt.PDS PCL <> 'nur') AND (dbo.VW_HPPATMFL.ccGrpName = 'ELA')
This is my output...
I want to be able to group by PDSPCL and DoctorName and crosstab total by ccYRMO....
This is my query.....
SELECT TOP 100 PERCENT dbo.VW_HPPATMFL.ccGrpName,
dbo.VW_MasterDoctorPt.PDNA
FROM dbo.VW_HPPATMFL INNER JOIN
dbo.VW_MasterDoctorPt ON dbo.VW_HPPATMFL.PPGRPno = dbo.VW_MasterDoctorPt.PJGR
dbo.VW_HPPATMFL.PPPATno = dbo.VW_MasterDoctorPt.PJPA
WHERE (dbo.VW_HPPATMFL.PPTYPE = 'IP') AND (dbo.VW_HPPATMFL.PPMTCD <> 'D') AND (dbo.VW_MasterDoctorPt.PJA
(dbo.VW_HPPATMFL.ccAdmitDT
GROUP BY dbo.VW_HPPATMFL.ccGrpName,
HAVING (dbo.VW_MasterDoctorPt.PDS
This is my output...
I want to be able to group by PDSPCL and DoctorName and crosstab total by ccYRMO....
Well considering I have been writing these for longer than Jeff, then I'll give it a go. Gimme an hour or two and I will be back.
In the meantime, what is happening - what results (or errors) are you getting ?
In the meantime, what is happening - what results (or errors) are you getting ?
ASKER
I may have the columns in wrong place, but I try....
exec USP_CrossTab
'SELECT ccYRMO, PDSPCL, PDNAME FROM dbo.VW_HPPATMFL INNER JOIN
dbo.VW_MasterDoctorPt ON dbo.VW_HPPATMFL.PPGRPno = dbo.VW_MasterDoctorPt.PJGR P# AND
dbo.VW_HPPATMFL.PPPATno = dbo.VW_MasterDoctorPt.PJPA T#',
'ccYRMO',
'COUNT(1 ELSE 0)[]',
'PDNAME',
'Count(*) as TotalCount'
Error:
Msg 207, Level 16, State 3, Line 1
Invalid column name 'PDNAME'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
exec USP_CrossTab
'SELECT ccYRMO, PDSPCL, PDNAME FROM dbo.VW_HPPATMFL INNER JOIN
dbo.VW_MasterDoctorPt ON dbo.VW_HPPATMFL.PPGRPno = dbo.VW_MasterDoctorPt.PJGR
dbo.VW_HPPATMFL.PPPATno = dbo.VW_MasterDoctorPt.PJPA
'ccYRMO',
'COUNT(1 ELSE 0)[]',
'PDNAME',
'Count(*) as TotalCount'
Error:
Msg 207, Level 16, State 3, Line 1
Invalid column name 'PDNAME'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'ccYRMO'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are awesome...
how do I sort by Jan..Dec?
exec USP_CrossTab '
SELECT HPP.ccGrpName, HPP.ccYRMM, MDP.PDSPCL, MDP.PDNAME AS DoctorName, 1 as cntr
FROM dbo.VW_HPPATMFL HPP
INNER JOIN dbo.VW_MasterDoctorPt MDP ON HPP.PPGRPno = MDP.PJGRP# AND HPP.PPPATno = MDP.PJPAT#
WHERE (HPP.PPTYPE = ''IP'')
AND (HPP.PPMTCD <> ''D'')
AND (mdp.PJAFTP = ''2'')
AND (HPP.ccAdmitDT BETWEEN CONVERT(DATETIME, ''2009-01-01 00:00:00'', 102) AND CONVERT(DATETIME, ''2010-12-31 00:00:00'', 102))
AND (MDP.PDSPCL <> ''nur'')
AND (HPP.ccGrpName = ''ELA'')
',
'ccYRMM',
'count(cntr)[]',
'PDSPCL,DoctorName'
how do I sort by Jan..Dec?
exec USP_CrossTab '
SELECT HPP.ccGrpName, HPP.ccYRMM, MDP.PDSPCL, MDP.PDNAME AS DoctorName, 1 as cntr
FROM dbo.VW_HPPATMFL HPP
INNER JOIN dbo.VW_MasterDoctorPt MDP ON HPP.PPGRPno = MDP.PJGRP# AND HPP.PPPATno = MDP.PJPAT#
WHERE (HPP.PPTYPE = ''IP'')
AND (HPP.PPMTCD <> ''D'')
AND (mdp.PJAFTP = ''2'')
AND (HPP.ccAdmitDT BETWEEN CONVERT(DATETIME, ''2009-01-01 00:00:00'', 102) AND CONVERT(DATETIME, ''2010-12-31 00:00:00'', 102))
AND (MDP.PDSPCL <> ''nur'')
AND (HPP.ccGrpName = ''ELA'')
',
'ccYRMM',
'count(cntr)[]',
'PDSPCL,DoctorName'
Sorting... Thats where you might want to check my routine - it can handle date sorting, but, you will need to cast ccYRMM as a date (simply append '-01') and then use the formatting options.
ASKER
CREATE procedure [dbo].[USP_CrossTab] (@Select varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100) = Null)
AS
set nocount on
set ansi_warnings off
declare @sql varchar(8000)
declare @Vals varchar(8000);
set @Vals = '';
set @OtherCols= isNull(', ' + @OtherCols,'')
create table #temp (PV varchar(100))
set @sql = 'select distinct convert(varchar(100),' + @PivotCol + ') as PV FROM (' + @Select + ') A'
insert into #temp
exec (@sql)
select @Vals = @Vals + ', ' +
replace(replace(@Summaries
PV + ''' THEN '),')[', ' END) as [' + PV + '')
from #Temp
order by PV
drop table #Temp
set @sql = 'select ' + @GroupBy + @OtherCols + @Vals +
' from (' + @Select + ') A GROUP BY ' + @GroupBy
exec ( @sql )
set nocount off
set ansi_warnings on