sql server - Can I create column with header name according to values in another table? -

-

this first question here , please excuse me, if question looks wired. totally new programming , sql though have knowledge in ms access database.

i have created 2 tables orderstabel , ordereditems. want create table orderfulldetails in screenshot here:

table picture

i want create orderfulldetails table follows.

  1. contains in item column in ordereditem table should create separate column in orderfulldetails table names.

  2. if column exists in orderfulldetails table equal item name in ordereditem table, qty value should entered matching column according orderno. , if there no column matching, should create new column.

your million worth me.

thanks

firstly welcome stack overflow. in asking question expected show how have attempted solve problem yourself. not pictures, , prefer given sample data, in format makes easy copy. please note not coding service. however, because first time, kind.

it goes against normal rules of relational database design build third table hold data held in other tables. should create stored procedure retrieve records both tables in format want. scsimon rightly says need pivot , dynamic sql in procedure.

by way of example give following:

create procedure orderexample   create table #orderstable( orderno int, orderdate date, customerid int, totalitems int, totalamount decimal(10,2) ) insert #orderstable values(101, '2016-8-11', 354, 3, 30) insert #orderstable values(102, '2016-8-12', 221, 2, 20) insert #orderstable values(103, '2016-8-13', 67, 1, 20) insert #orderstable values(104, '2016-8-14', 8965, 3, 40)  create table #ordereditems ( orderno int, orderitem varchar(10), orderqty int ) insert #ordereditems values(101, 't-shirt', 2) insert #ordereditems values(101, 'bedsheet', 1) insert #ordereditems values(102, 'pants', 2) insert #ordereditems values(103, 'quilt', 1) insert #ordereditems values(104, 't-shirt', 2) insert #ordereditems values(104, 'pants', 1)  declare @itemnames varchar(max) =  (select  stuff(( select distinct ',' + quotename(orderitem) #ordereditems xml path('')), 1, 1, '') collist)   declare @sql varchar(max) = 'select orderno, orderdate, customerid,  totalitems, totalamount,' + @itemnames + ' (select o.orderno, o.orderdate, o.customerid, o.totalitems, o.totalamount, i.orderitem, i.orderqty  #orderstable o inner join #ordereditems on i.orderno = o.orderno) subq pivot(sum(orderqty) orderitem in (' + @itemnames + ')) pvt'  exec (@sql)  drop table #orderstable drop table #ordereditems

to run this, type: exec orderexample

the result 

orderno orderdate   customerid  totalitems  totalamount bedsheet    pants   quilt   t-shirt 101     2016-08-11  354         3           30.00       1           null    null    2 102     2016-08-12  221         2           20.00       null        2       null    null 103     2016-08-13  67          1           20.00       null        null        1   null 104     2016-08-14  8965        3           40.00       null        1       null    2

note in finished version not need lines create temporary tables, insert data , drop them again. instead must replace #orderstable , #ordereditems names of correct tables (and of course correct field names). have included them, example runs stand-alone.


Comments

Post a Comment

Popular posts from this blog

mysql - Dreamhost PyCharm Django Python 3 Launching a Site -

-
i'm new python/django , created simple django website using pycharm. site works fine running on computer, i'm lost when comes taking site onto host (dreamhost). i'm able upload files correct directories fine, , believe made correct changes database on mysql. i'm not sure go there though. right now, page shows files rather site. direction appreciated. much! you should not run django application using python manage.py runserver on production. how run application on django depends on server (apache, nginx) want use for apache - https://docs.djangoproject.com/en/1.10/howto/deployment/wsgi/modwsgi/ for nginx - https://www.digitalocean.com/community/tutorials/how-to-serve-django-applications-with-uwsgi-and-nginx-on-ubuntu-14-04 the mysql database should, of course, exist , have correct credentials. or, if choose use sqlite, file should accessible, readable , writable www-user (may different depending on linux distributive)
Read more

java - Sending SMS with SMSLib and Web Services -

-
i have made rest web service send smss using hsdpa usb modem. using smslib in java send smss. every time web service invoked, create gateway start service, send message, stop service , remove gateway. takes 20 seconds each message. found starting service takes lot of time. part of code use send smss service.getinstance().addgateway(gateway); service.getinstance().startservice(); outboundmessage msg = new outboundmessage(phonenumber, message); if (service.getinstance().sendmessage(msg)) { result = "message sent successfully!!"; } else { result = "could not send message."; } service.getinstance().stopservice(); service.getinstance().removegateway(gateway);//remove gateway is there way can start service once if not started , use send message when ever web service invoked? why dont group messages , send in 1 go? service.getinstance().sendmessages(messa...
Read more

java - How to resolve The method toString() in the type Object is not applicable for the arguments (InputStream) -

-
i'm trying read json file local machine. @path("/") public class jsonparsing { file f = new file("file.json"); if (f.exists()){ inputstream = new fileinputstream("file.json"); string jsontxt = ioutils.tostring(is); system.out.println(jsontxt); jsonobject json = new jsonobject(jsontxt); string = json.getstring("1000"); system.out.println(a); } } but i'm getting error in tostring() method. possible read .txt file containing json object local machine? if possible, how done? firstly: whenever ask question- add imports well. secondly: yes possible read .txt file containing json object. steps: 1. add maven dependency in pom.xml - <dependency> <groupid>com.googlecode.json-simple</groupid> <artifactid>json-simple</artifactid> <version>1.1.1</version> </dependency> or add jar of dependency. 2.import...
Read more