Formatting the numerical output

Hi All,

I need to control the output format of a request on "int" columns. For instance, if the "select" returns me a "0" (zero), I would like it to be return with 4 digit = "0000" because this results is then append to some other to built a phone-like number : +33 2121-0000-1544.
In that case, "0" is not equal to "0000"

My ASP request is in attach.

THX.

Fabrice.I found a solution ... Thank's anyway ;)

formatting textbox output

I have a report whcih displays information about a servers hardware
configuration. Unfortunatley the vendors application whcih collects and
stores this information, add spcaing between vaules in the form of [-] and
[+].
I'd like to remove this in the final rendering of the report for display
purposes.
see sample output:
ComputerSystem Name[-]ADM-EX01[+]SystemType[-]X86-based
PC[+]Manufacturer[-]Compaq[+]Model[-]ProLiant ML350
G3[+]Domain[-]MARBLEHEAD[+]DomainRole[-]Member
Server[+]NumberOfProcessors[-]2[+]TotalPhysicalMemory[-]2,146,979,840
Anyone have any ideas?
Much appreciated,
RCITGUYUse expression and Replace function. Remember, you can use VB in expressions.
Victor
"RCITGuy" wrote:
> I have a report whcih displays information about a servers hardware
> configuration. Unfortunatley the vendors application whcih collects and
> stores this information, add spcaing between vaules in the form of [-] and
> [+].
> I'd like to remove this in the final rendering of the report for display
> purposes.
> see sample output:
> ComputerSystem Name[-]ADM-EX01[+]SystemType[-]X86-based
> PC[+]Manufacturer[-]Compaq[+]Model[-]ProLiant ML350
> G3[+]Domain[-]MARBLEHEAD[+]DomainRole[-]Member
> Server[+]NumberOfProcessors[-]2[+]TotalPhysicalMemory[-]2,146,979,840
> Anyone have any ideas?
> Much appreciated,
> RCITGUY

Formatting Problem

Hi,

I'm trying to format the output of a report and can't seem to figure out how use the Matrix control to get what I want. Currently, I have it formatted as:

General Information <-- Head
Tag Number Line Size Line Schedule <-- Information

Service P&ID No. Tightness Requirements

Line No. Area Classification Location

Process Data
Fluid Specific Gravity

Transmitter Spec.
Mounting Type

NEMA Rating Power Supplied Analog Outputs

Ideally I'd like to have it formatted as:

General Information <-- Header centered
Tag Number Line Size Line Schedule

Service P&ID No. Tightness Requirements

Line No. Area Classification Location
__ <-- Divider Line between sections

Process Data
Fluid Specific Gravity
__

Transmitter Spec.
Mounting Type

NEMA Rating Power Supplied Analog Outputs

Any ideas or suggestions would be greatly appreciated. This shouldn't be THIS hard to do.

Thanks,

John.

It'll be a lot clearer to understand if you make up your examples in Excel and then just copy paste that into your post.|||

Adam,

Thanks for taking an interest. Ok, here's what's going on: I'm trying to render information where I have multiple (unknown at design time) Catagories which have multiple Attributes (also unknown at design time). The relationship is One Catagory, Many Attributes.

I'm using a Matrix control because it seems to be the Control to use? If it's not, let me know. What I'd like ideally is to: Add a Line to separate the different Catagories (i.e. General Information, Process Data, etc.), and center the Catagory title. Currently I'm forcing the Matrix to wrap at 3 by setting a property in the List Control's "Edit Details Group..." property to: =Ceiling(RowNumber(Nothing)/3). If you have any ideas on how to solve this through the designer, please let me know.

Here's an example of the data as it's currently being rendered:

General Information

Service

P&ID No.

Tightness Requirements

Tag Number

Line Size

Line Schedule

Line No.

Area Classification

Location

Process Data

Fluid

Specific Gravity

Transmitter Spec.

Mounting Type

Model No.

Support Materials

Accuracy

NEMA Rating

Power Supplied

Analog Outputs

Local Display

Operating Range

Dead Weight

Output Signal

Conduit Connection

Live Weight

Gross Weight

Manufacturer

Weigh Cell Data

Mounting

Temperature Compensation

Power Supplied

|||

