June 28, 2010 at 12:25 pm
I have a table Orders that contains order information, and I need to produce reports that show the usage of the various input formats for orders. This needs to produce a report something like this:
| Format | # of Orders of this Format |
| web | 200 |
| FTP | 20 |
| unknown | 10 |
However, some of the rows in the Orders table will have Format='' (blank) and Format=NULL (a null field). For this report, I need to convert the Format field I query for to 'unknown' in both cases. How can I go about doing this? Bonus points if you can explain how I can produce a CSV ordered by Format in alphabetical order but where the line "Format,# of Orders of this Format" will always be the first line in the CSV regardless of alphabetical order.
June 28, 2010 at 12:45 pm
This is all untested air code since you didn't provide data in a format that is easy to work with.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
1)
SELECT COALESCE(NULLIF(Format,'')'unknown' )FROM Orders
2) Maybe something like...
SELECT
Field1,
100 AS CustomSort
FROM Orders
UNION
SELECT
'Format' AS Field1,
1 AS CustomSort
FROM Orders
ORDER BY CustomSort
Then you would exclude the CustomSort from your export to csv.
You could also use CASE in your ORDER BY statement
You didn't say how you were creating your csv file, in SSIS you would just output the column headers.
HTH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply