September 6, 2012 at 1:13 pm
I need to combine multiple rows into one row for each id number so that I can display multiple corresponding trans_amt columns for each person. A person may have one or more rows with a trans_cde = '200' with corresponding trans_amt, or trans_cde = '201' with corresponding trans_amt. Below is the code I am working with. Any advice would be appreciated.
SELECT trans_hist.id_num,
name_master.last_name,
name_master.first_name,
trans_hist.trans_dte,
trans_hist.folio,
name_master.email_address,
trans_hist.trans_amt
FROM name_master,
trans_hist
WHERE ( trans_hist.id_num = name_master.id_num ) and
( ( trans_hist.fund_cde in ( 200,201 ) ) AND
( trans_hist.trans_amt > 0 ) AND
( trans_hist.subsid_trans_sts = 'C' ) AND
(trans_hist.trans_dte >= '2012-08-1' AND
trans_hist.trans_dte <= '2012-08-31') AND
trans_hist.acct_cde = '99 99999999 99999' )
September 6, 2012 at 1:29 pm
Can you take a look at this article so we can help?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 6, 2012 at 1:31 pm
pelusodm (9/6/2012)
I need to combine multiple rows into one row for each id number so that I can display multiple corresponding trans_amt columns for each person. A person may have one or more rows with a trans_cde = '200' with corresponding trans_amt, or trans_cde = '201' with corresponding trans_amt. Below is the code I am working with. Any advice would be appreciated.SELECT trans_hist.id_num,
name_master.last_name,
name_master.first_name,
trans_hist.trans_dte,
trans_hist.folio,
name_master.email_address,
trans_hist.trans_amt
FROM name_master,
trans_hist
WHERE ( trans_hist.id_num = name_master.id_num ) and
( ( trans_hist.fund_cde in ( 200,201 ) ) AND
( trans_hist.trans_amt > 0 ) AND
( trans_hist.subsid_trans_sts = 'C' ) AND
(trans_hist.trans_dte >= '2012-08-1' AND
trans_hist.trans_dte <= '2012-08-31') AND
trans_hist.acct_cde = '99 99999999 99999' )
That sort of depends on how you want to retrieve this data. If you want to combine all rows into a single column (like a csv) or if you want a dynamic number of columns. The approach will be drastically different depending on what you want.
Since you are new around here you should take a look at the first link in my signature about best practices when posting questions.
I would make a suggestion that you use the current style for joins instead of the old style. It makes it easier to read and you are less likely to get an accidental cartesian product.
Something like this.
SELECT trans_hist.id_num,
name_master.last_name,
name_master.first_name,
trans_hist.trans_dte,
trans_hist.folio,
name_master.email_address,
trans_hist.trans_amt
FROM name_master
inner join trans_hist on trans_hist.id_num = name_master.id_num
WHERE trans_hist.fund_cde in ( 200,201 )
AND trans_hist.trans_amt > 0
AND trans_hist.subsid_trans_sts = 'C'
AND trans_hist.trans_dte >= '2012-08-1'
AND trans_hist.trans_dte <= '2012-08-31'
AND trans_hist.acct_cde = '99 99999999 99999'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2012 at 1:32 pm
Thanks for your advice, much appreciated.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply