SQLTeam.com | Weblogs | Forums

How to insert pivot data into a table

sql2008r2

#1
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;

#2

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;

#3

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.


#4

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.


#5

Thanks James, will do that!