sql server - Pivot SQL table (Rows into Columns) -


i returning data table in sql stored procedure. trying pivot rows columns, , columns rows, struggling lot of tutorials following have laid out they're tables differently.

this select @ bottom of stored procedure:

select   (case when [fitter] null (select distinct substring([first name],1,1)+' '+[second name] fitters [fitter id]=fitterid) else fitter end) fitter, sum([install sell]) [install sell], sum([install cost]) [install cost], sum([install cost amt]) gross, (select cast(cast((taxstatus/100 * sum([install cost amt])) decimal(18,5)) float)) cis, (select cast(cast((vatstatus/100 * sum([install cost amt])) decimal(18,5)) float)) vat, sum([install cost amt]) - (select cast(cast((taxstatus/100 * sum([install cost amt])) decimal(18,5)) float)) + (select cast(cast((vatstatus/100 * sum([install cost amt])) decimal(18,5)) float)) fitterspay, sum([install cost amt]) + (select cast(cast((vatstatus/100 * sum([install cost amt])) decimal(18,5)) float)) datafile  @temptable group fitterid, fitter, taxstatus, vatstatus

this data returns: enter image description here

i columns pivot rows , rows pivot columns...

i have searched around online , struggling figure out, wondering if assistance please.

any assistance appreciated.

this done first unpivoting , pivoting. here example, can adjust yo data:

declare @t table(col0 varchar(20), col1 money, col2 money, col3 money)  insert @t values ('aaaaaa', 1, 1.2, 0), ('bbbbbb', 2, 2.2, 0), ('cccccc', 3, 3.3, 100), ('dddddd', 4, 4.4, 0)  select * @t   select * @t unpivot (a b in([col1],[col2],[col3])) pivot (max(a) col0 in([aaaaaa],[bbbbbb],[cccccc],[dddddd])) p

output1:

col0    col1    col2    col3 aaaaaa  1.00    1.20    0.00 bbbbbb  2.00    2.20    0.00 cccccc  3.00    3.30    100.00 dddddd  4.00    4.40    0.00

output2:

b       aaaaaa  bbbbbb  cccccc  dddddd col1    1.00    2.00    3.00    4.00 col2    1.20    2.20    3.30    4.40 col3    0.00    0.00    100.00  0.00

it depends on type of data, need dynamically. there plenty of examples on site. search dynamic pivoting.

edit:

something this:

select * ( select   (case when [fitter] null (select distinct substring([first name],1,1)+' '+[second name] fitters [fitter id]=fitterid) else fitter end) fitter, sum([install sell]) [install sell], sum([install cost]) [install cost], sum([install cost amt]) gross, (select cast(cast((taxstatus/100 * sum([install cost amt])) decimal(18,5)) float)) cis, (select cast(cast((vatstatus/100 * sum([install cost amt])) decimal(18,5)) float)) vat, sum([install cost amt]) - (select cast(cast((taxstatus/100 * sum([install cost amt])) decimal(18,5)) float)) + (select cast(cast((vatstatus/100 * sum([install cost amt])) decimal(18,5)) float)) fitterspay, sum([install cost amt]) + (select cast(cast((vatstatus/100 * sum([install cost amt])) decimal(18,5)) float)) datafile  @temptable group fitterid, fitter, taxstatus, vatstatus) t  unpivot (a b in([install sell],[install cost],[gross]/*,...*/)) pivot (max(a) fitter in([d page],[j hopley]/*,...*/)) p

-

Comments

Post a Comment

Popular posts from this blog

facebook - android ACTION_SEND to share with specific application only -

in android application have 4 buttons facebook , viber , telegram , whatsapp , want share different content based on each button. for example if user clicks on viber button want user action_send share content viber only. i found this explains how facebook , twitter seems it's calling specific class name of application don't know applications wanna use except facebook. all android apps have unique id, first have check if these apps installed in user's device , can pass unique id via intent sharing. according below: unique ids different apps : viber : com.viber.voip telegram : org.telegram.messenger whatsapp : com.whatsapp check if these apps installed , if installed send messages through intent. private void sendmessage(context context,string message, string appids) { final boolean isappinstalled =isappavailable(context, appids); if (isappinstalled) { intent myintent = new intent(intent.action_send); myintent.settype("text/p...
Read more

python - Creating a new virtualenv gives a permissions error -

i'm getting following output when running virtualenv newvenv . traceback (most recent call last): file "/usr/local/bin/virtualenv", line 5, in <module> pkg_resources import load_entry_point file "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 2727, in <module> add_activation_listener(lambda dist: dist.activate()) file "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 700, in subscribe callback(dist) file "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 2727, in <lambda> add_activation_listener(lambda dist: dist.activate()) file "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 2227, in activate self.insert_on(path) file "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 2334, in insert_on self.check_version_conflict() file "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 2373, in check_version_conflict...
Read more

javascript - cocos2d-js draw circle not instantly -

i wondering how draw circles (or other shapes also) not instantly. so far tried drawcircle(args...) in ccdrawnode.js , drawcircle(args...) in cc.drawingprimitivecanvas class, , draw circles popping out instantly on screen. what if want achieve effect circular progressive bar, completes circle based on percentage of initialization? or more generally, if want draw circle respect prolonged period? thinking there drawcircle function elapsed time argument fail find any. or have implement own? thanks suggestions, far out of ideas. i think have implement own. think can achieve update circle drawing on each time update() function called.
Read more