sql server - How do I use Dynamic SQL to Generate Groups of Columns? -

-

people different types , amounts of fruit , want them. have table looks this:

name    fruit   count   temp ----------------------------- jim     apple    3      hot jim     banana   7      cold jim     orange   12     cold sam     plum     5      hot sam     peach    1      hot bob     cherry   4      cold bob     banana   11     hot bob     orange   9      cold bob     kiwi     6      hot

each person have 1 or 1000 rows , not know how many ahead of time think requires dynamic column lists. need this:

name    fruit_1 count_1 temp_1  fruit_2 count_2 temp_2  fruit_3 count_3 temp_3  fruit_4 count_4 temp_4 ------------------------------------------------------------------------------------------------------- jim     apple     3      hot    banana    7      cold   orange    12     cold   null     null   null sam     plum      5      hot    peach     1      hot    null     null    null   null     null   null bob     cherry    4      cold   banana    11     hot    orange     9     cold   kiwi      6     hot

the code pivot in sql 2005 works parsing out 1 column when don't know how many rows there be, can't work multiple columns. tried creating @select_list variables, , can use create columns, can't data want in them , don't know how interweave order want (eg f1,c1,t1,f2,c2,t2 vs f1,f2,c1,c2,t1,t2 etc)

here code have been unsuccessfully trying tweak:

create table #stackoverflowtest(     [name] [varchar](3) not null,     [fruit] [varchar](12) not null,     [number] [int] not null,     [temp] [varchar](4) not null)  insert #stackoverflowtest values ('jim','apple',3,'hot'),     ('jim','banana',7,'cold'),     ('jim','orange',12,'cold'),     ('sam','plum',5,'hot'),     ('sam','peach',1,'hot'),     ('bob','cherry',4,'cold'),     ('bob','banana',11,'hot'),     ('bob','orange',9,'cold'),     ('bob','kiwi',6,'hot')   declare @sql varchar(max) declare @pivot_list varchar(max) declare @select_list varchar(max)   select @pivot_list = coalesce(@pivot_list + ', ', '') + '[' + convert(varchar, pivot_code) + ']'     ,@select_list = coalesce(@select_list + ', ', '') + '[' + convert(varchar, pivot_code) + '] [fruit_' + convert(varchar, pivot_code) + ']' (     select distinct pivot_code     (         select name, fruit, number, temp, row_number() on (partition name order name,number) pivot_code         #stackoverflowtest     ) rows ) pivot_codes  set @sql = ' ;with p (     select name, fruit, number, temp, row_number() on (partition name order name,number) pivot_code     #stackoverflowtest ) select name, ' + @select_list + ' p pivot (     min(fruit)     pivot_code in (         ' + @pivot_list + '     ) ) pvt ' exec (@sql)

i wouldn't try using pivot that, gets headmess situation pretty quickly.

this simpler:

declare @sql varchar(max) declare @select_list varchar(max)   select @select_list = isnull(@select_list + ', ', '') + 'sum(case when pivot_code = ' + [pivot_code] + ' number else null end) number_' + [pivot_code] + ', min(case when pivot_code = ' + [pivot_code] + ' temp else null end) temp_' + [pivot_code] + ', min(case when pivot_code = ' + [pivot_code] + ' fruit else null end) fruit_' + [pivot_code] (     select distinct pivot_code     (         select name, fruit, number, temp, convert(varchar(20), row_number() on (partition name order name,number)) pivot_code         #stackoverflowtest     ) rows ) pivot_codes  set @sql = ' ;with p (     select name, fruit, number, temp, row_number() on (partition name order name,number) pivot_code     #stackoverflowtest ) select name, ' + @select_list + ' p group name' exec (@sql)

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