On a side note, I've tried adding a Line control to the List, but it renders on every line. I also have no way to detect when the last line of a Catagory is being rendered. I've tried every way I can think of using the tool, but it's just not working for me.

TIA,

John.

|||I would suggest placing the matrix inside a list control. Group the list control by category which will result in a separate matrix being rendered per category. You can then place the category in a textbox above the matrix and also inside the list and center it.|||

Adam,

Mad thanks, that did the trick!!

John.

Formatting output from a SQL table

I have used a Winform textbox to enter the following data into a nvarchar field:

1. Choice number one

2. Choice number two

3 Choice number three

etc.

How do I print out the nvarchar field to look like the above? Can I do this in Reporting Services or something else? Thanks.

why can't you? have you tried?

doesn't sound difficult.

|||You could use the ReportViewer control in your application, create a report in local mode and use a datatable as the data source for the local report.sql

formatting output

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----
SET NOCOUNT ON
DECLARE @.errorCount INT
SELECT @.errorCount = COUNT(*) FROM error_report WHERE id != - 2 AND id != - 5
IF @.errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
--------
The Results in the bottom pane looks like this below
------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
------
But, when I do a select, they are like this below
--

NODETABLE 0 There are 2 duplicate subclass name: Specification
Propertytable 0 at Parentid = 2000000859 and propertyid = 721
Propertytable 0 at Parentid = 10122 and propertyid = 9That sounds like a grid limitation to me. I'd suggest Ctrl-T to set text mode, then Ctrl-E to execute the query again.

-PatP|||Hi Pat,
It outputted in the text format but the results are the same. However when I do select statement, it is fine.|||You need to explicitly specify the size for VARCHAR fields.|||Hi LOOKING AT THE ABOVE CODE, CAN YOU TELL ME WHERE IT IS?|||For example, here:

'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR(8)) )

But you also don't need that CAST.

'The Report was generated on ' + CONVERT(VARCHAR(8), GETDATE(), 1)

Formatting numbers in SQL

Hi
I need to format the output of a numeric value from SQL-server with a
chosen thousand-separator and decimal-separator.
The Access equivalent would be something like:
Select format(myNumber, "#.###,##") as myFormattedNumber from myTable;
I have searched the net for a solution, and found something about
FORMAT_STRING, but that doesn't seem to be usable in SQL-Server...
Can anyone help me with this?
Regards,
Johnny Nordtvedt, NorwayIn an N-Tier environment it's better to leave presentational features
out of the database and do them in your client/middle tier. Some users
might prefer to configure a different numeric format so it's probably
not something you ought to fix in a SELECT statement.
TSQL does have a CONVERT function, which provides some limited
formatting capability but only if you convert your numerics to a
string. Also, AFAIK the CONVERT function only supports comma as a
thousands separator by default. In reality you should find it much
easier to do the formatting in the client control or form that displays
the data.
David Portas
SQL Server MVP
--|||My problem is that these values is displayed in a Word-document, and the
program that collects these values and inserts them into word, is not
available for formatting, and will not ever be available. So basically, I
am stuck with formatting this in SQL-server.
Regards,
Johnny Nordtvedt, Oslo, Norway|||Is it possible to make a user defined function to do this? And how would I
go by to do this?
Still hope that someone can help me with this. I am certain someone has
done this before?
Regards,
Johnny Nordtvedt, Oslo, Norway|||DECLARE @.numeric NUMERIC(10,2)
SET @.numeric = 123456.78
SELECT
REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(10),CAST(@.numeric AS MONEY),1)
,',','~') ,'.',',') ,'~','.')
David Portas
SQL Server MVP
--|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
There are formatting functions on Merge fields in Word. Read the Word
Help file (in English version):
Field Types and Switches
Field Reference
Switches
How to: Highlight merge field, hit Alt-9. Add switches at end of
field:
/# will give numeric format.
/#.#,## I believe will format the number 99999.00 as 99.999,00
Also, read the Word Help articles on Merge Fields.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlWjTIechKqOuFEgEQLidACfWKC4/ST6Pc1f6yJC3xaIoOO4dC8AoKLM
k5KfIvuctrzjBfvZduWw4W0t
=lpsE
--END PGP SIGNATURE--
Johnny, Norway wrote:
> My problem is that these values is displayed in a Word-document, and the
> program that collects these values and inserts them into word, is not
> available for formatting, and will not ever be available. So basically, I
> am stuck with formatting this in SQL-server.

