Pivoting SQL Server result set -

Pivoting SQL Server result set -

if have result returned follows:

pktestinstanceid percent1 count1 percent2 count2 1 25 1 75 3 2 50 2 50 2

is there way pivots in such format:

pktestinstanceid percent count 1 25 1 1 75 3 2 50 2 2 50 2

sorry if question totally misguided. i'm not super clear on pivoting process. help. edit should have noted percent1, count1, percent2 etc columns created based off of column (stackposition). if stackposition has 4 rows percent , count go percent4 count4. pivot or union still possible without knowledge of exact number of percent , count columns in result set.

edit 2: gets bit more complicated now...

i realize have include item in select statement (fkbandid). each bandid there stackposition stated above, bandid 96 stackposition 4, 97 stackposition 3, 98 stackposition 2 etc. want result set follows:

fkbandid pktestinstanceid band_percent band_count stackposition (not included there for visual example) 96 265 2 1 4 97 265 4 2 3 98 265 34 17 2 99 265 59 29 1

here creation of sec query looks after initial result set brought , bandid beingness selected including new bandid. pradeep's answer. http://gyazo.com/091ece1a4a1334c0f2546bccb8a6b8da

this result set looks like, can see there 4 rows beingness created each bandid. there anyway prepare , create displayed above in cross apply pradeep helped me with? or other solution? http://gyazo.com/cd19634a1201362ac3aa4546f15373c9

sorry i'm super nooby sql. allow me know if more info needed.

edit 3

