what I'm hoping to accomplish is to dynamically create columns as needed within a stored procedure. My issues lies in the fact that a drive may no incentives, 1 incentive or multiple (2,3,4.etc) incentives assigned to the drive. Screen shot below for example data:
I initially used the FOR XML Path to concatenate records:
Stuff ((Select ', ' + isnull(EM.Description,'')
From EquipmentMaster EM
Inner Join EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
Where ED.DriveID = DM.DriveID
For XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as [Incentives]
But the end users have requested the incentives be in separate columns. I am trying to reuse some code I found here: Stack Overflow link
But I cannot get it to work. Was hoping to get some help to make this procedure work.
Here is the procedure code:
declare
@startDate datetime,
@endDate datetime,
@RegID varchar(8000),
@OrgID varchar(8000),
@SubID varchar(8000),
@COID varchar(8000),
@RecID varchar(8000),
@StatusID varchar(8000),
@Cols nvarchar(max),
@Incentives nvarchar(max)
Select @Cols = Stuff ((Select ', ' + isnull(EM.Description,'')
From EquipmentMaster EM
Inner Join EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
--Where ED.DriveID = DM.DriveID
For XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '')
Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
Stuff ((Select ', ' + isnull(EM.Description,'')
From EquipmentMaster EM
Inner Join EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
Where ED.DriveID = DM.DriveID
For XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as [Incentives]
From
rpt_DriveMaster DM
Left Outer Join rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Left Outer Join IDViewRecruiters Rec on Acct.RecruiterID=Rec.CodeID
Inner Join rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Where
DM.StatusID <>5
And DM.FromDateTime Between '03/01/2015' AND '03/02/2015'
Here is my original procedure that works to bring all incentives into one column (and used by SSRS to display a report):
Create Procedure Hema_IncentiveReport
@startDate datetime,
@endDate datetime,
@RegID varchar(8000),
@OrgID varchar(8000),
@SubID varchar(8000),
@COID varchar(8000),
@RecID varchar(8000),
@StatusID varchar(8000)
As
Begin
Select Item Into #Region From OBAPPS.[dbo].DelimitedSplit8K(@RegID,',');
Select Item Into #OrgCenter From OBAPPS.[dbo].DelimitedSplit8k(@OrgID,',');
Select Item Into #SubCenter From OBAPPS.[dbo].DelimitedSplit8k(@SubID,',');
Select Item Into #CollectionOp From OBAPPS.[dbo].DelimitedSplit8k(@COID,',');
Select Item Into #Recruiter From OBAPPS.[dbo].DelimitedSplit8k(@RecID,',');
Select Item Into #DriveStatus From OBAPPS.[dbo].DelimitedSplit8k(@StatusID,',');
Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic],
Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
DT.EarlyShiftStart [EarlyShiftStart],
DT.LateShiftEnd [LateShiftEnd],
DrStat.StatusText [DriveStatus],
Acct.AccountID [AcctID],
Acct.InternalName [AcctInternalName],
Acct.ExternalID [ExternalID],
AD.Addr1 [Addr1],
AD.Addr2 [Addr2],
AD.City [City],
AD.State [State],
AD.Zip [Zip],
CO.CodeID [CO_ID],
CO.Description [CO_Desc],
Org.CodeID [Org_ID],
Org.Description [Org_Desc],
Rec.CodeID [Rec_ID],
Rec.Description [Rec_Desc],
Sub.CodeID [Sub_ID],
Sub.Description [Sub_Desc],
Reg.CodeID [Reg_ID],
Reg.Description [Reg_Desc],
DPaCT.ProcedureProjection [Proc_Proj],
DPaCT.ProductProjection [Prod_Proj],
DPaCT.ProceduresPerformed [Proc_Perf],
DPaCT.ProductsCollected [Prod_Coll],
DPaCT.QNS [QNS],
DPaCT.FTD [FTD],
(isnull(DPaCT.ProductsCollected,0))-(isnull(DPaCT.ProceduresPerformed,0)) [Alyx],
Stuff ((Select Distinct ', ' + isnull(MSM.ShortDesc,'')
From Production.[dbo].rpt_DriveShiftDetail DSD
Inner Join Production.[dbo].rpt_DriveShiftMobileDetail DSMD On DSMD.ShiftID = DSD.ShiftID
Inner Join Production.[dbo].rpt_MobileSetupMaster MSM
On MSM.MobileType = 1 and MSM.MobileID = DSMD.MobileID
Where DSD.DriveID = DM.DriveID
For XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as [Mobile_Short_Desc],
Stuff ((Select ', ' + isnull(EM.Description,'')
From Production.[dbo].EquipmentMaster EM
Inner Join Production.[dbo].EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
Where ED.DriveID = DM.DriveID
For XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as [Incentives]
From
Production.[dbo].rpt_DriveMaster DM
Left Outer Join Production.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join Production.[dbo].DriveTimes DT on DM.DriveID=DT.DriveID
Left Outer Join Production.[dbo].rpt_AddressDetail AD on DM.SiteID=AD.SiteID
Left Outer Join Production.[dbo].IDViewRecruiters Rec on Acct.RecruiterID=Rec.CodeID
Inner Join Production.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Inner Join Production.[dbo].IDViewCollectionOp CO on CD.CenterID=CO.CodeID
Inner Join Production.[dbo].IDViewRegion Reg on CD.Region=Reg.CodeID
Inner Join Production.[dbo].IDViewOrgCenter Org on CD.OrgCenter=Org.CodeID
Inner Join Production.[dbo].IDViewOrgSubCenter Sub on CD.OrgSubCenter=Sub.CodeID
Inner Join Production.[dbo].rpt_DriveStatusDef DrStat on DM.StatusID=DrStat.StatusID
Inner Join Production.[dbo].DriveProjectionandCollectedTotals DPaCT on DM.DriveID=DPaCT.DriveID
Inner Join #Region As Region On Reg.CodeID = Region.Item
Inner Join #OrgCenter As OrgCenter on Org.CodeID = OrgCenter.Item
Inner Join #SubCenter As SubCenter on Sub.CodeID = SubCenter.Item
Inner Join #CollectionOp as CollectionOp on CO.CodeID = CollectionOp.Item
Left Outer Join #Recruiter as Recruiter on Rec.CodeID = Recruiter.Item
Inner Join #DriveStatus as DriveStatus on DrStat.StatusID = DriveStatus.Item
Where
DM.StatusID <>5
And DM.FromDateTime Between @startDate AND @endDate
