pivot information: state

Showing posts with label state. Show all posts

Friday, March 30, 2012

Please help Invalid Cloumn Name

Hi,
Here is my SP
I need to run this as this
ConFirmOrders_SP 'SVR,UCC' this is giving me
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'UCC'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'SVR'.
ALTER Procedure ConfirmOrders_sp
(
@.StrType Varchar(100)
)
As
BEGIN
SET NOCOUNT ON
Create table #Confirm
(
ShipName Varchar(80),
Reference Varchar(60),
ReqNo int,
CorpName varchar(255),
ReqDate datetime,
RptType nVarchar(24)
)
Declare @.SQL varchar(5000)
Insert into #Confirm(ShipName,Reference,ReqNo,CorpNa
me,ReqDate,RptType)
SELECT Sh.ShiptoName , Req.RefNo , Req.Reqno ,
Case Sm.ItemGroup
When 'UCC' Then Req.Debtor
When 'SVR' Then Req.Respecting
When 'SAT' Then Req.CorpName
When 'SRO' Then Req.CorpName
When 'TRO' Then Req.CorpName
When 'REG' Then Req.CorpName
End As [Name],Req.Reqdate,SM.ItemGroup As [Req Type]
FROM dbo.tblArShipTo Sh INNER JOIN
dbo.tblCorpRpt Req ON Sh.CustId = Req.CustId INNER JOIN dbo.tblSmItem SM
ON Req.ReqType = SM.ItemCode
WHERE Req.Reqstatus ='W' AND Sh.ShiptoId ='MAIL'
Order By Req.Reqno
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type] from #Confirm WHERE Rpttype IN
(' + @.StrType + ')'
Exec(@.SQL)
ENDSVR and UCC are individual strings so they both need to be quoted
individually. Change your call to something like this:
ConFirmOrders_SP '''SVR'',''UCC'''
You'll need to double check that I have the quotes done correctly, but you
should get the idea.
--Brian
(Please reply to the newsgroups only.)
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:uRETsQ%23rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Here is my SP
> I need to run this as this
> ConFirmOrders_SP 'SVR,UCC' this is giving me
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'UCC'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'SVR'.
>
> ALTER Procedure ConfirmOrders_sp
> (
> @.StrType Varchar(100)
> )
> As
> BEGIN
> SET NOCOUNT ON
> Create table #Confirm
> (
> ShipName Varchar(80),
> Reference Varchar(60),
> ReqNo int,
> CorpName varchar(255),
> ReqDate datetime,
> RptType nVarchar(24)
> )
> Declare @.SQL varchar(5000)
> Insert into #Confirm(ShipName,Reference,ReqNo,CorpNa
me,ReqDate,RptType)
> SELECT Sh.ShiptoName , Req.RefNo , Req.Reqno ,
> Case Sm.ItemGroup
> When 'UCC' Then Req.Debtor
> When 'SVR' Then Req.Respecting
> When 'SAT' Then Req.CorpName
> When 'SRO' Then Req.CorpName
> When 'TRO' Then Req.CorpName
> When 'REG' Then Req.CorpName
> End As [Name],Req.Reqdate,SM.ItemGroup As [Req Type]
> FROM dbo.tblArShipTo Sh INNER JOIN
> dbo.tblCorpRpt Req ON Sh.CustId = Req.CustId INNER JOIN dbo.tblSmItem SM
> ON Req.ReqType = SM.ItemCode
> WHERE Req.Reqstatus ='W' AND Sh.ShiptoId ='MAIL'
> Order By Req.Reqno
> Set @.SQL=
> 'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
> ReqDate AS [Req Date],RptType AS [Rpt Type] from #Confirm WHERE Rpttype IN
> (' + @.StrType + ')'
>
> Exec(@.SQL)
> END
>|||Thanks
Dib
"Brian Lawton" <brian.k.lawton@.redtailcr

.com> wrote in message
news:OWca8W%23rFHA.908@.tk2msftngp13.phx.gbl...
> SVR and UCC are individual strings so they both need to be quoted
> individually. Change your call to something like this:
> ConFirmOrders_SP '''SVR'',''UCC'''
> You'll need to double check that I have the quotes done correctly, but you
> should get the idea.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
> news:uRETsQ%23rFHA.2212@.TK2MSFTNGP15.phx.gbl...
>sql

Friday, March 23, 2012

Please comment

Dear all
My server received following information around 2 months from event viewer.
Error: 17883, Severity: 1, State: 0
The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
(We have not a scheduler 3 in my sql)
After that, i disable symantec antivirus on my server, then this error do
not occur again. i dont know why?
Server :windows 2003
SQL : SQL 2000 SP3Hi
Generally speaking, it is not a good idea to have anti-virus products on a
SQL Server.
http://support.microsoft.com/kb/309422/en-us
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
> Dear all
> My server received following information around 2 months from event
> viewer.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
> (We have not a scheduler 3 in my sql)
> After that, i disable symantec antivirus on my server, then this error do
> not occur again. i dont know why?
> Server :windows 2003
> SQL : SQL 2000 SP3
>|||Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi
> Generally speaking, it is not a good idea to have anti-virus products on a
> SQL Server.
> http://support.microsoft.com/kb/309422/en-us
>
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
>> Dear all
>> My server received following information around 2 months from event
>> viewer.
>> Error: 17883, Severity: 1, State: 0
>> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context
>> 0x00CDA950
>> (We have not a scheduler 3 in my sql)
>> After that, i disable symantec antivirus on my server, then this error do
>> not occur again. i dont know why?
>> Server :windows 2003
>> SQL : SQL 2000 SP3
>>
>|||Do you have any document ?
"bass_player [SBS-MVP]" wrote:
> Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
> > Hi
> >
> > Generally speaking, it is not a good idea to have anti-virus products on a
> > SQL Server.
> >
> > http://support.microsoft.com/kb/309422/en-us
> >
> >
> > --
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> > "SQL" <SQL@.discussions.microsoft.com> wrote in message
> > news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
> >> Dear all
> >>
> >> My server received following information around 2 months from event
> >> viewer.
> >>
> >> Error: 17883, Severity: 1, State: 0
> >> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context
> >> 0x00CDA950
> >>
> >> (We have not a scheduler 3 in my sql)
> >> After that, i disable symantec antivirus on my server, then this error do
> >> not occur again. i dont know why?
> >> Server :windows 2003
> >> SQL : SQL 2000 SP3
> >>
> >>
> >
> >
>
>

Monday, February 20, 2012

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