Problem Sorting Data Returned From Database

May 31, 2007

 I need help in sorting data return from database. For example, I have the following data:
ID             field1                                                                                                                                                                                                             1              Computer                                                                                                                                                                                                       2               Cell Phone                                                                                                                                                                                                    3                iPod                                                                                                                                                                                                            4                Car                   

I know that a select statement will look something like this: SELECT field1 FROM table ORDER BY field1. However, I want the data to be display this way in the gridview:
ID             field1                                                  
                                                                       
                                                                       
          3              iPod                                        
                                                                       
                                                                       
                     1              Computer                      
                                                                    
                                                                       
                                 2              Cell Phone                  
                                                                      
                                                                       
                                   4              Car   
Any idea of how I can make this happen. Any idea is appreciated. 

View 11 Replies


ADVERTISEMENT

Visual Formatting Of Data Returned From A SAP Database

Apr 3, 2014

I am working with extracting data from a SAP database, and I would like to perform some 'visual formatting' on the returned data. Because the SQL code that I am writing will be used in a custom Report it is not possible for me to use '3rd Party' formatting tools. Furthermore because the database is hosted on an MS SQL 2008 R2 RDBMS it is not possible to use the IIF() function.

Here is a sample of the data that I am getting back at the moment. URL...Here is how I would like the data to be returned (with the exception that the blue lines will ideally be blank cells). URL...

I have come to believe that I should be using the ROW_NUMBER() function, along with the OVER() function and possibly the 'partition by' keywords.Here is my original code -

Code:
select
td.ItemCode as 'Item Code'
, td.Dscription as 'Item Description', td.Quantity as 'Order Qty'
, titm.OnOrder as 'PO Qty'
, th.CardCode as 'BP Code', th.CardName as 'BP Name'
, th.DocNum as 'Sales Order Number'
, tsp.SlpName as 'Sales Person'
, twhs.WhsCode as 'Whs Code'
, isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'

code]...

Notice above that I only see the 'Seq' and no other data. How can I go about modifying this code so that I see all of the data columns I desire? If I further 'refine' the above, by adding case statements and the individual columns that I want to see (as opposed to using the * wildcard) I get a long list of error messages.

View 9 Replies View Related

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3.   string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE  a.RegionCode = b.RegionCode"; 5.   DataSet dsMarketList = new DataSet();6.   SqlConnection sqlConn = new SqlConnection(intranetConnStr);   7.   SqlCommand cmd = new SqlCommand(sql,sqlConn);8.  sqlConn.Open();9.   SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10.   try11.   {12.   adapter.Fill(dsMarketList);
 13.  String bling = adapter.SelectCommand.CommandText;//BRG 14.   dsMarketList.DataSetName="RegionMarket"; 15.  dsMarketList.Tables[0].TableName = "MarketList"; 16.    dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to  17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18.           // from the SQL query 19.  } 20.  catch(Exception e) 21. {  22. // Handle the exception (Code not shown)

View 2 Replies View Related

Cache Only A Dropdown In A Page, With Data Returned By Database

Dec 12, 2006

Dear Friends,
I want your help to know the best way to cache the data of a dropdownbox returned by a SQL Query.(SELECT FIELD FROM DBO.TABLE)
This data returned by database is used in more than one dropdown on multiple pages. I want to cache only the dropdown control, not the entire page. Which is the best way to do it? The data isn't refreshed often, probably once per month.
 Could you tell me the best way? 
THANKS 

View 4 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.



Any suggestions would help.


Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int.  When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end.  The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order.  The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Sorting Data

Apr 17, 2008

Hi,

I have a column containing data

3 KB
16 KB
2 KB
4 KB
43 KB

How to sort this column and display results like:

43
16
4
3
2

Thanks.

View 7 Replies View Related

Sorting Some Data...

Jul 20, 2005

ok..here goes..I have a name field in my table called "bname"all of the data in this field has full names in it, spaces areseparated by a "+"ie. "john+doe"i have two other fields that are currently NULL. "First_name" and"Last_name"now my question is obvious.I need to populate "First_name" with all of the data in "bname" to theleft of the "+"and likewise to the Right of the "+" in "Last_name"i sort of know how i would do this in ASP... but im trying to makethis a DTS package... so i was wondering if this could be done in SQLThanks for any help! :DMario C.