formatting in sql server

hi

below is my query

select * from guest.tbl1

i m getting result like this

id score

1 50
2 100

3 150

4 200

but i want this output like this

1 50 2 100

3 150 4 200

how to do this?

thanx

Where you want to display this record?In a web page or in the SQL server o/p?|||in sql server o/p|||

Code Snippet

CREATE TABLE #TMP1(ID INT IDENTITY (1,1),COL1 INT,COL2 INT)
CREATE TABLE #TMP2(ID INT ,COL3 INT,COL4 INT)

INSERT INTO #TMP1
(
COL1
,COL2
)
SELECT id
,val
FROM tbl1

INSERT INTO #TMP2(ID,COL3
,COL4
)
SELECT ID
,COL1
,COL2 FROM #TMP1 WHERE ID%2=1

SELECT * FROM #TMP1
SELECT * FROM #TMP2
SELECT T2.COL3
,T2.COL4
,T1.COL1
,T1.COL2 FROM #TMP1 T1 JOIN #TMP2 T2 ON T1.ID = T2.ID + 1
GO
DROP TABLE #TMP1
DROP TABLE #TMP2

|||

Afraid to believe the ID value , It may have gaps rite?

Code Snippet

Create Table #data (

[id] int ,

[score] Varchar(100)

);

Insert Into #data Values('1','50');

Insert Into #data Values('2','100');

Insert Into #data Values('5','150');

Insert Into #data Values('6','200');

Insert Into #data Values('9','200');

Insert Into #data Values('10','200');

If you use SQL Server 2005,

Code Snippet

;WITH CTE

as

(

Select

*,

Row_Number() Over(Order By ID) RID

From

#data

)

Select up.id,up.score,dwn.id,dwn.score from CTE up

left outer join CTE dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

If you use SQL Server 2000,

Code Snippet

Declare @.Table Table(

[id] int ,

[score] Varchar(100) ,

[RID] int identity(1,1)

)

Insert Into @.Table

Select Id,Score From #Data Order By 1;

Select up.id,up.score,dwn.id,dwn.score from @.Table up

left outer join @.Table dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

formatting dates

Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.Try this:

SELECT CONVERT(char(10),getdate(),103)|||Please.. use the 101 code for the date format..

like this:

SELECT CONVERT(char(10),getdate(),101)

the other example is another format.

You can consult all the date format in the SQL SERVER BOOKS ON LINE!.|||But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Just replace the getdate() with checkedOutDate. Do the same again with ReturnedDate (a separate query).

Originally posted by domiflichi
But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Look at my post from yesterday. I had the same question.
exdter|||That's what I thought you're supposed to do. But when I do that...here's my new SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),checkedOutDate,101), ReturnedDate FROM Checkouts

I get an error when trying to display my results of the query when encountering that field:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 173

If it helps: my webserver is Win2k Pro running Apache and PHP, and my SQL server is on Win2K server and it's actually MSDE 2000.|||Originally posted by exdter
Look at my post from yesterday. I had the same question.
exdter

I'm getting the same error message on my query:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 174

Here's the SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103), ReturnedDate FROM Checkouts

Why isn't this working for me?
Thanks,
domiflichi|||Try this

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP
, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate, ReturnedDate FROM Checkouts|||Thank you Brett!...it worked! And thank you to everybody else for getting me through each step, and being patient with this newbie. And thank you all for such quick replies!

Formatting Data Output

