SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sybase List function with Sum with SQL server?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pirre0001
Starting Member

19 Posts

Posted - 03/02/2014 :  12:08:30  Show Profile  Reply with Quote
I have a subquery that retunrns multiple lines. To solve it, I have used the List function. The separation of List function is comma.

TblBill is in the main query with many other tables.
(Select List(tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr

This subquery returns: B12, B13, B14, B43 and so on...

Now I want to use SUM from another field (quant) from table tblBatch in the subquery and then separate those with a line break instead of comma.
Batch number may occur several times and I want to summarize these

I want the subquery return like this:

---Batchnr---
3 B12
4 B13
1 B14
1 B43

and so on..

How do I solve it the best way with SQL Server?

Edited by - Pirre0001 on 03/02/2014 12:49:27

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2014 :  12:48:38  Show Profile  Reply with Quote
List is not a t-sql function so I guess you may be on Oracle. This is MS SQL Server forum so solutions given here are mostly t-sql centric. Please try you luck at Oracle forums like www.orafaq.com or www.dbforums.com for Oracle related here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pirre0001
Starting Member

19 Posts

Posted - 03/02/2014 :  12:51:38  Show Profile  Reply with Quote
I edit my post exactly when you post. I will use this functionality in SQL server?
quote:
Originally posted by visakh16

List is not a t-sql function so I guess you may be on Oracle. This is MS SQL Server forum so solutions given here are mostly t-sql centric. Please try you luck at Oracle forums like www.orafaq.com or www.dbforums.com for Oracle related here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Edited by - Pirre0001 on 03/02/2014 12:52:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/03/2014 :  04:16:51  Show Profile  Reply with Quote
quote:
Originally posted by Pirre0001

I edit my post exactly when you post. I will use this functionality in SQL server?
quote:
Originally posted by visakh16

List is not a t-sql function so I guess you may be on Oracle. This is MS SQL Server forum so solutions given here are mostly t-sql centric. Please try you luck at Oracle forums like www.orafaq.com or www.dbforums.com for Oracle related here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





You cant as List is not there in SQL server.
in sql server your requirement can be obtained using query like this


Select batchnr,SUM(quant) AS TotalQty
From tblBatch 
group by batchnr 


and if you want this to be merged to something else do like below


SELECT *
FROM tblBill bl
INNER JOIN (Select tr_id ,batchnr,SUM(quant) AS TotalQty
From tblBatch 
group by tr_id ,batchnr )b
ON b.tr_id = bl.kfr_trans_id 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000