Retrieve parent child records in SQL Server -


i have 2 tables trade , trade details this:

trade table:

id    portfolio   source   version     createdon   status --------------------------------------------------------- 1     test1       rim      1.0         2016-01-20    1 2     test2       ios      1.0         2016-01-20    1

trade details table:

id   tradeid    ticker   company     shares     action  comments ---------------------------------------------------------------- 1      1        msft     microsoft     100        buy      test 2      1        ibm      ibm           200        sell     test 3      2        yahoo    yahoo inc     50         sell     test 4      2        goog     google inc    500        buy      test

i want retrieve , show data in following format.

output

1     test1       rim      1.0         2016-01-20    1 1      1        msft     microsoft     100        buy      test 2      1        ibm      ibm           200        sell     test 2     test2       ios      1.0         2016-01-20    1 3      2        yahoo    yahoo inc     50         sell     test 4      2        goog     google inc    500        buy      test

you need use union between 2 tables, , add additional columns customized, set desired order generate required output

ex [in oracle syntax, idea same]

    select          case when oid = 0 to_char(p_tradeid) else portfolio end tradeid, portfolio, ticker, company,shares, action, comments         (         select             id oid, 1 soid,             tradeid p_tradeid,             to_char(id) portfolio, -- important change datatype of columns matching in union in order union statement work             ticker,             company,             to_char(shares) shares,             to_char(action) action,             comments                 (             --here select statement.... below demo             select 1 id, 1 tradeid, 'msft'  ticker, 'microsoft'  company, 100 shares, 'buy'  action, 'test' comments dual union             select 2 id, 1 tradeid, 'ibm'   ticker, 'ibm'        company, 200 shares, 'sell' action, 'test' comments dual union             select 3 id, 2 tradeid, 'yahoo' ticker, 'yahoo inc'  company, 50  shares, 'sell' action, 'test' comments dual union             select 4 id, 2 tradeid, 'goog'  ticker, 'google inc' company, 500 shares, 'buy'  action, 'test' comments dual         )          union --the trick union here          select             0 oid,0 soid, --and order columns here , in above query             id p_tradeid,             portfolio,             source ticker,             version company,             to_char(createdon) shares,             to_char(status) action,             '' comments                 (             --and again, make sure datatype of columns same!             select 1 id, 'test1' portfolio, 'rim' source, '1.0' version, '2016-01-20' createdon, 1 status  dual union             select 2 id, 'test2' portfolio, 'ios' source, '1.0' version, '2016-01-20' createdon, 1 status  dual         )     )     order          p_tradeid, soid, oid

this generate following:

1 | test1   | rim   |   1.0         | 2016-01-20 |  1       |  1 | 1       | msft  |   microsoft   | 100        |  buy     | test 2 | 2       | ibm   |   ibm         | 200        |  sell    | test 2 | test2   | ios   |   1.0         | 2016-01-20 |  1       |  3 | 3       | yahoo |   yahoo inc   | 50         |  sell    | test 4 | 4       | goog  |   google inc  | 500        |  buy     | test

see this sql fiddle link sql-server-2008 syntax


Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more