Ordering Values In A Cell

Jul 21, 2004

Data
10 13 15 3 26
10 13 20 35 29
10 13 5 26 2
10 15 27 34 33

Query
Declare @num1 char(5)
Set @num1 = (SELECT ltrim(Substring(ltrim(number2), 8, CHARINDEX(' ', ltrim(number2)))) FROM Num2played)
SELECT @num1 FROM Num2played

Error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestions or fix?

Thank you.
=========================================
The data above is in one colum. I would like to rearange the
numbers in order. e.g. from "10 13 15 3 26" to "3 10 13 15 26".

View 4 Replies


ADVERTISEMENT

Ordering Of The Data Values

Jul 28, 2004

Hi frnds,
i need a help from all of u.

i will tell whats it ------ i have a webpage creating using ASP.NET,C# using SQL server 2000.

i have a table in the database which has attribute named "Priority" which has values like "High" "Low" "Medium". all i wana to make is that i enter any of this value tru a dropdown list into the database and i have used Datalist in Webpage so that i will retrieve back the info from the database.
when any request is made to retrieve back this value . i want to see all the priority field in the datalist which has priority as "High" in bold and in order. is this possible ,if it is plz let me know

thank u in advance

sunilk

View 2 Replies View Related

Multiple Values Per Table Cell?

Mar 2, 2004

I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value.
Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>

View 1 Replies View Related

For XML: Create Elements Using Cell Values

Aug 5, 2005

Hi All,If have a sql table with 2 columns and 2 rows with values[["col1row1","col2row1"],["col1row2","col2row2"]].Using t-SQL with "for xml"How can i create a xml where the cell values (not column names) appearas elements?eg:<col1row1>col2row1</col1row1><col1row2>col2row2</col1row2>Thanks,slyi

View 12 Replies View Related

Multiple Values Into Single Cell

May 18, 2007

Taking the Northwind database as an example,I have an order table: 'Orders'a order details table: 'Order Details'and a products table: 'Products'For reasons best ignored, I want to produce a query which has columns:Orders.OrderID, Productswhich has results like:10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella diGiovanni'10249, 'Tofu, Manjimup Dried Apples'________so for those who don't really know what I'm on about and haven't gotaccess to northwind, I want the second cell to contain the returnedcolumn of a second query (but in text format)could anyone suggest a way this could be done? am I entering the landof cursors?Many thanks,Neil

View 4 Replies View Related

Find Values From String In A Single Cell

Jan 10, 2014

I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:

[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59

As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?

View 3 Replies View Related

Checking For Null Values In A Table/Matrix Cell

Jun 12, 2007

What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?

View 3 Replies View Related

How To Add Two Cell Values In Table In Reporting Services(on 27th)

Mar 27, 2008

Hi all
I have two cells,having first cell 0 and other cell 5.
How can i add these two cell values.
I used following expression

=Fields!LITMIN.Value + Fields!NUMMIN.Value
But i am getting Concatenated result ie:05
But i need 5.
How can i do it.Pls help me

View 4 Replies View Related

Power Pivot :: Selected Report Filter Values Display In A Cell

Oct 28, 2015

I have a power pivot with 2 multi valued report filters   student_branch & blood_group. These report filters are used to fetch the data set that contain below result set

student_branch               blood_group           count

Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.

View 2 Replies View Related

Copying An Ntext Cell From One Cell Into Another (destination Row Already Exists)

May 6, 2004

Hi!

What I'd like to do is:

UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @precomputed_id_1)
WHERE table1_ID = @precomputed_id_2

Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.

I'd like to do something like (PSEUDOcode):

WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@textptr_initialised_to_point_at_target_c ell))

But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...

Any hints or pointers HUGELY appreciated... THANX

View 1 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

Matrix - Centering Higher Level Column Group Values In Viewing Area Instead Of Entire Cell Band Width

Dec 28, 2007

I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.

Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?

View 4 Replies View Related

READ EXCEL DATA CELL BY CELL FROM SP

Jul 20, 2005

HI,I HAVE AN EXCEL SHEET WITH SOME DATA, I WANT TO IMPORT THAT DATA (CELLBY CELL WITH MANIPULATION) INTO THE SQL SERVER TABLES BY USING STOREDPROCEDURE(IF POSSIBLE).IF ANYBODY HAVE DONE SIMILER TYPE OF JOB OR KNOWING ABOUT IT, PLS. LETME KNOW.THANKS IN ADV.T.S.NEGI

