pivot information: Pivot Error

Hi :

I am getting the following error message when I am trying to do the Pivot operation.

Msg 265, Level 16, State 1, Line 1

The column name "FirstName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

Msg 265, Level 16, State 1, Line 1

The column name "LastName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

Msg 8156, Level 16, State 1, Line 1

The column 'FirstName' was specified multiple times for 'Pvt'.

My SQL Query is:

SELECT UserID,FirstName,LastName

FROM

(

SELECT UserID,FirstName,LastName

FROM Tempreport AS ATR

PIVOT (

MAX(QuestionAnswer)

FOR QuestionText

IN ([CompanyAddress1],[CompanyAddress2],[CompanyName],[Country],[EmailAddress],[FirstName],[LastName],[PhoneNumber],[PostalCode],[Select the Category that best describes your Industry],[Select the Category that best describes your role],[Select the Category that best describes your title],[Town])) as Pvt

) Result (UserID,FirstName,LastName)

Here is the DDL:

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'FirstName','Testing1')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'LastName','Testing2')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'CompanyName','Testing3')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'CompanyAddress1','Testing4')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'CompanyAddress2','Testing5')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'Town','Testing6')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'Country','Testing7')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'PostalCode','Testing8')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'PhoneNumber','Testing9')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'EmailAddress','Testing9')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your Industry','Testing1')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your role','Other')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your title','Other')

CREATE TABLE Tempreport

(UserID INT,

LastName VARCHAR(255),

FirstName VARCHAR(255),

OrderNumber INT,

QuestionText VARCHAR(255),

QuestionAnswer VARCHAR(255)

)

Drop table tempreport.

Can anyone please let me know what I am doing wrong and how to fix this error?. looks like I cant select the same column inside the pivot and outside the pivot. In such a case what is my alternative?.

Any help is appreciated.

Thanks

? What output are you looking for? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Meher@.discussions.microsoft.com> wrote in message news:70fe23b8-d832-4a8d-b10e-e4bfec6c69bb@.discussions.microsoft.com... Hi : I am getting the following error message when I am trying to do the Pivot operation. Msg 265, Level 16, State 1, Line 1 The column name "FirstName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Msg 265, Level 16, State 1, Line 1 The column name "LastName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Msg 8156, Level 16, State 1, Line 1 The column 'FirstName' was specified multiple times for 'Pvt'. My SQL Query is: SELECT UserID,FirstName,LastName FROM ( SELECT UserID,FirstName,LastName FROM Tempreport AS ATR PIVOT ( MAX(QuestionAnswer) FOR QuestionText IN ([CompanyAddress1],[CompanyAddress2],[CompanyName],[Country],[EmailAddress],[FirstName],[LastName],[PhoneNumber],[PostalCode],[Select the Category that best describes your Industry],[Select the Category that best describes your role],[Select the Category that best describes your title],[Town])) as Pvt ) Result (UserID,FirstName,LastName) Here is the DDL: INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'FirstName','Testing1') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'LastName','Testing2') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'CompanyName','Testing3') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'CompanyAddress1','Testing4') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'CompanyAddress2','Testing5') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'Town','Testing6') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'Country','Testing7') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'PostalCode','Testing8') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'PhoneNumber','Testing9') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'EmailAddress','Testing9') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your Industry','Testing1') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your role','Other') INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer) VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your title','Other') CREATE TABLE Tempreport (UserID INT, LastName VARCHAR(255), FirstName VARCHAR(255), OrderNumber INT, QuestionText VARCHAR(255), QuestionAnswer VARCHAR(255) ) Drop table tempreport. Can anyone please let me know what I am doing wrong and how to fix this error?. looks like I cant select the same column inside the pivot and outside the pivot. In such a case what is my alternative?. Any help is appreciated. Thanks M|||

The output I am looking is for the Number of Questions that the survey has the responses something like this:

LastName FirstName UserID Address1 Address2 Select the category Select the role

Veerman Maek 10 123 XXX Other Other

Mark Smith 20 324 YYY IT Developer

|||? Something like this, perhaps: CREATE TABLE Tempreport (UserID INT,LastName VARCHAR(255),FirstName VARCHAR(255),OrderNumber INT,QuestionText VARCHAR(255),QuestionAnswer VARCHAR(255)) INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'FirstName','Testing1')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'LastName','Testing2')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'CompanyName','Testing3')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'CompanyAddress1','Testing4')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'CompanyAddress2','Testing5')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'Town','Testing6')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'Country','Testing7')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'PostalCode','Testing8')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'PhoneNumber','Testing9')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'EmailAddress','Testing9')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your Industry','Testing1')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your role','Other')INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your title','Other')go SELECT UserID, FName, LName, TotalAnswers, pvt.*FROM ( SELECT UserID, FirstName AS FName, LastName AS LName, QuestionText, QuestionAnswer, COUNT(*) OVER(PARTITION BY UserId) AS TotalAnswers FROM Tempreport) AS ATRPIVOT ( MAX(QuestionAnswer) FOR QuestionText IN ([CompanyAddress1],[CompanyAddress2],[CompanyName], [Country],[EmailAddress],[FirstName],[LastName], [PhoneNumber],[PostalCode], [Select the Category that best describes your Industry], [Select the Category that best describes your role], [Select the Category that best describes your title],[Town])) as Pvtgo drop table tempreportgo -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Meher@.discussions.microsoft.com> wrote in message news:4bc96011-34ee-46fb-bdfd-822456ee11e4@.discussions.microsoft.com... The output I am looking is for the Number of Questions that the survey has the responses something like this: LastName FirstName UserID Address1 Address2 Select the category Select the role Veerman Maek 10 123 XXX Other Other Mark Smith 20 324 YYY IT Developer|||Thanks Adam. It works!!!.|||