In a report that has a table that contains a number field, how do you
format the output to contain commas between thousands?On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
> In a report that has a table that contains a number field, how do you
> format the output to contain commas between thousands?
Select the cell(s) that need the thousands format (in Layout view) and
in the 'Format' field of the Properties window, enter: #,0
Regards,
Enrique Martinez
Sr. SQL Server Developer|||i already set the format to the fields, but even my windows regional
settings configuration is SPANISH MEXICO (wich means comma for thousands an
a period for decimals) im still seeing my reports with periods for thousands
and comma for decimals
what else i should check?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173303498.820749.284820@.n33g2000cwc.googlegroups.com...
> On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
>> In a report that has a table that contains a number field, how do you
>> format the output to contain commas between thousands?
>
> Select the cell(s) that need the thousands format (in Layout view) and
> in the 'Format' field of the Properties window, enter: #,0
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Thanks, that worked.
On 7 Mar, 21:38, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
> > In a report that has a table that contains a number field, how do you
> > format the output to contain commas between thousands?
> Select the cell(s) that need the thousands format (in Layout view) and
> in the 'Format' field of the Properties window, enter: #,0
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer|||On Mar 8, 3:56 am, robin9...@.hotmail.com wrote:
> Thanks, that worked.
> On 7 Mar, 21:38, "EMartinez" <emartinez...@.gmail.com> wrote:
> > On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
> > > In a report that has a table that contains a number field, how do you
> > > format the output to contain commas between thousands?
> > Select the cell(s) that need the thousands format (in Layout view) and
> > in the 'Format' field of the Properties window, enter: #,0
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome.
Regards,
Enrique Martinez
Sr. SQL Server Developer

formatted output

Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........

Regards

SubhasSubhas (dba_sybase2003@.yahoo.com) writes:
> Is there a way to get stored procedure texts out in a formatted way
> like defncopy in Sybase??? The way they print it out really
> sucks.........

There is no command-line utility, but there are scripting facilities
both in Query Analyzer and Enterprise Manager.

In QA click F8 to get an Object Brower. Right-click a procedure and you
find the scripting option. In EM, right-click a database and pick
All Tasks and then Generate SQL Scripts.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Formating of columns in output of a SQL Statement in Query Analyzer

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdjand the difference is?|||What is the difference between two outputs?|||above the example is not appearing like I want but I want to delimite the size of a column setting the tool.|||Originally posted by joelperez
Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

left(col1,10) ?|||Originally posted by snail
left(col1,10) ?

yes left function for me!!!

Thanks!!!|||...or use CAST or CONVERT to specify the column widths of the resulting fields.|||Originally posted by blindman
...or use CAST or CONVERT to specify the column widths of the resulting fields.

Thanks for your recommendation blindman

Formating numbers in SSIS

I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152

output need it to be -

00000.1520

How can I do this?

You'll have to convert it a string and prepend/append the zeroes.

-Jamie

|||Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.|||

Godai B wrote:

Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.

Too many steps? Really?

"0000" + (DT_STR, 20, 1252) 0.1520

That doesn't seem like too many to me. Can you give me examples of format functions like you require?

-Jamie

|||

this field is derived from 2 columns in a sql server table and the datatype is decimal(18,3)

the output format needs to be 000000.0000 .

the input field can have a single digit or upto six digits before the decimal point. if it has a single digit then i need to prepend "00000", if there are 2 digits then i prepend "0000" and so on.

same with digits after the decimal point.

ex: Input -->10.120 Output --> 000010.1200

Input --> .1 Output --> 000000.1000

what i did was, used the findstring function in the derived column to find the decimal point position and then got the predecimal digits and postdecimal digits. then with the help of length function i prepended or appended the zero's and then finally concatenated the predecimal and postdecimal digits.

Hope u can help me with an easy way to do this.

|||

I would use the FINDSTRING function as you have done to get the whole and the mantissa. But instead of using LENGTH I would just do this:

RIGHT("000000" + [wholepart], 6) + "." + REVERSE(RIGHT(REVERSE([mantissapart] + "0000"), 4))

Yeah, maybe a format function would be good.

-Jamie

Format the column with

Hello,
Can you tell me how can i format the column with in the output of a query?
The results must be returned as text and my output is like this:
y x
-- --
1 2
the way im treating the output is to cast the field as varchar(xxx):
select cast(y as varchar(1)), cast(x as varchar(1))
from xxxxxx
y x
- -
1 2
It is possible to do it with a different way?
Thanks and best regards.
Hi
SELECT coalesce(x,'')+coalesce(y,'') as blabla
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.
|||SQL Server returns meta-data with the result set. This meta-data includes information about the
datatype for each columns. A tool like Query Analyzer uses this meta-data to define the length of
the columns presented. Note that when you execute into grid, things are different. The columns are
indeed made smaller to include more information in what you see. So, in other words, what you see if
done by the client application, not SQL server itself.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.
|||Thanks Uri,
Best regards
"Uri Dimant" wrote:

