sql - Creating Dynamic Column Names from Dynamic Row Number -

about me not sql dba programmer. pretending one. so, when comes designing complex queries beginner/middle of road depending on is.

so far have come example show do.

tried researching , have gotten far.

what want rownumber dynamic based off 2 factors recid, groupeddataid.

without getting whicked doing. hoping simple example illustrate enough.

factors number of roles unknown. recid can have multiple groupeddataids. groupeddataids can have 1 record.

output desired

pcrid,groupeddataid, answertext, question 1   1   driver, driver  role1 1   2   driver, driver  role2 1   33  driver, driver  role3 2   48  driver, driver  role1 2   55  driver, driver  role2 3   32  driver, driver  role1 3   33  driver, driver  role2 4   109 driver, driver  role1 

example created

create table #example (  recid int,  groupeddataid int,  question varchar(50),  answertext varchar(100) ) insert #example (recid, groupeddataid, question, answertext) select 1, 1, 'role', 'driver, driver' union select 1, 2, 'role', 'driver, driver' union select 1, 33, 'role', 'driver, driver' union select 2, 55, 'role', 'driver, driver' union select 2, 48, 'role', 'driver, driver' union select 3, 32, 'role', 'driver, driver' union<br> select 3, 33, 'role', 'driver, driver' union select 4, 109, 'role', 'driver, driver'  select recid , groupeddataid , answertext , question = 'role' + cast(row_number() on (order (select recid), (select groupeddataid)) varchar(max)) #example  drop table #example <p> 

this getting. notice role # doesn't start on over new recid,groupeddataid grouping. hoping order didn't

pcrid,groupeddataid, answertext, question 1       1   driver, driver  role1 1   2   driver, driver  role2 1   33  driver, driver  role3 2   48  driver, driver  role4 2   55  driver, driver  role5 3   32  driver, driver  role6 3   33  driver, driver  role7 4   109 driver, driver  role8 

appreciated...i have spent day pretty getting myself point data looks above example.

:)

your current query close, missing partition by on row_number() windowing function. allow number reset 1 each time recid changes:

select recid   , groupeddataid   , answertext   , question = 'role' + cast(row_number() on (partition recid                                                  order recid, groupeddataid) varchar(max))  example; 

see sql fiddle demo