Formating SQL Query Results

May 22, 2006

Hi ppl

Could someone please tell me how I could format my output to look something like this:

What I have:

Code:


customer street city telnum
---------- ---------- ---------- ----------
Ann 12 bpem there 34215
Ben 2 blim here 344365
Carl 512 crom there 34725
Dean 62 blon here 2132345
El 142 brom there 657345
Frank 71 clom here 6345215



What I would like:
NOTE: I edited this myself incase you are confused about how I got these results, not that Im calling anyone stupid, just to make clear up any possible misunderstandings

Code:


customer contact details
---------- -----------------
Ann 12 bpem
there
34215
Ben 2 blim
here
344365
Carl 512 crom
here
34725
...... etc



Thanks for taking the time with this.
Your help is always appreciated.

Justin

View 8 Replies


ADVERTISEMENT

Formating Results In Query Analyzer

Feb 19, 2004

I have a table as follows:
A NO Balance [decimal (16,5)]
---------------- -------------------------------------------------- ------------------
18.00 001000000000000 -37.982
19.00 002000000000000 -17.656


In the Query analyzer when I try:
SELECT * FROM TABLE1

A NO Balance [decimal (16,5)]
---------------- -------------------------------------------------- ------------------
18.00 001000000000000 -37.98
19.00 002000000000000 -17.66

I need the query analyzer to format them as they are in the tables.
Help please.

View 11 Replies View Related

Formating Date Results

Jun 5, 2007

I tried looking for assistance but I cant get my formatting to work. I was pretty sure yyyy=2007 and yy=07 but its not working right.

What I am doing is using Business Intelligence to read the previous days IIS logs. The logs are labeled as ex070602.log (exyymmdd.log) The code I use to get the previous day is dateadd("d",-1,getdate())

now I want to format the yy/mm/dd but I am getting single digits for the month and day and 4 digits for the year. Can someone tell me the expression I would need to use for this. Thank you kindly guys.

Some things I tried and results I got:


datepart("yy",dateadd("dd",-1,getdate())) = 2007
year(dateadd("dd",-1,getdate())) = 2007

day(dateadd("dd",-1,getdate())) = 4
month(dateadd("dd",-1,getdate())) = 6


Thanks. I appreciate the assistance.

View 4 Replies View Related

Formating Of Columns In Output Of A SQL Statement In Query Analyzer

Jan 16, 2004

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

View 7 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Easy SQL Question. How To Display Query Results In Query Analyzer

Feb 12, 2008

When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Sniier */SELECT TOP 1       @SniierName      = Sniiers.SniierName,        @SniierAlias    = Sniiers.SniierAlias,        @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View 3 Replies View Related

Returning Results Based On One Query Into Existing Query

Aug 2, 2012

linking two tables together to get an end result

find below the code i have used

The first part of the query provides me with the info i need

SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp

[Code].....

View 1 Replies View Related

Transact SQL :: Use Query Results As Select Criteria For Another Query

Jul 10, 2015

I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.

While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.

View 6 Replies View Related

DateTime Formating.

Oct 5, 2006

I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;* 8:00 AM Get to work* Independance daysome of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time.. I've tried this...select Event = (CONVERT(CHAR(8),start,8) +' ' + title) from Calendar where CONVERT(CHAR(8),start,8) <> '12:00:00'It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time? Thanks for any help.Mark

View 3 Replies View Related

FORMATING THE SQL OUTPUT

May 17, 2001

Hi!

I have couple of questions. Anybody can help me.

1. How can I format the output in T-SQL coming like as one can do in SQL*PLUS (ORACLE)?

2. I decalerd a Cursor and using that It is generating the output. I jut want to dispaly only 100 rows, its mean that I want to control the output.

Please give your feedback

View 1 Replies View Related

Formating Date

Aug 6, 2002

How do I get the following format using SQL server ( SQL QUERY ??)
yymmddhh.

In Oracle I can do simple...