View 4 Replies View Related

Sorting Data Upon Import Into SQL 7

Sep 12, 2001

Hello all,
The problem goes like this. I am trying to upsize all my data to a
SQL server and get it out of the old Access 97 database. One of our tables
involves testing reports which tell if an object has passed or failed and
its main key is it's ID autonum. When I look at the table in Access, I see
that the first record in the table has a date of 1/01/97 with an id of 1 and continues through today. When I bring those records into SQL 7, the first record contains a date of 9/01/99 and continues out of order down the table. I need the data in the SQL server to begin with the 1997 record like it is in Access and not with the 1999 record. I cannot have the records appearing in the table as 20, 3, 4, 5, 1, 2 but I need it as 1, 2, 3, 4, 5, 6, because
the id must be in sync correctly. How can I sort the data in SQL 7 ?

View 1 Replies View Related

Sorting Data, ADO, SQL Server Etc...

Feb 21, 2008

Ok, it is a given that sorting operations are sometimes expensive in SQL Server using the ORDER BY clause because the relational model is not really well suited to sort data blah blah blah.

So the canned DBA response is "this is a presentation issue, handle it in the presentation layer".

OK, great. Problem solved. So I am digging around in the ADO.Net object model trying to give a junior developer some guidance and i am trying to figure out what is the best way in ADO.Net to order what we used to call a recordset where the sorting is not done at the SQL Server, but in IIS.

I imagine there is some overhead in this operation as well. Is it really going to save me some overall performance or am I just shifting the burden and I will not likely see any appreciable difference?

View 8 Replies View Related

Sorting Out Data In A Field

Apr 19, 2004

Hi there,

I got one prob and i need help to solve it.
I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each.
Example:

ASP_Tin No
----------------

ss24fg
ss98jk
tp98ij
yh88ij
yh67tr
tp34ed
fg98bv
fg56sl
..........

and i need to sort it out into like this

ss tp yh fg
------------- ------------- ---------------- ---------------
ss24fg tp98ij yh88ij fg98bv
ss98jk tp34ed yh67tr fg56sl

i need to do it in sql statement which is in stored procedure
hope somebody can help me
Thanx

View 2 Replies View Related

Sorting Data Having Months

Sep 21, 2006

Dear all,



I have a field [Month] in my table AA.having Data like ( January,February,March,April etc.)

I need to sort them by starting of month

lets say as

January

February

March

How i can i do this

Have no index and can't define a index on this field

Thanks for ur time and Help



Regards

Mohd Sufian

View 12 Replies View Related

Sorting The Data In Ascending Order .

Aug 16, 2002

Hi ,

Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .

Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .

Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?

Many Thanks .

Anita.

View 1 Replies View Related

Sorting Based On Various Data Types

Jul 23, 2005

I've been tasked with designing a database to store collectiveagreements. I need to store each sub-article of each article of eachagreement in it's own row within a table. I'm having difficultyfiguring out the best way to store each sub-article's identifier sothey sort properly.The majority of the collective agreements use numbers in a dottednotation, for example:1.11.21.3.11.3.21.3.31.4Some agreements mix letters with numbers, such as:A-1.11.1.A.1Any ideas how I should deal with this?

View 4 Replies View Related

Sorting Three Different Data Through One Column With 2 Values Only.

Jan 21, 2008



can anyone please help on a sql statement that would sort out three kinds of data. let's say i have a column that contains a status flag. there are only 2 possible values, add and delete. now, i want to sort out three kinds of data: add_only, delete_only, and update. Update is the data that contains duplicate records in the table. Which means, there are two rows having same "student_number" but two different status flags. This happens when there's an update or change in data, such as new address or new phone number. The old row/data is sent with status flag of delete, while the updated data is sent with status flag of add. Example below. An output table with a new column of tag is created.












reports table

STUDENT_NO
NAME
ADDRESS
PHONE
STATUS FLAG


1
McKenzie
Ohio
9111111
add


2
Hario
New Jersey
1234567
add


3
Oda
Japan
9876543
delete


4
Davis
Chicago
1112222
add


5
Rodriguez
Detroit
4445555
add


