Create table on SQL Server from dynamic pivot results -

-

is there way directly store results of dynamic pivot query fixed table? result dynamic can't create table specifying columnnames , methods "create table mytable (pivot select statement)" seem fail on sql server ("incorrect syntax near keyword 'as'"). have tried format sql below select - - structure failed so. appreciated!

the sql used pivot (build great website!):

declare @pivot varchar(max), @sql varchar(max) create table pivot_columns (pivot_column varchar(100))  insert pivot_columns select distinct datefield mytable order 1  select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from pivot_columns  set @sql = 'select * (select datefield, refcode, sumfield mytable) p pivot (sum(sumfield) datefield in  ( ' + @pivot + ') )  pvl'  drop table pivot_columns  exec (@sql)

unless not following trying should able add into mynewtable sql going execute , should new table. 

declare @pivot varchar(max), @sql varchar(max) create table pivot_columns (pivot_column varchar(100))  insert pivot_columns select distinct datefield mytable order 1  select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from pivot_columns  set @sql = 'select * mynewtable (select datefield, refcode, sumfield mytable) p pivot (sum(sumfield) datefield in  ( ' + @pivot + ') )  pvl'  drop table pivot_columns  exec (@sql)

i test creating new table in following script , gives me new table in db use:

create table t (      [month] int,      [id] nvarchar(20),      [cnt] int  )  insert t values (4,'total',214) insert t values (5,'total',23) insert t values (6,'total',23) insert t values (4,'func',47) insert t values (5,'func',5) insert t values (6,'func',5) insert t values (4,'indil',167) insert t values (5,'indil',18) insert t values (6,'indil',18)   declare @cols nvarchar(max),     @query  nvarchar(max);  select @cols = stuff((select distinct ',' + quotename(month)              t              xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select *             tabletest              (                 select month, id, cnt                 t            ) x             pivot              (                  sum(cnt)                 month in (' + @cols + ')             ) p '   execute(@query)  drop table t

Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more