c# - Show data like Pivot in Grid or any other control -

c# - Show data like Pivot in Grid or any other control -

i want show info pivot grid. right showing info following. please see next image or click on link. http://screencast.com/t/cwegy0vi

but want above info following: http://screencast.com/t/ztb2wk4cdmb

any suggestion how accomplish this. starting point. should utilize repeater?

without seeing table structure, etc. hard give exact answer. can suggest how perform in sql. have previous questions tagged sql server guessing that.

you can using both unpivot , pivot:

;with unpiv ( select activity, work, location+'_'+col col, value ( select activity, work, cast(assignedtasks varchar(50)) assignedtasks, cast(completedtasks varchar(50)) achievedtasks, location yourtable ) src unpivot ( value col in (assignedtasks, achievedtasks) ) unpiv ), piv ( select activity, work, london_assignedtasks, london_achievedtasks, geneva_assignedtasks, geneva_achievedtasks, row_number() over(partition activity order activity, work) rn unpiv pivot ( max(value) col in (london_assignedtasks, london_achievedtasks, geneva_assignedtasks, geneva_achievedtasks) ) piv ) select case when rn = 1 activity else '' end activity, work, london_assignedtasks, london_achievedtasks, geneva_assignedtasks, geneva_achievedtasks piv

see sql fiddle demo.

the result is:

| activity | work | london_assignedtasks | london_achievedtasks | geneva_assignedtasks | geneva_achievedtasks | ------------------------------------------------------------------------------------------------------------------- | activity 1 | task 1 | 10 | 8 | 1 | 1 | | | task 2 | 15 | 15 | 100 | 25 | | activity 2 | task 1 | 5 | 5 | 0 | 0 | | | task 2 | 0 | 0 | 2 | 2 | | activity 3 | task 1 | 10 | 10 | 50 | 40 |

edit #1, if have unknown or dynamic number of locations can utilize dynamic sql homecoming result:

declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select ',' + quotename(location+'_'+t.tasks) yourtable cross apply ( select 'assignedtasks' tasks union select 'achievedtasks' ) t grouping location, tasks order location xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = ';with unpiv ( select activity, work, location+''_''+col col, value ( select activity, work, cast(assignedtasks varchar(50)) assignedtasks, cast(completedtasks varchar(50)) achievedtasks, location yourtable ) src unpivot ( value col in (assignedtasks, achievedtasks) ) unpiv ), piv ( select activity, work, row_number() over(partition activity order activity, work) rn, '+@cols+' unpiv pivot ( max(value) col in ('+@cols+') ) piv ) select case when rn = 1 activity else '''' end activity, work, '+@cols+' piv' execute(@query)

see sql fiddle demo

c# asp.net sql sql-server-2008 gridview