Dynamically Creating Columns for an Unknown # of Columns

Question

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:

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

Did you try writing this in a static version first before diving right into dynamic SQL? That would allow you to get the logic correct, then convert it. — Taryn, Mar 02 '15 at 16:40
Yes, the static version works. My procedure is correct, I'm now attempting to convert it to dynamic SQL to separate Incentives into individual columns. — MISNole, Mar 02 '15 at 16:41
Can you edit to show your static version? Then we can compare it to your dynamic version to see any issues. — Taryn, Mar 02 '15 at 16:51
If dynamic columns is required for ssrs reporting only, then you can try with matrix. For that each incentive should come as a row value, and then you will have to group by column in SSRS. — Jose Tuttu, Mar 11 '15 at 06:46

score 0 · Accepted Answer · answered Mar 11 '15 at 14:57

0

I recreated the table as a matrix and created a row grouping on Drive ID field. And then I created a column group on the Incentive field and set the value to: =IIF(IsNothing(Fields!Incentive.Value),True,False)

answered Mar 11 '15 at 14:57

MISNole

992
1
22
48

Dynamically Creating Columns for an Unknown # of Columns

1 Answers1