sql server - SQL 2 Dimension Pivot -

sql server - SQL 2 Dimension Pivot -

i pivot 2 tables in ms sql 2008, retrieve survey question available answers in single row.

table 1 "questions"

id text 1 gender 2 married 3 ethnicity

table 2 available "answers" each question

id questionid text 1 1 male 2 1 female 3 2 yes 4 2 no 5 3 caucasian 6 3 african/black 7 3 hispanic 8 3 asian etc.

i result of query this:

questionid questiontext ans1 ans2 ans3 ans4 1 gender male female null null 2 married yes no null null 3 ethnicity caucasian african/black hispanic asian

i have tried 10 different combinations of pivot, cte, , sub-query no luck.

i should mention there no "answer sequence" column (yet). real db has on 200 questions , 700 answers subject alter @ time, coding each value isn't practical.

your insight appreciated.

you can implement pivot function result.

if know how many answers have each question, can hard-code values similar this:

select * ( select q.id, q.text question, a.text answer, 'answer_'+cast(row_number() over(partition q.id order a.id) varchar(10)) col questions q left bring together answers on q.id = a.questionid ) src pivot ( max(answer) col in (answer_1, answer_2, answer_3, answer_4) ) piv order id;

see sql fiddle demo

but if have unknown number of answers each question, need utilize dynamic sql:

declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select distinct ',' + quotename('answer_'+cast(row_number() over(partition q.id order a.id) varchar(10))) questions q left bring together answers on q.id = a.questionid xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select id, question, ' + @cols + ' ( select q.id, q.text question, a.text answer, ''answer_''+cast(row_number() over(partition q.id order a.id) varchar(10)) col questions q left bring together answers on q.id = a.questionid ) x pivot ( max(answer) col in (' + @cols + ') ) p order id' execute(@query)

see sql fiddle demo

the result both queries is:

| id | question | answer_1 | answer_2 | answer_3 | answer_4 | --------------------------------------------------------------------------------- | 1 | gender | male | female | (null) | (null) | | 2 | married | yes | no | (null) | (null) | | 3 | ethnicity | caucasian | african/black | hispanic | asian |

sql sql-server sql-server-2008 tsql pivot