Pivot may not be so simple. There are multiple columns to aggregate and the column names are dynamic. Here's what I worked up:1. Create the tablecreate table rowintocolums (PondCrop varchar(50), DOC int, ABW decimal(8,2), TargetABW decimal(8,2))
2. Populate the tableInsert into rowintocolums values('01PA02-18',28,0.87,2.6),('01PA02-18',35,1.8,3.7),('01PA02-18',42,2.4,4.8),('01PA02-18',49,3.51,5.9),('01PA02-18',56,4.6,7),('01PA02-18',63,5.51,8.1),('01PA02-18',66,6.53,8.1),('01PA02-18',73,7.42,9.2),('01PA03-18',14,0.53,0.77),('01PA03-18',21,1.14,1.5),('01PA03-18',27,0.91,1.5),('01PA03-18',34,1.67,2.6),('01PA03-18',41,2.2,3.7),...etc...
3. Setup local variables and template query commanddeclare @DOC nvarchar(100) = ',max(case when PondCrop = ''{PondCrop}'' then DOC END) as ''{PondCrop}_DOC'' 'declare @ABW nvarchar(100) = ',max(case when PondCrop = ''{PondCrop}'' then ABW END) as ''{PondCrop}_ABW'' 'declare @TargetABW nvarchar(100) = ',max(case when PondCrop = ''{PondCrop}'' then TargetABW END) as ''{PondCrop}_TargetABW'' 'declare @cols nvarchar(max)declare @sql nvarchar(max) = 'select {cols}from (select r.*, PondCrop as PondCrop_ABW, PondCrop asPondCrop_TargetAHW , rn = ROW_NUMBER() over (partition by PondCrop order by (select 1)) from rowintocolums r ) srcgroup by rn'
4. Build list of aggregated columnsselect @cols = stuff(cols,1,1,'')from ( select replace(@DOC, '{PondCrop}', PondCrop) , replace(@ABW, '{PondCrop}', PondCrop) , replace(@TargetABW, '{PondCrop}', PondCrop) from rowintocolums for xml path('')) x(cols)select @cols
5. Build final query and execute itselect @sql = replace(@sql, '{cols}', @cols)select @sqlexec(@sql)
Partial results:01PA01-18_DOC 01PA01-18_ABW 01PA01-18_TargetABW 01PA01-18_DOC 01PA01-18_ABW 01PA01-18_TargetABW ...7 0.00 0.21 7 0.00 0.2115 0.59 0.77 15 0.59 0.7722 1.24 1.50 22 1.24 1.50...