> Hi
> SELECT coalesce(x,'')+coalesce(y,'') as blabla
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
>
>

Format the column with

Hello,
Can you tell me how can i format the column with in the output of a query?
The results must be returned as text and my output is like this:
y x
-- --
1 2
the way im treating the output is to cast the field as varchar(xxx):
select cast(y as varchar(1)), cast(x as varchar(1))
from xxxxxx
y x
- -
1 2
It is possible to do it with a different way?
Thanks and best regards.Hi
SELECT coalesce(x,'')+coalesce(y,'') as blabla
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||SQL Server returns meta-data with the result set. This meta-data includes information about the
datatype for each columns. A tool like Query Analyzer uses this meta-data to define the length of
the columns presented. Note that when you execute into grid, things are different. The columns are
indeed made smaller to include more information in what you see. So, in other words, what you see if
done by the client application, not SQL server itself.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||Thanks Uri,
Best regards
"Uri Dimant" wrote:
> Hi
> SELECT coalesce(x,'')+coalesce(y,'') as blabla
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> > Hello,
> >
> > Can you tell me how can i format the column with in the output of a query?
> > The results must be returned as text and my output is like this:
> >
> > y x
> > -- --
> > 1 2
> >
> > the way im treating the output is to cast the field as varchar(xxx):
> > select cast(y as varchar(1)), cast(x as varchar(1))
> > from xxxxxx
> >
> > y x
> > - -
> > 1 2
> >
> > It is possible to do it with a different way?
> >
> > Thanks and best regards.
>
>

Format the column with

Hello,
Can you tell me how can i format the column with in the output of a query?
The results must be returned as text and my output is like this:
y x
-- --
1 2
the way im treating the output is to cast the field as varchar(xxx):
select cast(y as varchar(1)), cast(x as varchar(1))
from xxxxxx
y x
- -
1 2
It is possible to do it with a different way?
Thanks and best regards.Hi
SELECT coalesce(x,'')+coalesce(y,'') as blabla
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||SQL Server returns meta-data with the result set. This meta-data includes in
formation about the
datatype for each columns. A tool like Query Analyzer uses this meta-data to
define the length of
the columns presented. Note that when you execute into grid, things are diff
erent. The columns are
indeed made smaller to include more information in what you see. So, in othe
r words, what you see if
done by the client application, not SQL server itself.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||Thanks Uri,
Best regards
"Uri Dimant" wrote:

> Hi
> SELECT coalesce(x,'')+coalesce(y,'') as blabla
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
>
>

Format Question