1
McKenzie
Ohio
7778888
delete


4
Davis
Detroit
1112222
delete









create, remove, update table

STUDENT_NO
NAME
ADDRESS
PHONE
STATUS FLAG
TAG

2
Hario
New Jersey
1234567
add
create

5
Rodriguez
Detroit
4445555
add
create

3
Oda
Japan
9876543
delete
remove

1
McKenzie
Ohio
9111111
add
update

4
Davis
Chicago
1112222
add
update

Anyone knows what SQL statements to use to have this output? Thanks.


View 6 Replies View Related

Problem With Sorting The Column Data On Table

Apr 9, 2008

Hi All,

I want to sort the column data which is of positive and negative number like the following.

-5823
-1
200
100

i want to sort like
-1
100
200
-5823

How can i do that can any one help me..

Thanks in advance.

-john

View 10 Replies View Related

SELECT And Then I'm Clueless. Ordering And Sorting Data

Nov 13, 2007



I want to select the below info from a sql 2005 db, to use it in a dynamic datadriven menu. Only problem, I don't know how to formulate my select statement. So here's the data

ID, TEXT HREF DESCR PARENTID





1
hMenu1
link1.htm
desc text
0


2
hMenu2
link2.html
desc text
0


3
sMenuM1
link3.htm
desc text
1


4
sMenuTSMenu3
link4.htm
desc text
3


I want the data structured so that below the row with id 1, would come it's subitems, subitems for the subitems and so on. So the recordset I'd like to return would in this case be:hMenu1
sMenuM1
sMenuTSMenu3
hMenu2
I've indented the data just to make things clearer. And finally, I want the substructure to be infinite at least in theory.I also do not want to use recursive logic to do this, if possible. I can change the structure of my table if neeed.Cheers!/Eskil

View 7 Replies View Related

Sorting The Report Data After Exporting To Excel

May 11, 2007



We have a requirement where in we need to sort the data after exporting to excel .But when we are trying to apply the filter on the excel and trying to sort we are getting an error ..



Is there any workaround for this??

View 1 Replies View Related

Sorting Columns In Grid Data Viewer

Aug 28, 2007

Has anyone ever looked at the way the grid data viewer sorts it data when
clicking on a column header? If you click on a column header, something
happens to the sorting of the data in the viewer, but it's not always clear
to me _what_ is happening. It appears the data is sorted ascending on the
column you clicked on. If you click again the sort order seems to inverse to
descending. However, if you look closely, it turns out that the data is not
always sorted correctly, especially when you click on an integer valued
column.

I found this when doing a demo on the AdventureWorks demo extracting data
from the SalesOrderDetail table. If you sort on the OrderQty column, data is
correctly sorted in ascending quantities. However, if you click the column
again, orders with an order quantity of 2 are displayed on top (while there
are orders with a much higher order quantity) and if you scroll down the
list, you notice that there is no clear sorting anymore. The same happens
with other columns.

Is this supposed to work as I would expect it to do or is there a logical
explanation for the behaviour I see?

--
Best regards,

Hans Geurtsen
Docent Kenniscentrum

Info Support
De Smalle Zijde 39
3903 LM Veenendaal
The Netherlands
www.infosupport.nl

View 1 Replies View Related

Reducing Like Returned Data

Jul 20, 2005

I am working on a personal project and am drawing a complete blank(too much celebrating last night?) on the SQL term that is used toeliminate multiples of like data when it is returned from thedatabase.ie, instead of ....redblueredgreenit would return ...redbluegreenSorry for the trouble and thanks.

View 2 Replies View Related

Forcing A Value If No Data Is Returned?

Jan 6, 2008

In the following procedure i write the results to a temp table called #temp1I now want to count the results of #temp1, if the count of #temp1 = 0
I want to insert 'No Records Found' into #temp.ERRORMSG else return what is in the table

any idea on how to do this?


ALTER PROC [dbo].[SPU_RPT_Savings_AnomalyDispatches] 40,'04/01/07|06/30/07'
@PropertyID varchar(4000),
@DropDown varchar(50)

