View Multiple Records In A Single Row?

Jan 3, 2012

Id account num acc_type
42 1376200071278 gl
42 1308111111111 ic
42 1291111111111 os
34 1245200000000 gl
34 1132485111111 ic

this is table structure.there are multiple records like this in a table . I need output as

id gl accountnum ic accountnum osaccountnum
42 1376200071278 1308111111111 1291111111111
34 1245200000000 1132485111111 -

View 7 Replies

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Calling A Single UDF Multiple Times Within 1 View

Sep 27, 2005

Lets say I have a User Defined Function. I need to call the UDF a number of times within the same View. However, the results are not as expected. The only way (I know of) is to have seperate instances on the same function saved under different names. Is there a way to call a single UDF multiple times within a View without the returned values being messed-up?

View 3 Replies View Related

Combine Multiple Records Into Single Row

Mar 29, 2007

This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.

View 13 Replies View Related

Update Multiple Records From A Single ASP.NET Webpage

Mar 3, 2004

Got a beginner question here...
Let's say I have a database table that houses server information with four columns: make, model, serial #, ip address. And assume there are ten rows with that information filled out. How could I display all the rows of information on a single webpage (ASP.NET), with all the fields being editable; and a single save button that would send any changes to the database (in reality I guess it would be sending all rows and fields to the database, and just overwrite the previous data).

Could a page such as that be created using FrontPage 2003 or Dreamweaver MX 2004?

This would be strictly for updating information. I would have a separate form for adding a new entry.

Thanks for your help.

View 1 Replies View Related

Updating Multiple Records In A Single Table?

Sep 3, 2014

I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.

(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249

(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'

It says query executes successfully but returned no rows.

View 3 Replies View Related

SP Return Multiple Records For A Single Record

May 3, 2007

I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4

that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

View 8 Replies View Related

Update Multiple Records In Single Transaction

Feb 26, 2008

Hi All,

I want to know that how we will be able to update multiple rows in single transaction.

e.g If original database is
S_No Data
1 -
2 -
3 -
4 -

After
S_No Data
1 1
2 3
3 6
4 10

View 4 Replies View Related

Show Multiple Order Records In Single Row

Mar 13, 2006

I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.

I have data in both table like

CompanyTable
ID Name
1 name1
2 name2

OrderTable

OrderId CompanyTabID
1 1
2 1
3 1
4 1

In my query I want to show all orders in single row.

ID Name Orders
1 name1 1,2,3,4
2 name2 null

Is anybody can help on it.

Thanks
Arvind

View 5 Replies View Related

Update Multiple Varbinary Records With Single Sql Statement

Jan 16, 2007

I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...

What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)

'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)

Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)

'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

View 1 Replies View Related

Return Single Records By Joining Multiple Tables

Jun 4, 2008

I would like to know if it's possible to return a single record by joining the tables below. [Persons]
PersonID [int] | PageViewed [int]
=============== =================
1 10
2 5
3 2
4 12

[PersonNames] - PersonID JOINS Persons.PersonID
PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int]
=============== ============== ======================= ===================
1 1 Samantha Brown 5
1 2 Samantha Green 10
2 3 Richard T 10
3 4 Riko T 0
4 5 Sammie H 0

[AltNames] - backup for searches caused by common spelling mistakes
AltNameID [int] | AltNames [nvarchar]
================ =============================
1 Sam, Samantha, Sammie, Sammy
2 Riko, Rico

[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID]
NameID [int] | AltNameID [int]
============= ================
1 1
4 1
3 2
This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch
========= ============ ================= =================
1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy
2 5 Richard T Richard T
3 2 Riko T Riko T, Riko, Rico
4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy

[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].

The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field.
Any help would be greatly appreciated.
Thanks, Pete.

View 4 Replies View Related

Insert Multiple Records Into A Single Field On Another Table

Feb 15, 2012

I have a table JOBCODE which contains a list of codes.

I want to insert these values into table VIEWS as a list separated by spaces.

E.G.

Table Jobcodes looks like this

code
1
2
3
4
5
6

And I want table Views to look like this:

field1
1 2 3 4 5 6

How do I go about this?

View 4 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records From A Single Variable?

Apr 24, 2014

I have two tables. Table 1 has column "job", table 2 has column "job" and column "item". In table table 2 there are multiple "items" for each "job"

I would like to insert all of the "items" into table 1, based on a join table1.job = table2.job

View 7 Replies View Related

SQL Server 2012 :: Multiple Records Queried Into Single Record?

Jan 27, 2014

I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.

People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801

[code]....

Results

PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom

View 8 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

Data Access :: How To Create View To Get Records From Multiple Tables

Sep 1, 2015

I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table

UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.

When I query view where UserID=1 it should return record as

FName =John,
LName = Abraham and
Industry= Film

How to write query?

View 2 Replies View Related

Transact SQL :: Avoid Same Table Multiple Times Rather Than Put Records In Single Table And Use It Throughout

Nov 19, 2015

There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule

SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]

[code]....

Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times

View 11 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

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

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

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

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Using Union And Join In A Single View

Aug 8, 2006

I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.

View 7 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID key1 key2 key3 key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

SQL View To Split Rows In Single Table...

May 24, 2007

I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for

I have a table is MSSQL 2000 that looks as follows:

Code:

id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1

Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:

Code:

custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1

Any help would be great! I imagine some sort of join command can be constructed using a single table?

View 2 Replies View Related

Creating A Single View From 2 Existing Views

Aug 9, 2007

I have 2 views which contain the following fields:
EVENT,
WEEK,
SUBSCRIPTION,
QTY,
GROSS_AMOUNT,
SEASON

The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:

EVENT,
WEEK,
SUBSCRIPTION,
Q6 (qty of season 1),
A6 (gross_amount of season 1),
Q7 (qty of season 2),
A7 (gross_amount of season 2)

Below is my select command:

------

SELECT TOP 100 PERCENT
dbo.vw_SEASON06.EVENT,
SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6,
SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

FROM dbo.vw_SEASON06 FULL OUTER JOIN
dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND
dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND
dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT

GROUP BY
dbo.vw_SEASON06.EVENT,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

ORDER BY
dbo.vw_SEASON06.EVENT

-----

This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.

How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?

Any help is appreciated!

View 7 Replies View Related

Reporting Services :: Single Value Filter In Power View

Jul 15, 2015

I have a power view report with some filters. I want some of the Filters to be just Single Value Select Filters like dropdowns instead of Multi-Select ,as multiple selected values can show garbage data. Also it is very inconvenient for the end user to keep unchecking/clearing the previously selected value and then select the appropriate value. Is there a workaround or is it a limitation that has yet to be addressed???

View 4 Replies View Related

Can A View Be Used To Modify More Than One Underlying Table With A Single Statement In MS SQL?

Mar 20, 2008

What's the answer for this and how ? If it is yes then How ?

View 4 Replies View Related

Single Record View In SQL Server Management Studio (2005)

Apr 17, 2008

Hello,
In SQL Server Management Studio (2005), 'Open table' command or a SELECT query displays table rows in a grid (or text).Please tell how to view a single row at a time i.e. all only ONE row is displayed at a time (evenly arranged to cover the screen).
This feature (Single record view) is available in other database client like TOAD.
Thank You

View 5 Replies View Related

Selecting Multiple Records By Multiple Filters...

Aug 13, 2006

Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew

View 2 Replies View Related

Selecting Multiple Records From Multiple Tables

Nov 4, 2004

i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.

ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.

View 1 Replies View Related

Delete Multiple Records From Multiple Tables

Jan 20, 2006

What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

View 1 Replies View Related