View 4 Replies View Related

Ordering

Mar 3, 1999

Hi there!

We are experiencing a problem using order clause in the statement below:

SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey

Where person table contains the following columns:

pkey, name etc

and private_person

pkey, fkey_person (indexed), first_name (indexed)

The output is:

STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A

(14 row(s) affected)

STEP 1
The type of query is SETOFF

Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?

If we are ordering by fkey_person everything is Ok. Can anybody help?

Thanks a lot!

Kind Regards,
Vladimir

View 4 Replies View Related

Ordering Ids

Jun 18, 2007

I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.

But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.

So is there anyway to do this method in SQL?

Regards
Shaji

View 5 Replies View Related

Ordering In The Table

Jun 27, 2006

Hello,
 
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)values('aa','a')
insert into costreallocation(RuleId,Type)values('dd','d')
insert into costreallocation(RuleId,Type)values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa      a
cc      d
dd      d
but i need to be filled in the table as they run as:
aa      a
dd      d
cc      d
 
your help is highly appreciated
Best regards
 

View 2 Replies View Related

Ordering Dilemma

Jan 26, 2007

I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields.
thanks for any suggestions you may have.
 Jeff

View 1 Replies View Related

Advanced Ordering

Mar 5, 2001

Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:

[Books]
ID
Title


[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)

[Authors]
ID
Firstname
Lastname
AuthorOrder

As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.

Thanks for any help.

View 1 Replies View Related

Ordering By @parameter Possible?

Mar 20, 2001

Hiya!
I've got a little stored procedure:
CREATE PROCEDURE rICDCode @param1 char(15) = 'Description'
AS
SELECT DiagCode, ICD9ID, Description
FROM ICDCodes
ORDER BY @param1
and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."
I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this?

Thanks,
Sarah

View 2 Replies View Related

Custom Ordering Top 20 ?

Jul 11, 2005

Hi guys.

I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...

I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.

is this plausible ?

Thanks
Tom

View 1 Replies View Related

Ordering By One Field Or Another

May 16, 2008

Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null)

This would be on datetime fields.
Let's say I have 5 records with 2 date fields
sched_dt and arriv_dt.

sched_dt will always have a value, arriv_dt may or may not have a value.

In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt.

*Hope that makes sense*

Thanks

View 1 Replies View Related

Ordering Dates

Apr 7, 2005

I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc).

In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table

I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long.

Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc

Any thoughts ?

View 2 Replies View Related

Re-ordering Records

Jul 5, 2007

Hi all,

Ive got a table with a field called 'morder' which orders a menu based on the values in this field (1 for position 1, 2 for position 2 etc...) for example

1 - menu item 1
2 - menu item 2
3 - menu item 3
4 - menu item 4

If I want to add a record to this table and put it at number 2 in the list, i need to update the table to then read...

1 - menu item 1
2 - NEW menu item 2
3 - menu item 2
4 - menu item 3
5 - menu item 4

I want to use a mssql or php function to re-order this field... is it possible??

Hope you can help, and hope it makes sense...

Ash

View 2 Replies View Related

Specific Ordering

Jul 12, 2004

Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.

View 4 Replies View Related

About Ordering Of Columns

Jan 2, 2007

i add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........

how to do so .............:)

View 6 Replies View Related

Ordering By Date

Jun 7, 2007

Hi,I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.Thanks so much for your help!-Brandon

View 4 Replies View Related

Ordering Days

May 19, 2004

I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..

January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22

here is my sample code

select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'

Thanks, Jeff

View 10 Replies View Related

Ordering By Months

Jun 9, 2004

I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.

select to_char(created,'yyyy-Mon'), matdesc, count(*)
from test
group by to_char(created,'yyyy-Mon'), matdesc
order by to_char(created,'yyyy-Mon') desc

any help would be greatly appreciated

Thank you

Stephen

View 11 Replies View Related

Ordering Results

Jul 25, 2007

I am trying to do a simple SQL query..the result will be sorted alphabetically by default, but i need to specify say, if there is a "Others" it will be placed last in the list. Is there any way to write the query using ORDER BY?

e.g
Australia
China
Others
USA

and i want it to be:
Australia
CHina
USA
OThers

View 1 Replies View Related

Ordering Within Hierarchy

Aug 7, 2007

Hi all,

Ive got a bit of a problem,

I have two tables:

CATEGORY
id
name
parent_CATEGORY_id