(n'declare @stryearids nvarchar(100) set @stryearids = ''' + @stryearids + n''' declare @strdemocodeids nvarchar(100) set @strdemocodeids = ''' + @strdemocodeids + n''' declare @introstersetid int set @introstersetid = ' + convert(nvarchar, @introstersetid) + n' declare @intschoolid int set @intschoolid = ' + convert(nvarchar, @intschoolid) + n' declare @intteachid int set @intteachid = ' + convert(nvarchar, @intteachid) + n' declare @intgradeid int set @intgradeid = ' + convert(nvarchar, @intgradeid) + n' declare @intdeptid int set @intdeptid = ' + convert(nvarchar, @intdeptid) + n' declare @intcourseid int set @intcourseid = ' + convert(nvarchar, @intcourseid) + n' declare @intperiodid int set @intperiodid = ' + convert(nvarchar, @intperiodid) + n' declare @strtestinstid nvarchar(100) set @strtestinstid = ''' + @strtestinstid + n''' declare @inttesttypeid int set @inttesttypeid = ' + convert(nvarchar, @inttesttypeid) + n' declare @strsubids nvarchar(100) set @strsubids = ''' + @strsubids + n''' declare @bitisstrand bit set @bitisstrand = ' + convert(nvarchar, @bitisstrand) + n' declare @intperflevelreportid int set @intperflevelreportid = ' + convert(nvarchar, @intperflevelreportid) + n' declare @temptests table (id int) insert @temptests exec spgetstudenttests_local_mti @strdemocodeids, @stryearids, @intschoolid, @intteachid, @intgradeid, @introstersetid, @intperiodid, @intdeptid, @intcourseid, @strtestinstid, @inttesttypeid declare @tempsubs table (id int) if @bitisstrand = 1 begin insert @tempsubs select pktestsubjectid mm_test_subjects fkcststrandid /*= @intsubid*/ in (select number itot(@strsubids, n'','')) , fktesttypeid = @inttesttypeid end else begin insert @tempsubs select number itot(@strsubids, n'','')--values (@intsubid) end select bands.pkperformancelevelreportbandid ''fkbandid'', testinstances.pktestinstanceid ''testinstanceid'', studentscores_subject.fktest_subjectid ''testsubjectid'', ' + @cols + n'into ##temptable studentscores_subject inner bring together studenttests on studentscores_subject.fkstudenttestid = studenttests.pkstudenttestid inner bring together testinstances on testinstances.pktestinstanceid = studenttests.fktestinstanceid inner bring together cahsee_testperiods on cahsee_testperiods.pktestperiodid = testinstances.fktestperiodid inner bring together performancelevelreportbands bands on bands.fkperformancelevelreportid = @intperflevelreportid left bring together mmars_web_testinfo_california.dbo.perflevelreportbandcutscores cutscores on cutscores.fkperformancelevelreportbandid = bands.pkperformancelevelreportbandid , cutscores.fkgradeid = @intgradeid , cutscores.fktestsubjectid in (select id @tempsubs) inner bring together perflevelreportbandcomponents bandcomponents on bandcomponents.fkperformancelevelreportbandid = bands.pkperformancelevelreportbandid and((bandcomponents.scorevalue = studentscores_subject.scorevalue) or ((cast(studentscores_subject.scorevalue int) between bandcomponents.minscore , bandcomponents.maxscore) or (cast(studentscores_subject.scorevalue int) between cutscores.minscore , cutscores.maxscore)) ) right bring together mm_schoolyears on mm_schoolyears.pkschoolyearid = testinstances.fkschoolyearid mm_schoolyears.pkschoolyearid in (select number itot(@stryearids, n'','')) , bands.fkperformancelevelreportid = @intperflevelreportid , studentscores_subject.fkstudenttestid in (select id @temptests) , studentscores_subject.fkscoretypeid = bandcomponents.fkscoretypeid , studentscores_subject.fktest_subjectid in (select id @tempsubs) --and((bandcomponents.scorevalue = studentscores_subject.scorevalue) or --(studentscores_subject.scorevalue between bandcomponents.minscore , bandcomponents.maxscore) or --(studentscores_subject.scorevalue between cutscores.minscore , cutscores.maxscore)) grouping bands.pkperformancelevelreportbandid, testinstances.pktestinstanceid, studentscores_subject.fktest_subjectid order bands.pkperformancelevelreportbandid, testinstances.pktestinstanceid, studentscores_subject.fktest_subjectid')

the @cols variable follows:

declare @cols nvarchar(max) select @cols = stuff(( select distinct top 100 percent ', sum(case when bands.stackposition = ''' + str(b.stackposition, 1) + ''' 1 else 0 end) * 100.0/ case when count(pkstudentscoreid) = 0 1 else count(pkstudentscoreid) end ''percent_' + str(b.stackposition, 1) + ''', sum(case when bands.stackposition = ''' + str(b.stackposition, 1) + ''' 1 else 0 end) ''count_' + str(b.stackposition, 1) + '''' performancelevelreportbands b b.fkperformancelevelreportid = @intperflevelreportid order ', sum(case when bands.stackposition = ''' + str(b.stackposition, 1) + ''' 1 else 0 end) * 100.0/ case when count(pkstudentscoreid) = 0 1 else count(pkstudentscoreid) end ''percent_' + str(b.stackposition, 1) + ''', sum(case when bands.stackposition = ''' + str(b.stackposition, 1) + ''' 1 else 0 end) ''count_' + str(b.stackposition, 1) + '''' xml path('') ), 1, 2, '')

what looking unpivot not pivot

create table #piv ( pktestinstanceid int, percent1 int, count1 int, percent2 int, count2 int ) insert #piv values ( 1,25,1,75,3), (2,50,2,50,2) select pktestinstanceid, [percent], [count] #piv p cross apply ( values (percent1,count1), (percent2,count2)) x([percent], [count]);

if want work dynamically below code should help you. illustration have kept no. of stackposition rows 2 u can alter , check

declare @stackposition int=2, @sql nvarchar(max), @cnt int=1 set @sql =' select pktestinstanceid, [percent], [count] #piv p cross apply ( values ' while @cnt <= @stackposition begin set @sql+='([percent' + convert(varchar(10), @cnt)+ '],[count' + convert(varchar(10), @cnt) + ']),' set @cnt+=1 end set @sql= left(@sql, len(@sql) - 1) set @sql+=') x([percent], [count])' exec sp_executesql @sql

output

pktestinstanceid percent count ---------------- ------- ----- 1 25 1 1 75 3 2 50 2 2 50 2

sql sql-server pivot