Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mike987
Starting Member
1 Post |
 Posted - 2012-07-15 : 09:37:46
|
| Hi All,I noticed there was a similar question asked but (I think) mine is a little different so here we go again... OK let me see if I can explain the problem. I have some SQL in an SSRS report that I've been asked to modify, essentialy it's looking at all the payments coming in and out, It lists various fields such as Currency, who's on the other side of the deal, Amounts etc. The DB has two tables that are used. Table A contains all the transactions that are yet to clear and Table B contains the cleared transactions. There is a big Union query in the report and it gets out all the data in a similar format to this... Euro's Our Side Their Side Rate Amount Status Our Corp Their Company 1.26 100000 Cleared Our Corp Their Company 1.26 34976 Pending ...there are a lot more fields than that but essentially the difference between each row is what the status is and obv the amount). What they want to do is have all the data on the same row (they're exporting out to CSV so this is important for them) and instead have this kind of arrangement... Euro's Our Side Their Side Rate Amount Cleared Amount pending Our Corp Their Company 1.26 100000 34976So I'll need to create a new field and have the logic behind it either in the Sql query or SSRS to only display a single row .It's a pretty huge Union query with all the different fields so the less changes I have to do the better and if you could explain it like you're talking to a 5 year old that would be great as I'm new to this ;)I feel this should be really easy but I just can't seem to find out how to do it and time is running out.All help greatly Appreciated. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-07-15 : 11:31:29
|
| Without actually seeing the queries - there isn't much we can do. With that said, I see two options:1) Convert the union query into a regular join - possibly an outer join.2) Use SSRS to group the credit/debits for each grouping - and use the group level to display the single row of data.Jeff |
 |
|
|
|
|
|
Taking two similar rows and combining them to one