Formatting result set
- Can anyone help?
Thanx in advance.
I have a table called - Products.
I need to Get the - ProductID, ProductName, UnitPrice
where - ProductID is between 40 and 49
to output that looks like the following:
40 Boston Crab Meat $18.40
40 - ProductID = 2 char spaces
Boston crab meat - ProductName = 23 char spaces
$18.40 - UnitPrice = 5 char spaces
Product ID is int
ProductName is varchar
UnitPrice is money
In SQL Server 200 you can format your output with different functions
EXAMPLE:
Select Cast(ProductID As char(2)),
+ ' ' + cast(ProductName As char(23)),
+ ' ' + cast(UnitPrice As char (5))
From ProductsWhere ProductID Between 40 And 49;
This will give you the following results:
---- ------------------------ ------
40 Boston Crab Meat 18.4041
41 Jack's New England Clam 9.65
42 Singaporean Hokkien Fri 14.00
43 Ipoh Coffee 46.00
44 Gula Malacca 19.45
45 Rogede sild 9.50
46 Spegesild 12.00
47 Zaanse koeken 9.50
48 Chocolade 12.75
49 Maxilaku 20.00
(10 row(s) affected)
I want to format it so the space between Product ID and ProductName
is only 1 character space and the space between ProductName and Unit
price is only 1 character space And I want to loose the Column
Width markers and reduce the size of the columns to only display
the number of desired characters
2 | 23 | 5
is there any way to retrieve the columns without the headers?
I am outputting to text not a table This will be printed
Thanx again.
-
Allen,
Use varchar(nn) rather than char(nn) to reduce those gaps to 1 character.
To eliminate the "(10 row(s) affected)" message SET NOCOUNT ON.
To eliminate the colum headers:
a) from Query Analyzer uncheck the relevant box in Tools/Options/Results
or
b) from OSQL use the -h-1 switch
or
c) from bcp use the queryout and -c options
Cheers,
- Mark -
Thank you very much, worked great and learned something new.
-
Just one other remark: when in query analyzer if you want the result to switch from text to grid press the ctrl key and the letter d, if you want to switch from grid to text press the ctrl key and the letter t.
Just some additional info.
Gary
-
from OSQL use the -h-1 switch
I tried that but the result coming with like wraped. Like
1 | 121SAAABA |
151 Pvt (I) Ltd | BSAI241555 |
AZPCA | 039499 | 0629013 |
Any idea on how to set this out put not wrapped.
Or Is there any option like "SET NoCount On" to switch off the column header?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply