Reporting Services Error

l saw error message on log reader on replicated please suggest me
THANKS U
Another logreader agent for the subscription(s) is running.
NOTE: The step was retried the requested number of times (10) without succeeding .The step failedIs the logreader job still failing?
If so, the same logreader job is actually running on your publisher. It may be an orphaned process. If so, you will need to kill it.

--jfp|||It ok. Thank you so much

I just changed my dataset syntaxes from the typical
SELECT col FROM table WHERE col3 = @.value
to
="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
For some reason I'm getting the following pop-up:
--
Processing Errors
--
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
â'ds_Legal_Entityâ'.
--
OK
--
I am not familiar with the CommandText syntax to understand where the error
might be. My query is below, could someone tell me what the problem might be?
Thanks!
Mike
="SELECT DISTINCT dbo.t_d_legal.legal_desc
FROM dbo.t_d_legal
INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
IIF(Parameters!BusKey.Value = 0,"","
AND
(dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
" ORDER BY dbo.t_d_legal.legal_desc"think you had extra single quote:
="SELECT col FROM table WHERE col3 = " & Parameters!Code.Value & " rest of
code"
otherwise if you need quotes due to your parm value being character you'll
need to double up on the quotes. read BOL.
"Bassist695" wrote:
> I just changed my dataset syntaxes from the typical
> SELECT col FROM table WHERE col3 = @.value
> to
> ="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
> For some reason I'm getting the following pop-up:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Cannot set the command text for data set 'ds_Legal_Entity'.
> Error during processing of the CommandText expression of dataset
> â'ds_Legal_Entityâ'.
> --
> OK
> --
> I am not familiar with the CommandText syntax to understand where the error
> might be. My query is below, could someone tell me what the problem might be?
> Thanks!
> Mike
> ="SELECT DISTINCT dbo.t_d_legal.legal_desc
> FROM dbo.t_d_legal
> INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
> WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
> IIF(Parameters!BusKey.Value = 0,"","
> AND
> (dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
> " ORDER BY dbo.t_d_legal.legal_desc"

Can anyone help with this error message I get with vbullettin

Database error in vBulletin 2.2.9:

Invalid SQL:
INSERT INTO useractivation
(useractivationid, userid, dateline, activationid, type, usergroupid)
VALUES
(NULL, 21, 1063544677, '27618960', 1, 3)

mysql error: Unknown column 'usergroupid' in 'field list'

mysql error number: 1054Hi

This is a SQL Server news group!

It seems like the useractivation table does not contain the usergroupid
column. It could be that this column does not exist, the column is spelt
differently, or the name is case dependent..

John

"spider" <spiderxo@.msn.com> wrote in message
news:dPZ8b.2998$f7.229764@.localhost...
> Can anyone help with this error message I get with vbullettin
>
> Database error in vBulletin 2.2.9:
> Invalid SQL:
> INSERT INTO useractivation
> (useractivationid, userid, dateline, activationid, type, usergroupid)
> VALUES
> (NULL, 21, 1063544677, '27618960', 1, 3)
> mysql error: Unknown column 'usergroupid' in 'field list'
> mysql error number: 1054sql

Please give kind attension to my queries

When I am giving like http://localhost/ReportServer the following message is coming in explorer. What I have to do please help

Reporting Services Error

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) Get Online Help

SQL Server Reporting Services

Moving to RS forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||What version of reporting services are you using and what version of sql server did you point it to?|||

Thanku for attension

I am using version SQL server 2005

|||

This thread may help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237706&SiteID=1

cheers,

Andrew

sql

Hey everyone,
this message comes in two forms, the short version and the long detailed
version-- that way hopefully I can get all the help possible as fast as
possible :)
Short version:
I have a table Names(names_id, name1, name2) that I normalized into 2
tables: Name(name_id, name) and ConnectNames(oldName_id, name_id,
number). The number column just indicates if the name was from the name1
column or the name2 column. Given that I've now normalized this, if I
need to get what was once 1 row (for example: 123, Bob, John), it will
appear as 2 rows if I need to get it by the original nameId as follows:
select oldName_id, name, number
from connectNames cn
inner join name n on n.name_id=cn.oldName_id
where oldName_id=123
would now return:
123 Bob 1
123 John 2
What I need however is for this to be displayed inline like it used to
so that it can be returned in one row (because this gets joined to other
tables). The issue: I'm dealing with millions of rows in this table, and
millions of rows in the other tables that ultimately got joined with the
old denormalized Names table. I've tried using a pivot table approach on
the normalized data to get it in 1 row, but it is very slow when
returning large rowcounts. I've tweaked the indexes, but you can only
get so much performance.
How can I do this better?
Thanks a bunch in advance,
DS
Long detailed version:
I started with a table Names(<pk>names_id, name1, name2). Problem is I
needed to search by name, so I normalized this into a names table and a
cross-reference table:
Name(<pk>name_id, name) and ConnectNames(oldName_id, name_id, number).
I've included the code for this at the bottom of the message for how I
went about this.
Hurra for normalization, now is easy to search for a name:
select oldName_id from connectNames cn inner join name n on n.id=cn.name
where name=@.nameToSearch;
This however presented another problem: I need to be able to display
both name1 and name2 in a single row-- I need to pivot what I just
created (thats actually why I sneaked in the number column into the
cross-ref table to make it easy to pivot). A solution I grabbed from
MSDN was to create a view that I could then join onto twice:
create view connectNamesView
select oldName_id,
MIN(CASE number WHEN 1 THEN name_id END) AS name_id1,
MIN(CASE number WHEN 2 THEN name_id END) AS name_id2,
from connectNames
group by oldName_id
select n1.name, n2.name from connectNamesView cnv
inner join name n1 on n1.name_id=cnv.name_id1
inner join name n2 on n2.name_id=cnv.name_id2
The real issue with this though is that as you may imagine this is
pretty resource intensive, especially when you have several million
records in these tables, and when you join oldName_id to another table
with several million records like this:
select id, product, name1, name2
from Owners o -- note: owners has millions of rows too
inner join connectNamesView cnv on cnv.name_id=o.name_id
inner join name n1 on n1.name_id=cnv.name_id1
inner join name n2 on n2.name_id=cnv.name_id2
where id between 10000 and 20000
Running this takes ages when its joined to another table. Is there a
better way to improve performance or to denormalize the results JUST for
display (display them in 2 columns)? (by the way, is denormalize the
correct term for doing what I need to here).
Thanks in advance for the help and reading this long-winded post :)
-DS
To normalise the table Id did the following:
create table tmpName (id int, name varchar(20), number tinyint);
insert into tmpName (id, name, number) select name_id, name1, 1
insert into tmpName (id, name, number) select name_id, name2, 2
create table Name (id int not null identity(1,1), name varchar(20))
insert into Name (name) select distinct name from tmpName
-- at this point the Names table has been normalized; now to create the
one to many relationship:
create table ConnectNames(oldName_id int, name_id int, number tinyint)
insert into ConnectNames(oldName_id, name_id, number)
select t.id, n.id, t.number
from name n
inner join tmpName t on t.name = n.name
drop table tmpNameProviding the relationship is always 1 to 1 (exactly one name for each
old_name), then this might work (untested):
select name as Name1
,Name 2
=(select name
from Names OldNames
where (OdlNames.oldName_id = Names.name_id))
from Names
For a better solution post DDL, sample data, preferably with expected result
s.
ML

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