AS
SELECT Client.CLIENT, Client.CLIENTID, ErrorEmailLog.ID, ErrorEmailLog.SITEID, ErrorEmailLog.PROPID, ErrorEmailLog.DISTINCTERRORS,
ErrorEmailLog.ERRORMSG, ErrorEmailLog.ERRORDATETIME, ErrorEmailLog.EMAILRECIPIENTS, Property.PROPERTY, Property.STREET,
Property.CITY, Property.STATE, Property.ZIP, Property.PHONE
INTO #TEMP1
FROM ErrorEmailLog INNER JOIN
Property ON ErrorEmailLog.PROPID = Property.PROPID INNER JOIN
Client ON Property.CLIENTID = Client.CLIENTID
WHERE (ErrorEmailLog.ERRORDATETIME BETWEEN SUBSTRING(CONVERT(VARCHAR(12), @DropDown), 0, 9)

View 3 Replies View Related

International Data Not Being Returned Correctly ???

Jan 24, 2004

HI all.

I have a web service that does an SQL select against a database that contains international data, however when this is displayed from the web service the text such as "Rue Emile Féron 168" is not shown correctly and the 'é' is shown as a comma. Can someone advise what changes I need to make to the coding. Also our own tables have varchar fields and I'm assuming the "é" data will be saved correctly ???

Thanks

View 4 Replies View Related

How To Show SUBREPORT When No Data Is Returned.

Mar 20, 2006

I have a subreport within the details section of my main report. This is enclosed by a single group. When I preview the subreport alone, it diplays the subreport correctly with no data (meaning it still displays the header and footer without a detail section). When the subreport is run within the main report and no subreport data is returned the subreport does not show.

Based on other threads I understand that the default functionality is to display headers and footers when no data is returned. This appears accurate except on a subreport.

View 4 Replies View Related

Preview Has Data But No Columns Returned

Oct 4, 2006

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

View 6 Replies View Related

Can We Hide A Column If No Data Is Returned?

Feb 7, 2008

Hi All,

Is there a way to hide a column if there is no data for that column? Suppose that I have a SP (lets call it wrapper) which will call multiple SP's. But for any one run, there'll be only one SP which is called. But the columns returned by these SP's is obviously different. So, in order to accomodate the output, can I create a table, add all the possible columns to it, and set the properties of the individual columns such that they will be hidden if there is no data in them? Example, SP 1 returns - ServerName, Location, OS ; SP 2 returns ServerName and location only. Now if the wrapper SP calls only the second SP (SP 2) can I configure in such a way that the third column (OS) is not displayed at all? (In normal cases, it would appear but without any data in it)...

Hope the question is clear... Thanks a lot..


Manoj.

View 8 Replies View Related

How Can I Group Data Returned By A Datareader By A Particular Field

Apr 23, 2007

How can i group the data returned by statement below by "Dept" field. I have already tried to group it like shown below but that doesn't seem to work for me. Any help.
cmd_select = New SqlCommand("SELECT Incident_id,Dept From Report_Incident Group By Dept,Incident_id", myconnection_string)

View 3 Replies View Related

Formatting Returned Data To Multiple Columns?

Oct 9, 2014

I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.

First is that possible? Second if so how?

My data looks like this:

University X
University X - 1
University X - 2
University X - 3
College X
College X - 1
College X - 2
College X - 3
Cmty College X
Cmty College X - 1
Cmty College X - 2
etc.

How can I get it to return only:

University X
College X
Cmty College X

View 20 Replies View Related

XML Data Task XPath No Results Returned

Oct 22, 2007

Hi,

I'm trying to use the XML Task XPath operation for the first time but having some problems. I'm using the following settings:

Source type: File connection
SaveOperationResult: True (file destination)
SecondOperandType: Direct Input
PutResultInOneNode: False
XPathOperation: Values

The XML file I'm testing has the following stucture:

<?xml version="1.0" standalone="yes"?>
<BackupDataSet xmlns="http://tempuri.org/BackupDataSet.xsd">
<Device>
<DeviceID>6356452a-e7a6-42e1-895a-d4ade62210d5</DeviceID>
<UserID>1533c44f-c263-db11-9db3-000e9bd9f98d</UserID>
</Device>
</BackupDataSet>

When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:

//Device/DeviceID
/BackupDataSet/Device/DeviceID
//DeviceID

All of these return no results. What am I doing wrong?

Regards,

Greg



View 3 Replies View Related

Inserting Data Returned From A Sproc Into A Table

Apr 12, 2006



i am writing a sproc that calls another sproc. this 2nd sproc returns 3 or 4 rows. i want to be able to insert these rows into a table. this sproc is inside a cursor, as i have to call it many times.



how do i insert the rows that it returns into another table??

View 10 Replies View Related

The Data Provider Or Other Service Returned An E_FAIL Status!?!?! WT-???

Oct 24, 2006

I'm running sql server ver 7.0 SP4.I have an access project (.adp) that runs a view which is nothing morethan a select statement. Access locks up solid when I try to run thisquery - with NO error messages what-so-ever.If I sign onto the server, and run the view from enterprise manager - Iget:"the data provider or other service returned an E_FAIL status" !?!?!There is NO ERROR number, or further explanation of the message!!Now - this query DID work last week - all of a sudden I get this error- I've:1) repaired / compacted this database2) rebooted the server3) spent several hours searching websites & groups for an explanationa) I don't care how useless some people think enterprise manager is -at least it gave me SOME kind of error; where access just locks up!b) There are no issues with null values, or problem date fieldsanywhere in these tables.4) tried MANY variations on the query, and am now more confused thanever.5) I've RUN THIS QUERY IN AN ACCESS MDB WITH LINKS TO THE SQL SERVER -AND IT WORKS!?!?!?!?The query I'm using follows; if I remove ONE field from the selectstatement - this query RUNS!!!It doesn't matter what field I remove! It's as if, I can only have somany fields in the select statement!?!?!?This is clearly ridiculous since the query ran fine last week with thesame number of fields, not thatthat should have anything to do with this, as sql shouldn't care howmany fields I request.I'm not actually requesting any fields in the last table - but if Iremove this table it works!? If I leave this table, and select fieldsfrom it, but remove another table - again it works!So my questions:1) What [T-] is going on here?2) Why would an mdb be able to run a query that an adp can not!?!?3) When will microsoft ever release a product that provides usefulerror messages?I really don't expect an answer to #3, because I know the answer isNEVER - microsoft doesn't want to make solving problems easy.Here's the query I'm using:SELECT codes.proj_id, answers.report_date,answers.release_date, answers.notes,answers.answer_date, answers.answer_person,answers.answer, answers.answer_status,answers.answer_person2, codes.cust_idFROM dbo.codes LEFT OUTER JOINdbo.demos ONdbo.codes.code = dbo.demos.code LEFT OUTER JOINdbo.answers ON dbo.codes.code = dbo.answers.codeany help appreciated - TIABob

View 2 Replies View Related

Transact SQL :: Summarizing Data Returned By Join Query

Nov 20, 2015

I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM.  

I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D').  

When I run the query below it returns 4 million rows.  How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?

SELECT edw_ar_fl.ARTBASE.MA_NUM,
       edw_ar_fl.ARTBASE.MA_PAID_AMT
FROM   edw_ar_fl.ARTBASE
       JOIN edw_sbx_bt.jjenkins_OVERAGE2
         ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM
WHERE  edw_ar_fl.ARTBASE.CONTRACT_NUM = '593'
       AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = '

View 4 Replies View Related

Determining If A SqlCeDataReader Has Returned Data From A SELECT Statement

Sep 26, 2006

Hi all,

is there no a way to determine if a SqlCeDataReader has managed to return data (rows) from an executed SQL SELECT statement? I ask this because I have the following problem where I first need to determine if data (rows) are returned, if so, cycle through them and get the data out. But if I do the test to determine if data is returned (like I have in my code below) then the Reader.Read() is classed as reading a row, so when I do my 'while (Reader.Read())' this will then only work with data in the 2nd row.
if (Reader.Read()){ while (Reader.Read()) { //Get data from rows }}

I recall having this issue before as there doesn't seem to be a standard way of testing for data returned from a SELECT statement. I hope you can shed some light on this for me.

Thanks

View 8 Replies View Related

Hide Header And Footer When No Data Returned On Report

Aug 23, 2007

Any suggestions on how to hide the header and footer information if there isn't any data returned on the report?

View 7 Replies View Related







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