RECORD
id
CATEGORY_id
Stock_Held_Number

ps. Ive stripped out any non relevant fields


I also have the following query (again ive stripped out the non-relevant fields)

SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number

At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.

The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.

What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.

For example:

The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2

When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60

It's technically correct because I don't want the parent to calculate the total value of the children

What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get

Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40

Does anybody have any pointers as to how I can achieve this.

Many thanks

Mike

View 3 Replies View Related

Ordering Rows

Feb 18, 2008

hi,

i have following data:

create table tb_customer (customer_id int, order_id varchar (12))
insert into tb_customer (customer_id, order_id) values (123, 2201)
insert into tb_customer (customer_id, order_id) values (123, 2246)
insert into tb_customer (customer_id, order_id) values (123, 2236)
insert into tb_customer (customer_id, order_id) values (123, 1253)
insert into tb_customer (customer_id, order_id) values (120, 1201)
insert into tb_customer (customer_id, order_id) values (127, 2251)
insert into tb_customer (customer_id, order_id) values (127, 2231)
insert into tb_customer (customer_id, order_id) values (122, 2121)

and i want to get the following output:

output:
Nu_order | customer_id| order_id
1 |123| 2201
2 |123| 2246
3 |123| 2236
4 |123| 1253
1 |120| 1201
1 |127| 2251
2 |127| 2231
1 |122| 2121

any idea?

thank you

View 11 Replies View Related

Ordering, Grouping - HELP!

Jul 23, 2005

Having recently had excellent service here (many thanks, Erland!),here's another wee problem.SQL 2000I need to get a report which will display the movement of people thus:ID DIRECTION NAME DATE VEHICLE REGISTRATIONThe value of DIRECTION can be either "Inbound" or "Outbound"I need to have the data grouped by ID, with the "Outbound" row first.For example1 "Outbound" Smith 10/02/2005 ABC1231 "Inbound" Smith 11/02/2005 ABC1235 "Outbound" Jones 14/02/2005 XYZ7895 "Inbound" Jones 15/02/2005 DEF456This is the SQL so far.SELECTfldPersonID,tblMovementType.fldType AS [MovementType],'Outbound' AS [Direction],fldMovementDate AS [Date],CASE WHEN tblPerson.fldForenames IS NULLTHEN fldSurnameELSE fldSurname + ', ' + fldForenames END AS [Name],'Outbound ' + fldVehicleOut AS VehicleRegistrationFROMtblPersonINNER JOIN tblMovementTypeON tblMovementType.fldMovementTypeID = tblPerson.fldMovementTypeWHERE((fldMovementDate BETWEEN @FromDate AND @ToDate) AND(fldStatus = 1))UNION ALLSELECTfldPersonID,tblMovementType.fldType AS [MovementType],'Inbound' AS [Direction],fldMovementDate AS [Date],CASE WHEN tblPerson.fldForenames IS NULLTHEN fldSurnameELSE fldSurname + ', ' + fldForenames END AS [Name],'Inbound '+ fldVehicleRtn AS VehicleRegistrationFROMtblPersonINNER JOIN tblMovementTypeON tblMovementType.fldMovementTypeID = tblPerson.fldMovementTypeWHERE((fldMovementDate BETWEEN @FromDate AND @ToDate) AND(fldStatus = 1))What I need is the way to Order or Group it so that I can just put therows into a report without any grouping on the report itself (CrystalReports aarrggh!) - if this is possible!Edward--The reading group's reading group:http://www.bookgroup.org.ukThanks

View 1 Replies View Related

Ordering Problem

Jul 23, 2005

Hi everyone, I have a database (not designed by me) in sql server, I amhaving trouble ordering it correctly, and I would appreciate any help.Here is my structure, it's sort of hierarchical:TABLE_AID_COLSUB_ID_COLTABLE_BID_COL'NAMEThis could be thought of as an employee and supervisor structure. Wehave employees (TABLE_B) and their relationship (TABLE_A).I know I need to use a group and oder by, but I am drawing blankstoday. I need the result to be (if using the employee example):Bob-Ryan-SueMarkNick-George-Paul--Ringo--StarHere is my half-hearted stab at it:SELECT *FROM TABLE_A AINNER JOIN TABLE_B BGROUP BY A.SUB_ID_COLORDER BY NAME;Thank you very much for any help you can offer.Have a great night!Ryan

View 4 Replies View Related







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