MS SQL Server :: Query For Table Formatting

Formatting In Sql Query

Dec 12, 2006

Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?

I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this

select distinct(companyname),max(calldate) from tablename

what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?

View 1 Replies View Related

SQL Query Formatting

May 24, 2006

Hi

Im new to sql server what i wanted to do was as follows:

i got a table with application_name and app_version

it looks like as follows

Application_name App_ver
-----------------------------
Oracle 8.1.0
Oracle 8.5
Oracle 9.1
Oracle 10.1
Sql Server 6.5
Sql Server 7
Sql Server 2000
Sql Server 2005

i want to get query the table and produce an out put as follows:

Application_name App_vers
-------------------------------------------
Oracle 8.1.0, 8.5, 9.1, 10.1
Sql Server 6.5, 7, 2000, 2005

please note i need the commas in place
i have written several queries to try and do it but no luck

im using sql server 2000

i will be greatful if any one can help...

View 7 Replies View Related

Formatting Results Of The Query

Jul 23, 2004

my current query returns data as follows:

stopID reason count

h1 e 5
h1 v 2
h1 a 1
h2 v 2
h2 m 8
h3 t 6
h3 v 1

Is it even possible to sort/format this query in SQL to
return the same data but in this type of format? Using
the values of the 'reason' column as column names?

stopID e v a m t

h1 5 2 1 0 0
h2 0 2 0 8 0
h3 0 1 0 0 6

thanks

-r

View 14 Replies View Related

Query Formatting In SQL Management Studio

Feb 2, 2007

I recently upgraded my SQL Server to 2005 and instead of the Enterprise Manager I am now using SQL Server Management Studio.

Does anyone know how to turn off query formatting in views?

In enterprise manager I use to be able to double-click the view and it would open an sql editor which left the code exactly how I formatted it (space, returns etc for ease of reading). Now in Management Studio, each time I modify a view it reformats it so it become confusing to build them.

View 1 Replies View Related

Formatting Dates As YYYY/MM/DD For A Particular Query

Oct 3, 2005

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2)).... Which seems so rediculous. There's gotta be a better way!

View 3 Replies View Related

Formatting Decimal Places In A Query In MS SQL

Jan 14, 2004

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.

View 5 Replies View Related

Formatting @query Results Using Xp_sendmail

Jul 20, 2005

I know formatting should be handled in the client app and not in SQL Serverbut this is the situation I must develop in so any help would beappreciated.I'm running the following in Query AnalyzerUSE PubsDECLARE @MessageSubject VARCHAR(50)SELECT @MessageSubject = 'Report'EXEC master.dbo.xp_sendmail 'me@mine.com',@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',@subject = @MessageSubjectThe results in my email look like:au_fname au_lname------------------- ----------------------------------------Abraham BennetReginald Blotchet-HallsCheryl CarsonI would like the results to be like:First Name: AbrahamLast Name: BennetFirst Name: ReginaldLast Name: Blotchet-HallsFirst Name: CherylLast Name: CarsonThanks

View 2 Replies View Related

Should Be Easy - Help Formatting A Conditional Query.

Aug 28, 2006

SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.

CREATE PROCEDURE dbo.GetByVersion
(
@targetVersion varchar(30),
@product varchar(50)
)
AS
SET NOCOUNT ON;
SELECT * FROM  MyTable
WHERE (product = @product)
AND
CASE
    WHEN @targetVersion='' THEN (targetVersion='')
    ELSE (targetVersion LIKE @targetVersion + '%')
END
GO

I get a syntax error in the Stored Procedure editor on an equal sign in this line:
    WHEN @targetVersion='' THEN (targetVersion='')

What I want is this (in psuedocode):
if @targerVersion is blank
    search for records where the targetVersion column = blank
else
    search for records where the targetVersion column starts with @targetVersion

Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)

View 5 Replies View Related

Formatting Ints In Table

Oct 5, 2004

Hi I am storing a customer code in my db that will always be 9 digits long. Sometimes the first digit is zero which gets knocked off when added in.

e.g code 050101111 is stored as 50101111

Can anyone suggest a way of formatting it so it will always be 9 digits long. A similar thing can be done in Excel, anyone have any ideas?

much appreciated

Tom

View 2 Replies View Related

Problems Formatting My Table...

Jul 27, 2006

I have a table that looks like this...

City State Server Type
Chicago IL Svr1 Data
Chicago IL Svr2 Data
Chicago IL Svr3 Backup
Chicago IL Svr4 Backup
Atlanta GA Svr1 Data
Atlanta GA Svr2 Data

I already have a function to convert the server rows into a comma delimited string...
ALTER function dbo.fnGetServers (@City varchar(25), @State varchar(25), @Type varchar(25), @Tree varchar(25))
returns varchar(1000)
as
begin
declare @NewSvrCol varchar(1000)
select @NewSvrCol = ''
select @NewSvrCol = @NewSvrCol + Server + ', ' from serverops.dbo.v_userviews where city = @City and State = @State and Type = @Type and Tree = @Tree
select @NewSvrCol = left(@NewSvrCol, len(@NewSvrCol)-1)
return(@NewSvrCol)
end

Any suggestions on how to display the table in the following format?

City State DataSvrs BackupSvrs
Chicago IL Svr1,Svr2 Svr3,Svr4
Atlanta GA Svr1,Svr2 NULL

View 2 Replies View Related

