sql server - SQL-statement for two group-columns -

-

i have problem creating sql-statement sqlserver2008. have following data:

city     person     priority ----------------------------------- linz     mike       1 wien     mike       1    linz     tom        1 wien     tom        1 linz     john       1 linz     sarah      2

this means persons mike , tom choose cities linz , wien priority 1.
john chooses linz priority 1.
sarah chooses linz priority 2.

now want following output:

cities          persons          priority ----------------------------------- linz, wien      mike, tom       1 linz            john            1 linz            sarah           2

i have following sql-statement not expected result query john has entry wien priority 1.

select (select    stuff((select ', ' + d.city    (select distinct d2.city dbo.dummytable d2         d2.priority = d1.priority) d   xml path('')), 1, 2, '')   )  cities, (select    stuff((select ', ' + d.person    (select distinct d2.person dbo.dummytable d2         d2.priority = d1.priority) d   xml path('')), 1, 2, '')   )  persons, d1.priority dbo.dummytable d1 group d1.priority

you can use sql fiddle

any ideas how query written in sql?

here's way it:

;with personcitygrouppreferences (   select     person,     priority,      stuff ((       select ', ' + d2.city       dummytable d2       d1.priority = d2.priority           , d1.person = d2.person       xml path('')     ), 1, 2, '') cities   dummytable d1   group person, priority ) select    cities,   stuff ((     select ', ' + p2.person     personcitygrouppreferences p2     p1.cities = p2.cities       , p1.priority = p2.priority     xml path('')   ), 1, 2, '') persons,   priority personcitygrouppreferences p1 group priority, cities

sqlfiddle link: http://www.sqlfiddle.com/#!3/d831d/57

in order achieve final result, divided solution 2 steps:

  1. obtain result set groups data person , priority , contains comma-separated list of cities third column

  2. take result set obtained point 1 , same thing, group columns cities (the comma-separated list) , priority , produce comma-separated list of corresponding persons.

in query above, step 1 query:

select   person,   priority,    stuff ((     select ', ' + d2.city     dummytable d2     d1.priority = d2.priority     , d1.person = d2.person     xml path('')   ), 1, 2, '') cities dummytable d1 group person, priority

here's how partial results in sql: http://www.sqlfiddle.com/#!3/d831d/58

i exposed first query cte, making available (outer) query 2, same thing, different grouping criteria.


Comments

Post a Comment

Popular posts from this blog

java - activate/deactivate sonar maven plugin by profile? -

-
i use sonar plugin executes if activated in maven profile. sonar runs on every compile. the problem build fail other team members not have sonar application installed locally. pom.xml checked in team members same copy including sonar configuration, don't want them modify purpose of disabling sonar (they might inadvertently check modified copy in). please advise how configure maven (.m2/settings.xml) and/or pom.xml sonar feature can enabled/disabled local profile. here sonar configuration in each project's pom.xml: <plugin> <groupid>org.codehaus.mojo</groupid> <artifactid>sonar-maven-plugin</artifactid> <version>1.0</version> <executions> <execution> <id>make-sonar</id> <phase>compile</phase> <goals> <goal>sonar</goal> </goals> </execution> </executions> ...
Read more

python - TypeError: can only concatenate tuple (not "float") to tuple -

-
i'm new python, question might easy, anyway, patience: as trying call newton-raphson method calculate implied volatility in black-scholes formula call/put option pricing, first thing is, newton method in scipy.optimize seems calculate function's zeros, in black-scholes formula, want function's value option price, not zero. (i'm new here programming, i'm not sure techniques.) should write function things like: def f(sigma, price): return bsformula(s0,k,r,t,q,sigma) - price then, while calling newton method, takes args=() 1 parameter in function, write this: value = newton(bsprice2, 0.5, args=price) but error message: file "bs.py", line 36, in bsimpvol value = newton(bsprice2, 0.5, args=float(price)) file "/usr/lib/python2.7/dist-packages/scipy/optimize/zeros.py", line 143, in newton q0 = func(*((p0,) + args)) typeerror: can concatenate tuple (not "float") tuple could tell me why that? how fix it? apprecia...
Read more

java - What is the difference between String. and String.this. ? -

-
this question has answer here: meaning of .this , .class in java 2 answers i'll straight point. im still learning bit of syntax , want know difference between code is code a: public class buttonz extends jbutton{ public buttonz(){ settext(new string(string.valueof(i))); } } please ignore fact i undeclared, not lost. code b: public class buttonz extends jbutton{ public buttonz(){ settext(new string(string.this.charat(i))); } } what don't yet understand difference in typing string.this , string. i under assumption when use dot operator on class accessing it's static methods(and/or variables if they're not hidden). i have studied little bit , have concluded when using string. accessing string static methods.. when using string.this. i'm accessing methods class buttonz extendi...
Read more