pivot information: operation

Showing posts with label operation. Show all posts

Friday, March 9, 2012

PL/SQL: couldnt create trigger

Hi,

i have a mass operation with some triggers to increment the id of a table ...

now, some of the copy&pasted statements working, some won't ... :(

part of the statement:

CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_LICENSE_ID BEFORE INSERT ON SHEMA.LICENSE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)

^^ this doesn't print out any error.

CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_SOFTWARE_ID BEFORE INSERT ON SHEMA.SOFTWARE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)

^^ there's an error like:
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_WORKSTATION_ID BEFORE INSERT ON SHEMA.WORKST
*
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed

whats going wrong?? both tables exists in the database with an id field defined as number(10) and flagged with a primary key.

maybe somebody can explain ...

thanksproblem solved|||Hi, I am stuck with this same problem. Please can someone help. It is quite urgent.

Thaks in advanced for your help...

Regards,

Paul|||This is what the error means:

Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:

DECLARE
dept_rec dept_cur%ROWTYPE;
CURSOR dept_cur IS SELECT ...
...

Without seeing your code, it's hard to comment further.|||Hi,

This problem is getting strange.

I am using this code to product a primary key for a table. I have created a sequene and am using the next value from the sequence to act as the key.

The code I am using for the trigger is as follows

BEGIN
IF :new.ORDER_ID IS NULL THEN
SELECT AUTO_ORDER_ID.NEXTVAL ///SEQUENCE
INTO :new.ORDER_ID
FROM dual;
END IF;
END;

This has worked for about 8 tables with the exception of this one. I have tried to re-create the table using a different name and a different ID atttribute i.e table called sales and Sales_id and still no joy.

any ideas??

Oh.. I have also reinstalled the whole 9i database and I still get the same problem

Thanks for your help in advanced.

Regards,

Paul|||/// is not a valid comment syntax in PL/SQL, so that won't work! If that isn't the problem, I can't see anything obvious. You are getting:

ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

yes? On what line?

If still struggling, please post the ENTIRE trigger text exactly as you are running it - including the CREATE..., and the description of the table...|||I am stuck with the exact same error as above, the code I am using is as follows:

CREATE OR REPLACE TRIGGER "FYP"."TEST" BEFORE
INSERT ON "FYP"."JOBS"
FOR EACH ROW begin
select seq_jobs.nextval into :new.id from dual;
end;

sequence named seq_jobs exists.

The table named exists too and the code is as follows:
CREATE TABLE "FYP"."JOBS" ("ID" NUMBER(10), "ALARM_TEXT"
VARCHAR2(255 byte), "POINT_ID" NUMBER(10), "DATE" DATE, "PRORITY"
NUMBER(10))
TABLESPACE "FYP" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PARALLEL ( DEGREE DEFAULT )
LOGGING

This is really annoying: I cannot see the forward reference in the code: I don't see anything referenced apart from the table, the colum ID and the sequence...|||Hello all,

I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.

Code for the table:

CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

Code for the Sequence:

CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER

Code for the Trigger:

CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;

Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.

Any help would be very much appreciated! Thanks!

With kind regards,

Gog|||Originally posted by Gog
Hello all,

I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.

Code for the table:

CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

Code for the Sequence:

CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER

Code for the Trigger:

CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;

Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.

Any help would be very much appreciated! Thanks!

With kind regards,

Gog
Looking at your code and that of Rhs98 posted earlier, I notice that you both have table and column names in double quotes, and in fact both have a column called "DATE" which is of course a reserved word in Oracle.

Changing the column name from "DATE" to ADATE (i.e. anything that isn't a reserved word) makes the problem go away.

Moral: NEVER put double quotes round table and column names, and (as a consequence) NEVER use reserved words as table and column names!|||hey thanks for the fast reply .. It seems to have worked. But this raises another question.

Why did a few other tables with a field called 'DATE' in it work properly than? Sorry! Apparently I had another name then 'DATE' for my date fields in the other table :-$ So never mind about that!

And ps. I didn't put the quotes there, DBA Studio studio gave that code to me when I selected 'show object ddl' :-D

thx for the help!

PL/SQL effeciency question

Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

MarkOriginally posted by mchih
Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

Mark
Generally it is preferable to avoid procedural logic if you want best performance. Maybe you could combine all (or many of) your counts and sums into a single query using DECODE (or CASE) to filter the records:

SELECT SUM( DECODE( col1, 'x', 1, 0 )) as COUNT_WHERE_COL1_IS X
, COUNT(*) TOTAL_COUNT,
, SUM( DECODE( col3, 123, col4, 0 )) as SUM_COL4_WHERE_COL3_IS_123
...
FROM ...|||it might be difficult to put many of the query together, since each of them has a different where clause.

eg:

table temp(
card_type
card_amount
message_type
message_response
.
.
.
)

a sample query would be
select count(*), sum(card_amount) from temp
where card_type = x
and message_type = y
and message_response = z;

now that i think about it, it might be possible to use a GROUP BY to get all the values with similar WHERE clause, but I don't know how to store the values individually so i can access them later (eg. put it into different table)

TIA

Mark

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