ok there seems to be another issue. For some questions in the survey, the respoonse is more than one. Because there is a check box on the web and the user can choose more than one response. in that case when I pivot the question text it shows up as null since the question text is inserted only once. How do we show all the possible answers (in other words concatenate the answers).

I tried to follow the article of http://www.aspfaq.com/show.asp?id=2529 and use the cross apply operator in SQL Server 2005 but it still shows as null. My query for cross apply was along the lines of

Select UserID,

QuestionText,

QuestionAnswer=LEFT(o.list,LEN(o.list) -1)

FROM Tempreport T

CROSS APPLY

(SELECT

CONVERT(VARCHAR(500),QuestionAnswer)+',' AS [text()]

FROM TempReport T2

WHERE T.UserID=T2.UserID

ORDER BY QuestionAnswer

FOR XML PATH('')

) o (list)

ORDER BY QuestionText

How do I incorporate the Cross apply into my original solution to come out with the question and show all the possible answers concatenated?. I dont want to see NULLs. Any re-write is really helpful.

Thanks

DDL is given below:

CREATE TABLE Tempreport

(UserID INT,

LastName VARCHAR(255),

FirstName VARCHAR(255),

OrderNumber INT,

QuestionText VARCHAR(255),

QuestionAnswer VARCHAR(255)

)

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'FirstName','Testing1')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'LastName','Testing2')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)

VALUES (200253,'Veerman','Mark',2,'CompanyName','Testing3')

INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyAddress1','Testing4')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyAddress2','Testing5')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Town','Testing6')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Country','Testing7')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'PostalCode','Testing8')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'PhoneNumber','Testing9')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'EmailAddress','Testing9')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your Industry','Testing1')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your role','Other')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your title','Other')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Please indicate which apps you are interested in','Accounting')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,NULL,'Payroll')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,NULL'Unavailable to attend')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'How often do you pay','Weekly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,Null,'Fortnightly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,Null,'Four weekly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,Null,'Monthly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,Null,'other')

--Original solution

DECLARE @.ListLen INT

DECLARE @.INDEX INT

DECLARE @.month nvarchar(25)

DECLARE @.year nvarchar(25)

DECLARE @.strResult VARCHAR(500)

select @.cstlist = (select DISTINCT QUOTENAME(LTRIM(RTRIM(QuestionText)))+','as [text()]

from Tempreport

order by QUOTENAME(LTRIM(RTRIM(QuestionText)))+','

for xml path(''))

SET @.ListLen=LEN(@.cstList)

SET @.cstList=LEFT(@.cstList,(@.ListLen-1))

SET @.strSQL=@.cstList

PRINT (@.strSQL)

select @.sql = 'SELECT UserID,FName,LName,pvt.* FROM

(SELECT UserID,FirstName AS FName,

LastName AS LName,QuestionText,QuestionAnswer,

COUNT(*) OVER(PARTITION BY UserId) AS TotalAnswers

FROM Tempreport) AS ATR '

+ 'PIVOT (MAX(QuestionAnswer) FOR QuestionText IN (' + @.strSQL+ ')) AS Pvt'

PRINT (@.sql)

--EXEC (@.sql)

|||? How do you know which answers belong to which questions? Can you introduce a QuestionId column or something along those lines? Otherwise, I don't think there's any way to solve this problem... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Meher@.discussions.microsoft.com> wrote in message news:16b34575-d985-4cde-9a9a-844627b4f068@.discussions.microsoft.com... ok there seems to be another issue. For some questions in the survey, the respoonse is more than one. Because there is a check box on the web and the user can choose more than one response. in that case when I pivot the question text it shows up as null since the question text is inserted only once. How do we show all the possible answers (in other words concatenate the answers). I tried to follow the article of http://www.aspfaq.com/show.asp?id=2529 and use the cross apply operator in SQL Server 2005 but it still shows as null. My query for cross apply was along the lines of|||

Hi Adam:

I have added the questionID (column name QuestionTextID) as you have suggested. I am helping some one with this problem and their db design is not good. The new DDL is given below:

CREATE TABLE Tempreport

(UserID INT,

LastName VARCHAR(255),

FirstName VARCHAR(255),

OrderNumber INT,

QuestionTextID INT IDENTITY (1,1),