I'm attempting to create additional columns for when a drive record has multiple other records assigned to the drive. In my attached screenshot, you can see there are three drives where all data is constant, but each has multiple incentives assigned to the drive.
Would a PIVOT function work to create an additional column for the incentive? I'm not looking for something dynamic and I would be OK with hard coding at the most 4 additional columns to have space for five incentives assigned to one drive.
What I would like my results to look like would be:
The last column is how I performed used For XML Path to accomplish it, but it's not exactly what I was looking for.
Here is my SQL statement:
Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
Acct.AccountID [AcctID],
Inc.Description [Incentive],
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 [XML_Incentives]
From
Production.[dbo].rpt_DriveMaster DM
Left Outer Join Production.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join Production.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Left Outer Join Production.[dbo].OBI_Incentives Inc on DM.DriveID=Inc.DriveID
Where
DM.DriveID in (658946,597611,651136)
I'm looking at the link provided when marked as duplicate, but is almost like reading Greek to me. I'm trying to modify my SQL to use this as a basis, but not having much luck:
Any chance on some guidance on how this works?
Select
*
From
( Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
Acct.AccountID [AcctID],
Inc.Description [Incentive1],
null [Incentive2],
null [Incentive3],
null [Incentive4],
null [Incentive5]
From
Production.[dbo].rpt_DriveMaster DM
Left Outer Join Production.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join Production.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Left Outer Join Production.[dbo].OBI_Incentives Inc on DM.DriveID=Inc.DriveID
Where
DM.DriveID in (658946,597611,651136)
) as P
Pivot (
min(P.[Incentive])
for P.[DriveID] in ([Incentive1], [Incentive2], [Incentive3])
) as PIV
