Pivot Table SQL 2008

I found many resources regarding Pivot tables yet still have not found one that fits my situation.  I have constructed a query that would provide the following data.

ID      Name      address        answer             Question               QuestionID
1      client1      caddress1      answer1     Question Text            1      
28      client2      c2address1     answer1    Question Text            1
57      client3      c3address1     answer1    Question Text            1      
73      client4      c4address1     answer1    Question Text            1
77      client5      c5address1     answer1    Question Text            1
1      client1      caddress1      answer2     Question Text2      5      
28      client2      c2address1     answer2    Question Text2           5
57      client3      c3address1     answer2    Question Text2      5      
73      client4      c4address1     answer2    Question Text2      5
77      client5      c5address1     answer2    Question Text2           5

I am trying to display it like this:

ID      Name      address        Question Text    Question Text2  Question Text3
1      client1      caddress1     answer1            answer2              answer3
28      client2      c2address1     answer1            answer2              answer3

The columns with the 'Question Text' will need to be dynamic, in other words their could be 2 of them or there could be 10, based on the users query.

Currently using MS SQL 2008 and the data will be displayed within a standard ASP page and not sure if I could do this with a Stored Procedure or some other means.

Keep in mind the users will have the ability to select the questions (via QuestionID) that they need to see the results of.

Any help is appreciated.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

dqmqConnect With a Mentor Commented:
You want Question Text for the column header? Yikes!

Anyway, since the number of columns is variable as is the column headings, that requires a dynamic pivot query.   You can find many examples with a Google search, like here:


Mark_KCAuthor Commented:
Yes, most solutions don't provide a solution for that, hence the pickle I am in.
dqmqConnect With a Mentor Commented:
Try one of the dynamic pivot techniques.  They do work! The only challenge in my mind is using the question text for the column header.  That's could be a pretty wide column.   But, if you can live with "Question 1", "Question 2", etc for the column header, you will have no problems.
Mark_KCAuthor Commented:
Took some work, but finally put together a SP that did the job.  Not elegant and with some limitations, but solved the problem.

[getAnswerPivot6] @contact_id varchar(200), @question_id varchar(200)


--declare @contact_id varchar(200)
--declare @question_id varchar(200)

--select @contact_id = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
--select @question_id = ''

declare @whereclause varchar(8000)

select @whereclause = case when @contact_id <> '' then ' where contact_id in ( 0,' + CAST(@contact_id as varchar(100)) + ') ' else ' where 1=1 ' end

--drop table #AnswerDetail

select b.Question_Text, a.Contact_ID,  cast(a.Answer_ID as varchar(1000)) + '**mf61172**' + isnull(CONVERT(nvarchar(30), a.Answer_Date_Start, 101),'') + '**mf61172**' + isnull(a.Answer_Comment,'') + '**mf61172**' + isnull(a.Answer_Bates_Pages,'') as details, b.Question_ID
into #AnswerDetail
from tblContacts_Answers a
left outer join tblQuestions b
on a.Question_ID=b.Question_ID
left outer join tblContacts c
on a.Contact_ID=c.contact_id

--SELECT * FROM #AnswerDetail order by question_id

DECLARE @cols varchar(8000)

IF @question_id = ''
            SELECT @COLS = COALESCE(@COLS + ',[' + CONVERT(VARCHAR(10),A.question_id,101) + ']',
            '[' + CONVERT(VARCHAR(10),A.question_id,101) + ']')
            FROM (SELECT DISTINCT Question_Text AS Question_Text, question_id
            FROM #AnswerDetail ) A
            ORDER BY A.question_id
            select @cols = '[' + replace(@question_id,',','],[') + ']'

--select @cols


--declare the list of Cities
SET @qList = @question_id

--declare the delimeter between each myQuestion
DECLARE @Delimeter char(1)
SET @Delimeter = ','

--Parse the string and insert each myQuestion into the @tblQuestions table
DECLARE @tblQuestions TABLE(myQuestion varchar(50))
DECLARE @myQuestion varchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@qList) > 0
    SET @StartPos = CHARINDEX(@Delimeter, @qList)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@qList) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
        SET @myQuestion = SUBSTRING(@qList, 1, @StartPos - 1)
        SET @qList = SUBSTRING(@qList, @StartPos + 1, LEN(@qList) - @StartPos)
        SET @myQuestion = @qList
        SET @qList = ''
    INSERT @tblQuestions (myQuestion) VALUES(@myQuestion)


DECLARE @header nVARCHAR(max)

IF @question_id = ''
            SELECT @header = COALESCE(@header + ',''' + A.Question_Text + '''',
            '''' + A.Question_Text + '''')
            FROM (SELECT DISTINCT Question_Text AS Question_Text, question_id
            FROM #AnswerDetail) A
            ORDER BY A.question_id
            SELECT @header = COALESCE(@header + ',''' + A.Question_Text + '''',
            '''' + A.Question_Text + '''')
            FROM (SELECT DISTINCT Question_Text AS Question_Text, question_id
            FROM #AnswerDetail where question_id in (select myquestion from @tblQuestions)) A
            ORDER BY A.question_id

--select @header

--drop table #t1

DECLARE @query nVARCHAR(max)

SET @query = 'select * into #mytemp from (SELECT Contact_ID, ' + @cols + '
SELECT Contact_ID, Question_ID, details
FROM #AnswerDetail) AS source
      FOR Question_ID IN (' + @cols + ')
) as pvt
select 0, ' + @header + '
) jj ' + @whereclause + ' order by contact_id;'

SET @query = @query + CHAR(13) + CHAR(10) + 'select Contact_Case_Num,Contact_Case_Name,Contact_Phone,Contact_Address1,Contact_City,Contact_State,Contact_Zip,Contact_State_Loss,Contact_Injury,Contact_Date_Injury,Contact_Date_Usage,Contact_Source,Contact_Date_Contract,a.* from #mytemp a left outer join dbo.tblContacts b on a.contact_id = b.contact_id order by a.contact_id;'

EXECUTE( @query)

All Courses

From novice to tech pro — start learning today.