June 6, 2011 at 12:25 am
Hi, I need your expertise on this issue. Basically the reason to do this is to group the rates so that my BI tool can read the table correctly.
For example, I have the following data:
foreign_curr local_curr year period ytd_ave_rate closing_rate
--------------------------
MYR MYR 01 2011 1.0000 1.0000
USD MYR 01 2011 0.3125 0.3030
Based on the example above, I intend to get the following result set and save it in my Views. Noticed that the ytd_ave_rate and closing_rate column name becomes the values under exchange_rate column.
foreign_curr local_curr year period exchange_rate rate_value
--------------------------
MYR MYR 01 2011 ytd_ave_rate 1.0000
MYR MYR 01 2011 closing_rate 1.0000
USD MYR 01 2011 ytd_ave_rate 0.3125
USD MYR 01 2011 closing_rate 0.3030
Any help you can provide with be greatly appreciated.
June 6, 2011 at 1:23 am
Look at UNPIVOT in Books Online ( a free help that comes with SQL Server). Online link to UNPIVOT - http://msdn.microsoft.com/en-us/library/ms177410.aspx
June 7, 2011 at 11:05 am
For this example you can use a simple UNION:
Setup:
IF OBJECT_ID(N'tempdb..#tbl') > 0
DROP TABLE #tbl;
GO
CREATE TABLE #tbl
(
foreign_curr CHAR(3),
local_curr CHAR(3),
period CHAR(2),
yr SMALLINT,
ytd_ave_rate FLOAT,
closing_rate FLOAT
)
GO
INSERT INTO #tbl
(
foreign_curr,
local_curr,
period,
yr,
ytd_ave_rate,
closing_rate
)
SELECT 'MYR',
'MYR',
'01',
2011,
1.0000,
1.0000
UNION ALL
SELECT 'USD',
'MYR',
'01',
2011,
0.3125,
0.3030
GO
Working query:
SELECT foreign_curr,
local_curr,
yr,
period,
'ytd_ave_rate' AS exchange_rate,
ytd_ave_rate AS rate_value
FROM #tbl
UNION
SELECT foreign_curr,
local_curr,
yr,
period,
'closing_rate',
closing_rate
FROM #tbl ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 7:55 pm
Thanks alot opc.three. Appreciate it!
June 8, 2011 at 9:11 am
No problem. I just realized something else, because you know up front that both result sets will be unique you can use UNION ALL instead of UNION and save yourself from doing an expensive "Distinct Sort" operation:
SELECT foreign_curr,
local_curr,
yr,
period,
'ytd_ave_rate' AS exchange_rate,
ytd_ave_rate AS rate_value
FROM #tbl
UNION ALL
SELECT foreign_curr,
local_curr,
yr,
period,
'closing_rate',
closing_rate
FROM #tbl ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply