Pivot Error

Hot Topic

how do i do a minimal client tool install to execute SSIS packages
accessing report server programmatically
replmerg.exe
Example illustrating SMO enumeration needed.
got error when using stored procedure with temp table in it
Error during processing report parameters
Unable to connect to sql server on local machine
connection error
"Not In" results set error
Started with Express, but installed Developer and don't see Developer instance...

SQL Server

Internal error: file 'dmattributeset.cpp', line 2004
Random access in a result set
Multi Value Parameters
Unable to add user to a role
Error: The value was too large to fit in the output column "colName" (60).
XML Bulk Load Error Trap
FlatFile Parsing Problem - Urgently Need Assistance!
Help with Report Filter on bit field
Parameter problem
Sizing Columns

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

M

Answer this question

Pivot Error

  • Lewis Yeung

    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

  • DIG

    What output are you looking for
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    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

  • Exentrick1

    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 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
    go

     
     
    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    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')

  • GhostlyDeath

    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')

  • Fylar

    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

  • Gaurav Makin

    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 Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    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

     

  • lbugnion

    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 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
    go
     

    drop table tempreport
    go
     

     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    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

  • UFAnders

    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')

  • btb73

    Any ideas .

  • OnlyLemming

    Thanks Adam. It works!!!.

  • ILVC

    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)
  • Pivot Error