Formatting For Writing My Query To An Excel Sheet

Feb 5, 2004

Hi ,

I am executing my query and writing it to an excel sheet by choosing "query" results to an excel sheet. It does not format them well. I have trimmed my fields too. There are about 10 fields in the database and I need to show them in the excel file all the ten fields adjacent to one another. Is there anyway I can format them other than the programming aspect.

Thanks

View 1 Replies View Related

How To Retain Text Formatting In A Table

Aug 17, 2007

What is the best way to parse large amounts of formatted text data
into a table so that it can be retrieved with as much formatting
retained as possible - particularly paragraphs? Will each
paragraph need to be inserted into its own row to be retrieved as a
paragraph?

View 11 Replies View Related

Formatting Attribute In Dimension Table

Apr 7, 2008

I have date and float in attribute in a dimension table. If deploy my cube and I try to create a report with RS, I can't format this data. The value seems to be a String so I have to do a CDate or a CDbl before formating it.
I have no problem when I try to format my measures.

Thanks for your help.
Cheers,

View 15 Replies View Related

Formatting Totals With The Table Footer

Aug 30, 2007

Here's a scenario that i'm trying to figure out.

In the table details, i return order number, item, manufacturer, and total cost of the order.
This is what it originally looked like:

Order Number Item Manufacturer Total Order Cost
1 portable DVD Company A $100
1 portable DVD Company B $100
1 portable DVD Company C $100

2 portable DVD Company B $100
2 portable DVD Company D $100
2 portable DVD Company F $100

Grand Total $600

I can get the table to look like this after hiding duplicates:

Order Number Item Manufacturer Total Order Cost
1 portable DVD Company A $100
1 portable DVD Company B $100
1 portable DVD Company C $100

2 portable DVD Company B $100
2 portable DVD Company D $100
2 portable DVD Company F $100

Grand Total $600

The problem is the grand total. It should be $200 but it takes in the all total costs in the row because I have:

=FormatCurrency(Sum(Fields!TotalCost.Value)) in the footer and it'll sum up all.

I'm stumped here. Any suggestions are greatly appreciated.

Thanks a lot for taking the time to read.

View 3 Replies View Related

Create Table For Phone Number Formatting?

Jul 15, 2014

I am trying to get my SQL create table to work for my phone number formatting and it is not. When I create the below code, the default is set to 3 numbers only.

CREATE SET TABLE dl_qpt_cqe.contacts, NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL

(contact_id integer not null ,contact varchar(50) , jobtitle varchar(50), dept varchar(50), phone integer format '999-999-9999', phone_ext varchar(10), email varchar(50), constraint pk primary key (contact_id));

Is there some other way I need to format the phone portion so the default is 999-999-9999?

View 1 Replies View Related

Reporting Services :: Select Query - Formatting A Date Affecting MIN

Sep 2, 2015

In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'

This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000

So I amended the SELECT query to:

MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'

This brings the date through as 11/06/2015, which is preferable.

But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.

Obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?

View 3 Replies View Related

Is There A Way To Do Mass Formatting Of Cells In A Table Instead Of Cell By Cell?

Oct 5, 2007

Hi,
I'm working with MRS and I've got a table with a lot of entries. For each value in the table I'm trying to get the text colour to be set to 'red' when the value of the cell is less than 0. Otherwise remain black.

I can do this by setting the colour property cell by cell. But I have a lot of cells in the table. Is there a way to set the statement to apply to ALL cells in the table?

Basically I'm asking if there is a way to set the property in bulk instead of going through tediously cell by cell.

Any help would be much appreciated. Thanks!

View 4 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

View 11 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

How To Create A Make-Table Query With A Union Query

Oct 30, 2006

I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View 2 Replies View Related

Query To Give All Table Sizes On A Database (was Query Help)

Mar 9, 2006

Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks

View 2 Replies View Related

Naming Query Column Name Of Resulting Table Name From Query

Oct 23, 2014

I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..

Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.

I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..

View 14 Replies View Related

Transact SQL :: Query Plan Shows Table Not Even In Query?

Jul 22, 2015

I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.

I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.

View 10 Replies View Related

Query To Only Display Information From One Table Where The Foreign Key Doesnt Exist In The Other Table.

Nov 28, 2006

I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.

View 1 Replies View Related

Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table

Dec 20, 2007

I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.

View 1 Replies View Related

Append Query From Access Table To Linked SQL Server Table Failing

Jun 18, 2004

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
Brad

View 4 Replies View Related

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?

Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

View 5 Replies View Related

Query To Return Records Where One Table Is Void Of Linked Data In Another Table

Feb 13, 2008

I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.

Thanks in advance,

Mark

View 5 Replies View Related

Transact SQL :: Create Hierarchies Table Or Query From Multi Parent Table?

May 21, 2015

convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.

ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent

View 13 Replies View Related

Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table

Aug 5, 2007

I have the following query in sql 2005:

PROCEDURE [dbo].[uspInsert_Blob] (

@fName varchar(60),

@fType char(5),

@fID numeric(18, 0),

@bID char(3),

@fPath nvarchar(60)

)

as

DECLARE @QUERY VARCHAR(2000)

SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)

SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)

AS Document"

EXEC (@QUERY)

This puts some values including a pdf or .doc file into a table, tblDocTable.

Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.

tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.

Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.

Any ideas how to do this?

View 1 Replies View Related