Select to_char(datecolumn, 'YYMMDDHH') from tablename;

RESULT > 02080603
-- YEAR, MONTH, DATE, HOUR.

What is that I need to do to get the same result on sql server.?

tks

View 1 Replies View Related

Text Formating

Nov 9, 2006

i have this:
\FILInternalBuisnessArea*.*
\FILInternalBuisnessArea
\FILInternalBuisnessArea*.qvw
\FILInternalBuisnessArea empfileDB.mdb
\FILInternalPublic Area*.*
\FILInternalPublic Area
\FILInternalPublic Area*.xls
\FILInternalPublic Areaacro*.doc
\FILInternalPublic Areaeconomi*.xls
\FILInternalPublic AreaeconomiGroup01FileToSave*.xls
\FILInternalPublic AreaeconomiGroup01Unit01FileToSave*.xls

And i want to strip away whatever is after the last '' so the output should look like this
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Areaeconomi
\FILInternalPublic AreaeconomiGroup01
\FILInternalPublic AreaeconomiGroup01unit01

datatype = varchar (500)

thx alot in advance

//Mr

View 7 Replies View Related

Formating The Record

Apr 7, 2008

Hi all,
i am using sql server 2005.
let me explain the scenario.
I have to built a report where I have to find out no of call made by an executive to a perticular customer during a year

my table record look like

empid custid product date and other field
em111 iimran aaaa 01-04-2008 ........
em111 iimran bbbb 01-04-2008 ........
em111 iimran cccc 01-04-2008 ........
em111 xxxxxx aaaa 01-04-2008 ........
em111 xxxxxx 2222 01-04-2008 ........
em111 iimran aaaa 02-04-2008 ........
em111 iimran bbbb 02-04-2008 ........
em111 iimran cccc 02-04-2008 ........
em111 xxxxxx aaaa 02-04-2008 ........
em111 xxxxxx 2222 02-04-2008 ........

.
problem is it has to implement in this manner

Srno custname jan feb mar apr may june july aug sep oct nov dec
1 iimran 0 4 0 5 6 7 0 3 5 7 3 1

please suggest some idea.


Please Help

View 1 Replies View Related

Formating A String In Sql

May 5, 2008

Ok so here is an explanation of what i need. I have a table that has emails in it. I need to get those emails to show up as verticle line of names. these will then be sent out via email so i have the following line of code to put them into a string:

select @emails1 = isnull (@emails1 + '; ','' + char(13))+ char(13) + email from emaillist

I have tried putting Char(13) in multiple areas trying to get line break. but the email sends the list in a straight line.

can anyone help me. also if you need more info let me know i will give it to you.

example of desired output
xxxx@dgfasg.com
sfgasg@asgahi.com
saghna@asogh.com

View 20 Replies View Related

Formating Problem

Dec 18, 2007

hi all
can anybody tell me how to get double underline for the text box value in reporting services
am using sql server reporting services 2000

View 3 Replies View Related

Condtional Formating

Oct 26, 2007



Hi all,

This expression gives me an error anyone know why?


=IIF(Fields!M.Value = 0,"Average",(MonthName(Fields!M.Value)))

Thanks

View 4 Replies View Related

Drill Down Formating

May 22, 2006

Does anyone know what code I'd use in an expression to conditionally format based on if the drill down is expanded or collapsed?

Thanks.

in an
in-an
inane
Inna
Ian
Edit...
Revert to "inan"conditionally
conditional
conditionals
conditionality
Edit...
Revert to "conditionaly"

View 3 Replies View Related

Formating Datetime

Mar 14, 2008

I have a decimal time field that I would like to convert to "hh:mms"

If I try to use =cdate(Fields!Avg_Visit_Duration.Value) I get #Error in my result. The error message is:

Conversion from type 'Double' to type 'Date' is not valid.

0.0044612654 should be shown as 00:06:25. I can do this very easy in SSAS or Excel... SSRS is giving me fits.

edit: made question clearer. nlm

