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
? 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),
QuestionText VARCHAR(255),
QuestionAnswer VARCHAR(255)
)
GO
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')
|||? Unfortunately, that won't work quite right as-is. It needs to be QuestionId, and needs to correspond to each question. So every answer to the same question will have the same QuestionId. That way, you can simply correlate in the FOR XML PATH subquery: SELECT UserID, FName, LName, TotalAnswers, pvt.*FROM ( SELECT UserID, FirstName AS FName, LastName AS LName, QuestionText, ( SELECT QuestionAnswer + ',' AS [data()] FROM TempReport TR2 WHERE TR2.UserId = TempReport.UserId AND TR2.QuestionId = TempReport.QuestionId FOR XML PATH('') ) AS QuestionAnswers, COUNT(*) OVER(PARTITION BY UserId) AS TotalAnswers FROM Tempreport WHERE QuestionText IS NOT NULL) 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 -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Meher@.discussions.microsoft.com> wrote in message news:0191df4c-9ecb-4505-8509-e8b5836f73a8@.discussions.microsoft.com... 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), QuestionText VARCHAR(255), QuestionAnswer VARCHAR(255) ) GO 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')|||Any ideas?.|||
Thanks Adam, I can make it as the same questionID for the answer as you have suggested.
In that case, would your solution hold good or does it need any more changes?.
Here is the DDL:
CREATE TABLE Tempreport
(UserID INT,
LastName VARCHAR(255),
FirstName VARCHAR(255),
OrderNumber INT,
QuestionTextID INT IDENTITY (1,1),
QuestionText VARCHAR(255),
QuestionAnswer VARCHAR(255)
)
GO
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',12,'Please indicate which apps you are interested in','Accounting')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',12,NULL,'Payroll')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',12,NULL,'Unavailable to attend')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',32,'How often do you pay','Weekly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',32,Null,'Fortnightly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',32,Null,'Four weekly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',32,Null,'Monthly')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',32,Null,'other')
|||
Hi Adam:
Dont worry about this. I got it working after I changed the questionID to be the same for the check box list. Here is the final query that brings back the data i want.
Thanks a lot for your tremendous help. you are awesome!!!
SELECT
UserID,
FName,
LName,
TotalAnswers,
pvt.*
FROM
(
SELECT
UserID,
FirstName AS FName,
LastName AS LName,
QuestionText,
(
SELECT QuestionAnswer + ',' AS [data()]
FROM TempReport TR2
WHERE
TR2.UserId = TempReport.UserId
AND TR2.QuestionTextId = TempReport.QuestionTextId
FOR XML PATH('')
) AS QuestionAnswers,
COUNT(*) OVER(PARTITION BY UserId) AS TotalAnswers
FROM Tempreport
WHERE QuestionText IS NOT NULL
) AS ATR
PIVOT
(
MAX(QuestionAnswers)
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],
[Please indicate which apps you are interested in],[Town],
[How often do you pay])
) as Pvt
go
.com> wrote in message