# question

## 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.

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

·
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

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

·
I hadn't hear of swap rates before. It's amazing what you can learn here. Thanks!
0 Likes 0 ·
·
@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 ·
·
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 ·
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

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

·
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 ·
·