Hi,
I have a column with numbers like this and the output desired.
dataset output desired
0.170 0.17
0 0
1.120 1.12
0 0
But when i format it with number, #.## etc. i get "0" as 0.00 - which i
dont want
any suggestions ?
Thanks
RPRP,
If I try 0.## I get the results you are looking for. The data type for the
data is dec(5,3). With #.## I get .00 for 0 rather than 0.00. What is the
datatype of your data?
RMac
"RP" wrote:
> Hi,
> I have a column with numbers like this and the output desired.
> dataset output desired
> 0.170 0.17
> 0 0
> 1.120 1.12
> 0 0
> But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> dont want
> any suggestions ?
> Thanks
> RP
>|||I take that back. I'm seeing 0.00 for zero. Let me see what I can come up
with. Apologies.
"RMac" wrote:
> RP,
> If I try 0.## I get the results you are looking for. The data type for the
> data is dec(5,3). With #.## I get .00 for 0 rather than 0.00. What is the
> datatype of your data?
> RMac
>
> "RP" wrote:
> > Hi,
> > I have a column with numbers like this and the output desired.
> >
> > dataset output desired
> > 0.170 0.17
> > 0 0
> > 1.120 1.12
> > 0 0
> >
> > But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> > dont want
> > any suggestions ?
> > Thanks
> > RP
> >|||RP,
Give G5 a try. If you need a larger precision specifier, just change the 5
to the desired number.
RMac
"RP" wrote:
> Hi,
> I have a column with numbers like this and the output desired.
> dataset output desired
> 0.170 0.17
> 0 0
> 1.120 1.12
> 0 0
> But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> dont want
> any suggestions ?
> Thanks
> RP
>|||G5 worked perfect. Thanks RMAC.
(Actually in the meantime i changed it to #.00, and in expression i put an
iif(no <=0 then '0') ..blah blah...
But G5 is much much easier to use. Thanks again.
"RMac" wrote:
> RP,
> Give G5 a try. If you need a larger precision specifier, just change the 5
> to the desired number.
> RMac
> "RP" wrote:
> > Hi,
> > I have a column with numbers like this and the output desired.
> >
> > dataset output desired
> > 0.170 0.17
> > 0 0
> > 1.120 1.12
> > 0 0
> >
> > But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> > dont want
> > any suggestions ?
> > Thanks
> > RP
> >

Format output of FOR XML RAW

Hi! I use SELECT FOR XML RAW on MSSQL 2000.
I would like to be able to format the XML output so that instead of
having:
<row a="asd1"...>
<row a="asd2"...>
<row a="asd3"...>
I would get
<1 a="asd1"...>
<2 a="asd2"...>
<3 a="asd3"...>
or better yet be able to assign a specifc column value to replace
"row".
This is because I want to load the XML stream into LUA tables, and LUA
tables need unique indexes.
Thanks in advance.
Jeff Lambert
RAW mode in SQL2K generates elements with name 'row'. If you want to change
element name (custom name) you have to use EXPLICIT or AUTO (using aliases)
mode. In SQL 2005 you are able to give custom name with RAW clause.
/Patrick
"Jeff Lambert" <xradicalx@.gmail.com> wrote in message
news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
> Hi! I use SELECT FOR XML RAW on MSSQL 2000.
> I would like to be able to format the XML output so that instead of
> having:
> <row a="asd1"...>
> <row a="asd2"...>
> <row a="asd3"...>
> I would get
> <1 a="asd1"...>
> <2 a="asd2"...>
> <3 a="asd3"...>
> or better yet be able to assign a specifc column value to replace
> "row".
> This is because I want to load the XML stream into LUA tables, and LUA
> tables need unique indexes.
> Thanks in advance.
> Jeff Lambert
|||Also note that <1 a="asd1"/> is not a well-formed XML element (names cannot
start with numbers).
If you want to generate something like <row1/><row2/> etc. you would need an
XSLT stylesheet that creates such elements and copies all the attributes
over.
What is LUA exactly?
Best regards
Michael
"Patrick Akerblom" <patrik.akerblom@.ontrax.se> wrote in message
news:%23m2N2x2qEHA.2900@.TK2MSFTNGP12.phx.gbl...
> RAW mode in SQL2K generates elements with name 'row'. If you want to
> change element name (custom name) you have to use EXPLICIT or AUTO (using
> aliases) mode. In SQL 2005 you are able to give custom name with RAW
> clause.
> /Patrick
> "Jeff Lambert" <xradicalx@.gmail.com> wrote in message
> news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
>

format output as scientific notation (was "sql 2000 question")

SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval

how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.I would strongly suggest that you do the formatting at either the web server (using PHP), or better yet at the client (possibly using Javascript). This would allow you to use locale specific information that isn't usually available to SQL Server.

If you really have to format the output at the SQL Server, it could be done using a SQL UDF that called xp_sprintf or other related tools. This means that every query will need to be formatted using the locale of your SQL Server, which is usually a huge problem for application globalization.

-PatP

format excel output

In my report I have used the "jump to url" for the report output but when I export this to excel I get a column with the correct data and also the url.

Is there anyway of exporting the data without the url?

I want my users to be able to view the report and click on the column the want to URL jump to but also be able to export just the raw data without the URL?

No. For this I've had to create 2 versions of my reports, 1 with formatting, layout, links etc. and the other just plain data. I then add a link to the formatted version (attached to an excel icon) and just pass through the current parameter values i.e. this is done by constructing the URL using an expression and additionally appending rs:Format=EXCEL to the end of it.

|||Is there any way of having a column that shows up on the report be can't be exported to excel?|||

I thought I already answered that?

The answer is No. You cannot detect within the report which renderer was used.

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance