i need output this.
in no limit of shape; example : [rd],[pc],[em],[se],[pr],[cm]
my data looks :
pointer_range shape_name pcs cts value 0.30-0.39 rd 6 1.826 3503.17 0.40-0.49 rd 3 1.238 2837.62 0.50-0.69 pr 1 0.504 776.16 0.50-0.69 rd 5 2.618 8639.58 0.70-0.89 pc 5 4.109 14278.41 0.70-0.89 rd 6 4.490 17335.04 0.90-0.99 rd 2 1.803 5860.92 1.00-1.49 pc 3 3.039 9515.10 1.00-1.49 rd 7 7.807 56716.18 1.50-1.99 em 2 3.203 17080.48 1.50-1.99 rd 3 4.950 32516.03 2.00-2.99 em 2 4.528 27155.22 2.00-2.99 rd 34 75.462 703583.44 3.00-3.99 cm 1 3.024 23194.08 3.00-3.99 rd 7 21.138 329701.25 3.00-3.99 se 1 3.212 26980.80 dynamic pivot query .
in sql, getting results these fine.
[pointer] [rd_pcs] [rd_cts] [rd_val] [em_pcs] [em_cts] [em_val] [....] 0.30-0.39 1 1.20 5000.00 2.00-2.99 5 5.30 10000.00 5 6.30 5000.00 : : 3.00-3.99 2 2.50 200.00
i explain type of requirement of this. complex me. can't write query , important me in reporting side.
you can use dynamic crosstab achieve this. read article jeff moden reference.
declare @sql1 varchar(4000) = '' declare @sql2 varchar(4000) = '' declare @sql3 varchar(4000) = '' select @sql1 = 'select [pointer] = pointer_range' + char(10) select @sql2 = @sql2 + ' , [' + shape_name + '_cts] = max(case when shape_name =''' + shape_name + ''' cts end)' + char(10) + ' , [' + shape_name + '_pcs] = max(case when shape_name =''' + shape_name + ''' pcs end)' + char(10) + ' , [' + shape_name + '_val] = max(case when shape_name =''' + shape_name + ''' value end)' + char(10) from( select distinct shape_name testdata )t order shape_name select @sql3 = 'from testdata group pointer_range order pointer_range' print (@sql1 + @sql2 + @sql3) exec (@sql1 + @sql2 + @sql3) 
Comments
Post a Comment