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.

Mark_KCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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:

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx 




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark_KCAuthor Commented:
Yes, most solutions don't provide a solution for that, hence the pickle I am in.
0
dqmqCommented:
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.
0
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)

AS
BEGIN

--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 = ''
      BEGIN
            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
      end
      else
            select @cols = '[' + replace(@question_id,',','],[') + ']'

--select @cols



---------------------------------------------------------------------------------------------

--declare the list of Cities
DECLARE @qList nVARCHAR(max)
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
  BEGIN
    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
      BEGIN
        SET @myQuestion = SUBSTRING(@qList, 1, @StartPos - 1)
        SET @qList = SUBSTRING(@qList, @StartPos + 1, LEN(@qList) - @StartPos)
      END
    ELSE
      BEGIN
        SET @myQuestion = @qList
        SET @qList = ''
      END
    INSERT @tblQuestions (myQuestion) VALUES(@myQuestion)
END


---------------------------------------------------------------------------------------------



DECLARE @header nVARCHAR(max)


IF @question_id = ''
BEGIN
            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
END
ELSE
            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 + '
FROM
(
SELECT Contact_ID, Question_ID, details
FROM #AnswerDetail) AS source
PIVOT
(
      max(details)
      FOR Question_ID IN (' + @cols + ')
) as pvt
union
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)

END
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.