I have a Pivot data which I need to insert into a table. I can get the data inserted into the desired table if I run it as SQL, but When I try to run it as Stored procedure, data does not get inserted. Here is my query: Begin Delete from TEMP_PIVOTTABLE; Insert into TEMP_PIVOTTABLE (COL1,COL2,COL13,COL4) with pivot_data as ( SELECT COL1,COL2,COL13,COL4 FROM TEMP_TABLE1 ) select * from pivot_data PIVOT ( sum (COL3) FOR COL2 IN ('Client','Yes','No')) order by COL1; END;
Are you using SQL Server? The code you posted has syntax errors even if you run it without putting it into a stored proc. The syntax should be something like shown below. Even that is not correct because you are trying to sum the subj_count column from the results of the cte named pivot_data, but that cte does not have a column named subj_count. So while this will parse, it will not run. Change the subj_count to COL1, COL2, COL3 or COL4 as appropriate.
Delete from TEMP_PIVOTTABLE; with pivot_data as ( SELECT COL1,COL2,COL13,COL4 FROM TEMP_TABLE1 ) Insert into TEMP_PIVOTTABLE (COL1,COL2,COL13,COL4) select * from pivot_data PIVOT ( sum (subj_count) -- THERE IS NO subj_count in PIVOT_DATA!! FOR COL2 IN ([Client],[Yes],[No])) P order by COL1;
I am using SQL Developer (Oracle)
subj_count is COL3. updated the original post.
I made your suggestion, I am getting syntax (Expected Select) error on the Insert into line.
The code I posted is for SQL Server. This forum is for Microsoft SQL Server; so you might be better off asking the question on an Oracle forum or a general database forum.
Thanks James, will do that!