asked on

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.

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark_KC

ASKER

Yes, most solutions don't provide a solution for that, hence the pickle I am in.

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark_KC

ASKER

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