Combining Multiple Rows Into One Row.

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining Multiple Rows Into One Row.

Author

Topic

raymurphy
Starting Member

2 Posts

Posted - 2011-05-19 : 15:58:33

Backend Database is SQL Server 2005.

Have table named hist_exch_rates holding exchange rates info with following structure :

ex_date char(8)
fromc char(3)
toc char(3)
rate_mul float
rate_div float

Table holds historical exchange rate info going back to 2007 (starting with ex_date value of
20070101) and up to current date (with ex_date value of 20110601).

For a fromc of AED and toc of CHF, I might have the following values in my table

ex_date fromc toc rate_mul rate_div
20070101 AED CHF 0.33 1.01
20070101 EUR CHF 0.44 2.01

20070201 AED CHF 0.55 3.01
20070201 EUR CHF 0.66 4.01

20070301 AED CHF 0.77 5.01
20070301 EUR CHF 0.88 6.01

So the twelve monthly rates for 2007 would have ex_date values of 20070101, 20070201, 20070301 up to 20071201.

Note that there could be up to 65 entries where toc is CHF for a specific ex_date (eg 20070101) rather than just 2 as shown in the example data above (each entry will have a different fromc value).

Not sure what terminology I need to use to describe what I want, but taking 2007 Rates as an example, I need to do the following :

take all 12 monthly values of rate_mul and rate_div for fromc 'AED' and toc of CHF
and output them into a single row alongside the fromc of 'AED' and toc of CHF;

take all 12 monthly values of rate_mul and rate_div for fromc 'EUR' and toc of CHF
and output them into a single row alongside the fromc of 'EUR' and toc of CHF;

and so on ....

Given the sample data above , the format I need to have the output in is so that I see :

fromc toc [rate_mul_20070101] [rate_div_20070101] [rate_mul for 20070201] [rate_div for 20070201]
AED CHF 0.33 1.01 0.55 3.01
EUR CHF 0.44 2.01 0.66 4.01

Hope I've explained this clearly enough for someone to come up with a solution. I'm sure I'm
missing an obvious way to go about this, but I'm still nowhere near to a working solution.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 16:30:07

The following will work with Madhi's dynamic pivot procedure (http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx):

EXEC dynamic_pivot 'SELECT fromc, toc, val FROM hist_exch_rates 
UNPIVOT(val FOR col IN (rate_mul, rate_div)) b
WHERE ex_date LIKE ''2007%''', 'col + ''_'' + ex_date', 'MAX(val)'

It doesn't sort the columns the same way but it pivots them correctly. If the column order is important you'll have to hack the procedure to do so.

raymurphy
Starting Member

2 Posts

Posted - 2011-05-20 : 07:01:00

Rob - thanks for the reply ...

This is exactly what I wanted. I hadn't came across Madhi's dynamic pivot procedure before, but I'm sure I'll find plenty of use for it in the future.

Your suggested of dynamic_pivot worked a treat (at this stage column sorting was not that important), so thanks for your assistance : much appreciated ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources