Pivot Cross Tab for Text Results

Question

Post reply

Pivot Cross Tab for Text Results

Felipe Brito

Valued Member

Points: 68
More actions
December 13, 2006 at 9:06 am

#115604

This is driving me nuts, all pivot/crosstab procedures I have found are related to aggregate functions.
My sql query is:
SELECT TOP 100 PERCENT dbo.ENC_Questions.Question, dbo.ENC_ANS.Answer, dbo.ENC_ANS.SessionID
FROM dbo.ENC_Questions INNER JOIN
dbo.ENC_ANS ON dbo.ENC_ANS.IDQuestion = dbo.ENC_AND.IDQuestion
WHERE (dbo.ENC_Questions.IDSurvey = 2155)
ORDER BY dbo.ENC_Questions.Order
So the return results are de ClientID (Session), the question asked, and the answer given by the client.

I want to pivot the table with no count, max, avr, or anything, just list the answers of every session ID as:

SessionID, Question1, Question2, QuestionN
1             Answer1_1, Answer1_2, Answer1_N,
2             Answer2_1, Answer2_2, Answer2_N,
3             Answer3_1, Answer3_2, Answer3_N,
4             Answer4_1, Answer4_2, Answer4_N,
an so on...
Any ideas on a crosstab procedure that can acomplish this task?

any help is welcome
Thanks in advance!

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

December 13, 2006 at 9:12 am

#677817

Felipe

Can we see the DDL for the tables, please?

John

Felipe Brito Valued Member Points: 68 More actions · Answer 2

Felipe Brito

Valued Member

Points: 68

December 13, 2006 at 9:14 am

#677819

John,

Sory but what do you mean by DDL?

FBP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Felipe

Data Definition Language. In other words, the CREATE TABLE statements.

Thanks

John

Felipe Brito Valued Member Points: 68 More actions · Answer 4

John

Two related tables, Questions and Answers:

Table Questions:

[IDQuestion] [int] IDENTITY (4125, 1) NOT NULL ,

[IDSurvey] [int] NOT NULL ,

[Question] [nvarchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[NPage] [int] NOT NULL ,

[Order] [int] NOT NULL CONSTRAINT [DF_ENC_Preguntas_Orden] DEFAULT (1),

[ReqA] [int] NOT NULL CONSTRAINT [DF_ENC_Preguntas_ReqA] DEFAULT (0),

[TypeQ] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[RPTA] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Other] [int] NOT NULL CONSTRAINT [DF_ENC_Preguntas_Otra] DEFAULT (0),

[OtherA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Media] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[RND] [int] NULL CONSTRAINT [DF_ENC_Preguntas_RND] DEFAULT (0)

Table ANS:

[IDANS] [bigint] IDENTITY (1, 1) NOT NULL ,

[IDQuestion] [int] NOT NULL ,

[SessionID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Answer] [nvarchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ANSAOther] [nvarchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Finalize] [int] NOT NULL CONSTRAINT [DF_ENC_RPTA_End] DEFAULT (0),

[QTY] [int] NULL CONSTRAINT [DF_ENC_RPTA_QTY] DEFAULT (1)

Felipe

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 5

Off the top of my head, this might give you the desired results, but it also may not be exactly what you are after. The query does use an aggregate function but it is neccessary to do the group by. Don't worry about the MIN unless you expect there to be more than one answer to a given question in a given user session.

The downside is, of course, the hardcoded nature of this code. If you want me to, I'll expand the solution to auto-grow itself.

SELECT SessionID, 
    MIN(CASE WHEN ENC_Questions.Question = 1 THEN ENC_ANS.Answer END) AS Question 1
    MIN(CASE WHEN ENC_Questions.Question = 2 THEN ENC_ANS.Answer END) AS Question 2
    MIN(CASE WHEN ENC_Questions.Question = 3 THEN ENC_ANS.Answer END) AS Question 3
FROM dbo.ENC_Questions 
    INNER JOIN dbo.ENC_ANS 
        ON dbo.ENC_ANS.IDQuestion = dbo.ENC_AND.IDQuestion
WHERE dbo.ENC_Questions.IDSurvey = 2155
ORDER BY dbo.ENC_ANS.SessionID, dbo.ENC_Questions.Order
GROUP BY dbo.ENC_ANS.SessionID

SQL guy and Houston Magician

Felipe Brito Valued Member Points: 68 More actions · Answer 6

Robert,

It does not work, as depending on the IDSurvey, the Questions table may have different number of questions, so the pivot column "Question" has to be built dynamic.

Thanks anyway