View 5 Replies View Related

Query A Query With Multiple Results

Aug 22, 2005

Hi,New to .Net and SQL.  I have two tables that I have joined together.  RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name.  I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts.  I have a details report that gives each contract info. for each specific adjusters rentals.  However, I want to just list the adjuster once and give all of their totals.  In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT     SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled)                       / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled)                       / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM         dbo.RentalControl_Main INNER JOIN                      dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE     (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND                       (dbo.RentalControl_Main.AgencyID = '2')

View 1 Replies View Related

Date Formating - Military

Apr 13, 2007

Hello all,
I am trying to import an excel spreadsheet that has a specific date column. The column is saved as military time. For example, one of the records is:
 04-12-2007 09:30
 But, when i import the data into sql 2005, the above date is displayed as:
2007-04-12 09:30
 I need it to display like the same way that it's being displayed in the excel spreadsheet.
My field in sql is nvarchar(100) and it still doesn't work.
 
Any ideas?Thanks in advance.

View 3 Replies View Related

Formating The Output Of Mails

Nov 16, 2001

Hello,

I'm using xp_sendmail in the sp and in @query I do select * from table1.
The result also page,s
The formate is not at all readable and even if @attach_results = 'TRUE'
In the text file its not formatted as it sends mail to my manager I want it to be formatted and looks good. Can anyone help me or Is there any way to get the results in readable format.

Thanks in Advance for the help

Rani

View 2 Replies View Related

Date Formating For Summarization

Oct 1, 2003

How do I format a date field to monthly and/or yearly only in order to summarize totals in another field.

View 6 Replies View Related

Formating Numbers, How Do I Format:

Dec 10, 2007

a number like 1,234,456.82 ?

View 13 Replies View Related

Formating Data In A Table

Dec 4, 2006

Hi

I wanted to knoe if it is possible to remove the leading and trailing spaces by using the RTrim and LTrim?

Currently I have the table Below is it possible to format all the data i.e. remove all leading and trailing spaces in the table from every record?


select *

from
(
select 'Rivet' as [Name], '01' as PartNo, '1999' as Year union all
select 'panel', '041', '1999' union all
select ' side panel', '021', '1999' union all
select 'thread ', '042', '1999'

) t

View 5 Replies View Related

Formating Numbers With Commas

Jul 23, 2005

I am pulling several numbers from a SQL table, adding them and doingvarious calculaitons. The numbers do not display a comma to separatethousands. What is a way to format this?

View 5 Replies View Related

Formating Bound Data

Apr 27, 2006

How do I change, or add to, the following code so as to be able to format the display:

this.txtAppointmentDate.DataBindings.Add(new Binding ("Text", dsData.Tables["Results"],"AppointmentDate")

The data is stored in SQL Server as datetime and I want only the date portion to show in the TextBox.

Thanks in advance

View 1 Replies View Related

Formating Numbers In SSIS

Oct 13, 2006

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?

View 5 Replies View Related

Formating A Number On A Cell

Jan 4, 2007

Hi,

I have the following problem, i want the number format to appear depending on the language that the IE is on once you are using the reports on the web.

The thing is that my reports have language = default.

Format of the numbers are N0 or ###,###,###

The result is

123,234,456 with pt language ou en-us language, and i would like to be 123.234.456 in pt and 123,234,456 on en.

What am i doing wrong?

Thank you

View 3 Replies View Related

IIF Formating In Table When Changes Between 2 Rows

Dec 14, 2007

Hi,

i have create a Report withe a table. In this Table i have History-data of a tabel from my database. When a value in a field was change between the actual row and the row before, i want to change the color of the chaged value.

For Example:
Server Customer Serial # Location
DC1 Company 123 123abc RZ9 / AB12
DC1 Company 123 456abc RZ9 / AB12
DC1 Company abc 456abc RZ9 / AB12


Who has an idea to resolve this problem?

Thanks and regards,
Eberhard Droege

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved