Multiple Results In One Column MS SQL 2000
Jun 11, 2008
Hi all,
I'm totally new and been reading the forums to help me and I think I've done ok so far getting the results I need from basic SQL selects and stuff but now I seem to have hit a wall and I realize I need help from someone who really knows their SQL.
I'm using Microsoft SQL 2000.
I have a database with columns
Product_ID
Product_Name
Product_Association
The Product_ID column is the unique ID key. Product_Name is the name of the product. And Product_Assocation represents search terms.
The column Product_Assocation has for example the values
nature*leaves*leaf*tree*trees*abstract*green
for one product and
food*dinner*table*eat*eating*restaurant*cuisine*plate*dinner*knife
for another.
I need to do 2 things basically.
1. How do I select the product based on ProductID and display each word independently in a recordset?
For example how can I Select from Table where Associate = 'each word and list them' for Product_ID = '1'
2. I have to allow the user to "search" for a word in the column so I need to create an SQL statement that goes someting like
Select * from Table where searchresult = 'search word for example tree which is a word seperated by one of the * in the Product_Association column' and that the recordset returns the table row.
I am sure these both probably sound like silly and simple questions and if they are I am sorry guys. I am trying my best. I am a newbie and no point pretending otherwise. Hopefully someone with greater SQL knowledge out there can assist me because I have tried all I know but can't seem to make any headway.
I am using ASP not .net or php if that makes any difference? I'm not sure that it does as I think it is about the SQL Select statement and how to construct them?
Thanks for any help. Its really appreciated.
Kind regards, Sam
View 8 Replies
ADVERTISEMENT
Nov 8, 2006
I want to display the top 5 count of areacodes for each name
I
want to combine all the results for the areacodes into one column for
each user. like a csv. I tried my code below but the results just
return the same 5 of areacodes for all names on each area code row with
each callername. like
joe blow 123,456,755,312,465,567,555
bill jones 123,456,755,312,465,567,555
I just want the top 5 for each particular name.
I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing!
Thanks
DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacodes int)
declare @MyAreaCodes varchar(50)
INSERT @Stage
(
CallerName,
AreaCode,
Calls,
theDate
--myAreacodes
) --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name
SELECT CallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC
-- Get Calls
SELECT s.CallerName,
s.AreaCode,
s.Calls,
s.theDate--,myareacodes
FROM @Stage s
INNER JOIN (
SELECT CallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY CallerName
HAVING (CallerName = 'name1') OR
(CallerName = 'name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BY callername,Calls desc
--
set @MyAreaCodes =''
--
SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCode,'') + ',' from @Stage
--
SELECT CallerNAme,@MyAreaCodes AS MyAreaCodes from @stage
Group By CallerName
View 1 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Oct 29, 2001
I ran this query against the pubs database and it runs successfully
ALTER TABLE publishers ALTER COLUMN state CHAR(25)
I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)
and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.
My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.
View 1 Replies
View Related
Feb 28, 2008
Hi everyone, I have a DTS package on my SQL Server database that returns several different results. Does anybody know how I would access any one particular part of the return. I am trying to make it so that there is only one run to the database for the information, but am not sure how to get just the parts I want out of the DTS package. for Example, here is my DTS Package:
Select @InvoiceNo as InvoiceNo, '02/01/08' as PaymentDate, 1000.00 as PaymentAmt, 500.00 as RemainingAmt, 'X31235X' as ReceiptNumber
Declare @Results Table ( InvoiceNo char(10), Amt float)
Insert Into @Results Values('SNJ0100001',100.00)Insert Into @Results Values('SNJ0100002',200.00)Insert Into @Results Values('SNJ0100003',300.00)
Select * from @Results
How would I ask for just the results from the @Results section and then somewhere else on my page I will want to return just the results from the @InvoiceNo section?? I would normally call the package from my code-behind and I know how to do that, just not to pull a particular section. Here is what I have so far on that section:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="hsp_rpt_AR_ReceiptStatus" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="TextBox1" Name="InvoiceNo" PropertyName="Text" Type="String" /></SelectParameters></asp:SqlDataSource>
Thanks for any helpRandy
View 1 Replies
View Related
Mar 6, 2006
Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:
create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)
As you can tell, the 'custom_address' column is going to house the results of the combination.
I am trying to INSERT (combine) using this statement:
INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id
I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.
Then I do my update:
UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id
However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.
If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?
As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.
View 5 Replies
View Related
Jul 19, 2013
I have following situation -
DECLARE @table1 TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
enterDate Datetime,
entercd char(10)
)
INSERT @Student VALUES
(4648, ‘20130515’, ‘892’,’30’),
(4648, ‘20130103’, ‘764,’10’)
SELECT STATEMENT will give me following -
Stuid schoolcd entercd enterDate
46488923005/15/2013
46487641001/03/2013
Desired output is one row for two or more records for same stuid
464889230 05/15/20137641001/03/2013xxxxxxx/xx/xxxx
How can I accomplish that?
View 5 Replies
View Related
Jul 23, 2005
Hi all,I need to put multiple results in one field, but not sure how. Here'ssome sample code:select a.name,a.accountnum,a.ordernum,(select itemid from items where items_ordernum = a.ordernum) asItems[color=blue]>From clients a[/color]There's multiple instances of 'itemid' in 'items' with the criteria Igave, so this generates an error. I want to instead have the itemnumbers listed as just a list seperated by commas, so the results mightbe this:Name AccountNum OrderNum ItemsJohn Smith 12345 0000234 1233, 1333, 4322Mike Jones 43223 0000023 4322, 543And so forth. Is there someway to loop through this sub-query and makethe results become a list?Thanks --rlangly
View 2 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Feb 25, 2015
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
View 7 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Dec 10, 2003
Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.
I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).
Thanks!
View 5 Replies
View Related
Feb 10, 2004
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
View 3 Replies
View Related
Apr 19, 2004
Hello,
I have a table which has the following structure:
ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time
How to build a query or store procedure to return result like this:
ID MessageText
001 Hello There Working
003 See you Next Time
Your help/advice is greatly appreciated.
Thanks, Ficisa
View 14 Replies
View Related
Oct 23, 2013
I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.
My query now looks like this (i have remove most columns)
SELECT STATUS, RunOn, sort, T_ID
FROM dbo.Results
WHERE (RunOn IN
(SELECT MAX(RunOn)
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)
ORDER BY sort
I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... The date time looks ok, i just can't work out why the latest and one older result are returned !
The original query was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.
View 10 Replies
View Related
Apr 17, 2008
Hi,
I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format:
Customer Name Address
----------------------------------------------------------
ABC Add1
ABC Add2
XYZ Add1
XYZ Add2
Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report).
Please give me a solution to this.
Shyam
View 4 Replies
View Related
Nov 14, 2006
Table users:
userid, name, added...
Table groups
groupid, groupname...
Table groupadmins:
userid, groupid
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
View 4 Replies
View Related
Jan 29, 2008
Hi,
Here is my sample table creation and insertion script.
I want represent as a summary-result using just one record.
How can I ?
Please note the below red color text.
create table policy
(
id int not null,
name nvarchar(50) not null,
constraint pk_policy primary key(id)
);
create table localhost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_localhost primary key(policy_id,id)
);
create table remotehost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_remotehost primary key(policy_id,id)
);
create table rate
(
policy_id int not null,
inbound int not null,
outbound int not null,
constraint pk_rate primary key(policy_id)
);
-------------
insert into policy values(0,N'policy0');
insert into localhost values(0,0,0xC0A80101,0xC0A80101,24);
insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);
insert into remotehost values(0,0,0xACA80101,0xACA80101,0);
insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0);
insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);
insert into rate values(0,1000,2000);
-------------
select * from policy;
select * from localhost;
select * from remotehost;
select * from rate;
-- result of policy table
0 policy0
-- result of localhost table
0 0 0xC0A80101 0xC0A80101 24
0 1 0xC0A80A01 0xC0A80B01 0
-- result of remotehost table
0 0 0xACA80101 0xACA80101 0
0 2 0xACA80C01 0xACA80C01 24
-- result of rate table
0 1000 2000
------------------------------------------------------
desired result set
id name localhost remotehost rate
-- ---------- ---------------------------------------------------------------- ------------------------------------------- -----------------
0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000
descriptin of desired result set :
0. key value is id column and is referenced by each policy_id column.
1. id and name column is the same as policy table's.
2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix.
3. ip_begin and ip_end should be converted dotted presention from numeric format.
4. if prefix greater than 0, it should be displayed using '/'.
5. if ip_begin and ip_end are equal, show just one ip.
6. if the two ips are different from each other, they separated by '~'.
7. rate field is packed divided by ','.
View 4 Replies
View Related
May 17, 2007
If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?
Example stored procedure:
ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]
As
SELECT MASTER_DSC.APP
, MASTER_DSC.BRANCH
, qlkpOfficer.strName
, MASTER_DSC.DSC_OFFICER_49
, qlkpBranchName.strDescrS
, MASTER_DSC.DSC_CUR_BAL_21
, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL
, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL
, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL
, MASTER_DSC.DSC_INT_RATE_45
, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD
, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count
, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count
, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count
, qlkpApplicationCode.strDescrS AS strApplCode
FROM
MASTER_DSC
INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference
LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID
LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference
WHERE
MASTER_DSC.DSC_CUR_BAL_21<>0
ORDER BY
MASTER_DSC.BRANCH;
SELECT dbo.fBankName() AS BankName;
View 7 Replies
View Related
Nov 9, 2015
I have a table similar to below:
itemID | part
1 | A
1 | B
2 | A
2 | A
2 | A
3 | C
I need the table to look like the following:
itemID | part1 | part2 | part 3
1 | A | B | null
2 | A | A | A
3 | C | null | null
There will _never_ be more than three parts to an item, and it does not matter what order they are in.
I cannot get pivot to work for me.
View 2 Replies
View Related
Apr 24, 2006
I'm writing a FTS query which needs to search on two different columns.
E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.
I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?
Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?
Shailesh Patel...
View 3 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Apr 25, 2006
I would like to make inner join with results from different exec %procedureName% calls.
thanks.
View 3 Replies
View Related
Sep 7, 2007
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...
But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.
In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...
/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/
SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);
... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).
So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?
Any help is greatly appreciated
View 5 Replies
View Related
Jun 2, 2015
If it possible to have an if statement match multiple results, as to not have to use the OR multiple times.
Example: I want to say, if Description equals red or blue or green or yellow or orange or black or white or pink, without having to use OR and OR and OR. Description can match 10 different values.
View 9 Replies
View Related
Dec 20, 2006
Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages.
for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on...
is there any way to achieve it using an SQL query?
the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway.
any suggestions?
Thanks.
View 5 Replies
View Related
Apr 20, 2008
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
can anyone give any code snippets?
View 1 Replies
View Related
Oct 7, 2004
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC
for
select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor
fetch next from myCursor
into @clientName
--loop now:
while (@@fetch_status=0)
begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))
begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =
'this will send
ClientName:'+ @clientName
end --end else
-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName
fetch next from myCursor
into @clientName
end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor
GO
View 1 Replies
View Related
Mar 3, 2014
I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
View 9 Replies
View Related
Jul 17, 2013
I have a table of attributes set up as follows:
ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
View 5 Replies
View Related
Jan 8, 2007
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
View 2 Replies
View Related
Mar 20, 2008
My team is using TFS for work item management and I'd like to provide a week schedule matrix that displays:
- weekdays on the x-axis
- persons on the y-axis
- and the assigned workitems in the cell.
This works fine if there's only one WIT (work item) per day but some WIT's are shorter and thus the resource is assigned more than one a day. My dataset may return something like this:
Date Person WIT
28/3 Mark Task1
28/3 Mark Task2
28/3 Hank Task3
29/3 Mark Task2
29/3 Hank Task3
In this case I'd like the matrix to look something like this, i.e. the two tasks Task1 and Task2 in the same cell:
Mark Hank
28/3 Task1 Task3
Task2
29/3 Task2 Task3
I've set up the matrix in the report but I only get the first item per day to show.
Mark Hank
28/3 Task1 Task3
29/3 Task2 Task3
The default definition in the cell was "=First(Field!TaskName.Value)", so I figured I'd solved the issue by changing it to "=Field!TaskName.Value", but it didn't.
...any idéas??
View 2 Replies
View Related
Jan 4, 2008
Hi everyone,I am interested in showing a ranking of each result that I get. Basically, the results will be the time of a race along with their name. However, what I want to do is also have a third column that will show their place. I would like my result to be like this:Place Name Time 1 Bob 1:00.002 Bill 1:00.012 Rob 1:00.014 Jill 1:00.025 Jon 1:00.035 Joe 1:00.037 Jane 1:00.04 Records are constantly being added to the table, so the place must be calculated dynamically. Now, I do realize that SQL 2005 has the wonderful RANK() OVER (ORDER BY [time]) AS [Place] feature, but unfortunately SQL 2000 does not have that mechanism. What is the best way to display these results? Should they be included calculated on the SQL side or using the datagridview control? Any help would be greatly appreciated. Thanks,Jason
View 2 Replies
View Related
