question

AbhiD avatar image
AbhiD asked

how to get this output file

i have a table like this > code CountryCode price > AUD= au 1 > BRL= br 2 > CAD= ca 3 > CHF= ch 4 > CLP= cp 5 > AUD1M= au 6 > BRL1MNDF= br 7 > CAD1M= ca 8 > CHF1M= ch 9 > CLP1M= cp 10 > AUD3M= au 11 > BRL3MNDF= br 12 > CAD3M= ca 13 > CHF3M= ch 14 > CLP3M= cp 15 and i want an text/csv file as out put which looks like this countrycode 7day 14day 21day au 1 6 11 br 2 7 12 ca 3 8 13 ch 4 9 14 cp 5 10 15 how to do this?? FYI I have used single digit for prices to make the example more simpler and understandable but in real time the prices value is very random.
ssistsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
Hi, first of all, it looks like Swap rates and probably each of this rate type should be in it's own column. 1M mean 1 month therm, 3M means 3 month rates. 3MNDF means 3 month Non Deliverable Forward rate and it's different from the 3M so probably also should be in it's own column. If you have a fixed list of the terms, you can can create a static PIVOT table for them. In case the terms can change there, you can create a dynamic PIVOT as @KenJ mentioned. Also if we assume, that the the currency without any term specification is 1W then you can take a look on below example and you can start from there. Then you can e.g. create a SSIS package which will use the PIVOT tables and export the data into a flat file or use eg. BCP. CREATE TABLE #rates ( code varchar(9), CountryCode char(2), price money ) GO INSERT INTO #rates (code, CountryCode, price) SELECT 'AUD=', 'au', 1 UNION ALL SELECT 'BRL=', 'br', 2 UNION ALL SELECT 'CAD=', 'ca', 3 UNION ALL SELECT 'CHF=', 'ch', 4 UNION ALL SELECT 'CLP=', 'cp', 5 UNION ALL SELECT 'AUD1M=', 'au', 6 UNION ALL SELECT 'BRL1MNDF=', 'br', 7 UNION ALL SELECT 'CAD1M=', 'ca', 8 UNION ALL SELECT 'CHF1M=', 'ch', 9 UNION ALL SELECT 'CLP1M=', 'cp', 10 UNION ALL SELECT 'AUD3M=', 'au', 11 UNION ALL SELECT 'BRL3MNDF=', 'br', 12 UNION ALL SELECT 'CAD3M=', 'ca', 13 UNION ALL SELECT 'CHF3M=', 'ch', 14 UNION ALL SELECT 'CLP3M=', 'cp', 15 --========================= --FIXED PIVOT --========================= SELECT Currency, CountryCode, [1W], [1M], [1MNDF], [3M], [3MNDF] FROM (SELECT LEFT(code, 3) AS currency ,CASE WHEN (SUBSTRING(code,4,LEN(code) - 4)) = '' THEN '1W' ELSE (SUBSTRING(code,4,LEN(code) - 4)) END AS TERM ,CountryCode ,price FROM #rates) AS Source PIVOT ( MIN(price) FOR TERM IN ([1W], [1M], [1MNDF], [3M], [3MNDF]) ) AS PivotTable --========================= --DYNAMIC PIVOT --========================= --put distinct terms into coma separated list to be used by dynamic PIVOT DECLARE @terms nvarchar(4000); DECLARE @sql nvarchar(4000); WITH Terms AS ( SELECT DISTINCT CASE WHEN (SUBSTRING(code,4,LEN(code) - 4)) = '' THEN '1W' ELSE (SUBSTRING(code,4,LEN(code) - 4)) END AS TERM FROM #rates ) SELECT @terms = STUFF((SELECT ',[' + TERM + ']' FROM Terms FOR XML PATH('')), 1, 1, '') SELECT @terms SET @sql = N'SELECT Currency, CountryCode, ' + @terms + N'FROM (SELECT LEFT(code, 3) AS currency ,CASE WHEN (SUBSTRING(code,4,LEN(code) - 4)) = '''' THEN ''1W'' ELSE (SUBSTRING(code,4,LEN(code) - 4)) END AS TERM ,CountryCode ,price FROM #rates) AS Source PIVOT ( MIN(price) FOR TERM IN (' + @terms + N') ) AS PivotTable' EXEC (@sql) DROP TABLE #rates
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
I hadn't hear of swap rates before. It's amazing what you can learn here. Thanks!
0 Likes 0 ·
AbhiD avatar image AbhiD commented ·
@pavel thanks for the answer but is there any way i can make this #rates table from my file directly instead of manually creating this table bcoz the data is like 200/300 rows in my table so manually typing those everyday in #rates table will be pain... FYI i need to create this file daily.
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
And where you have your data? In table? then use your table instead of My Table. In case you have the data in text/csv file, you can impor them using the ImportExport wizarad in SSMS, or import the file using SSIS or BCP. The easies one wil be to use the Import/Export wizard (Right Click on the Database in the object explorer/Task/Import Data.
0 Likes 0 ·
KenJ avatar image
KenJ answered
it wasn't clear by the question, but this query assumes that price always increases with the number of days and that there are always exactly 3 rows in the "Prices" table for each CountryCode SELECT p.CountryCode , p.price AS [7day] , p1.price AS [14day] , p2.price AS [21day] FROM prices AS p INNER JOIN prices AS p1 ON p.countrycode = p1.countrycode AND p.price < p1.price INNER JOIN prices AS p2 ON p1.countrycode = p2.countrycode AND p1.price < p2.price ORDER BY CountryCode If something in the code column determines price/day order, or if you don't have exactly 3 rows in the "Prices" table for each CountryCode, you will need something a little more generic
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AbhiD avatar image AbhiD commented ·
hey kenj ..prices field is very very random..there is no pattern in the price column.. .i took it like 1 2 3 4..15 to make my question more simpler .. i think something has to be done using the code or the country code and not the prices .. and there are lot lot more columns other than 7 14 and 21 days... so what should i do??
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Before you can do much else, you need to identify what causes a particular price to display in a particular "day" column. If the prices can go up OR down as time progresses, you have to have some other indicator that tells you which price to place in which day column. I don't think it can be country code because that's used for grouping the prices. Is it the "code" column? If so, we'll need to know how the character patterns identify the appropriate day column to provide any useful help. Let's take Canada (ca) for example... What is it about CAD= that says 3 is the 7day price? What is it about CAD1M= that says 8 is the 14day price? What is it about CAD3M= that says 13 is the 21day price? What is it about CAD = that says x would be the 28day price, etc? It looks like a pattern could be developing there but, out of all the people I've ever met, you are the only one who can positively identify it. Once you identify how the price rows are put with the day columns, I think you can use one of the Dynamic Pivot answers on this forum to get the prices into the right columns: http://ask.sqlservercentral.com/search/?q=dynamic+pivot&Submit=search&t=question
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.