combining two tables with a full-text search

I had a table that was terribly in need of normalisation that I have now
split into two tables. The table contains three "similar" fields that I
were previously indexed using a full-text query. I now need to do the query
twice as subqueries, UNION the results, then order by the calculated rank.
Problem is - the rank appears to be independent between the two tables, to
the results are coming up with one query always being higher than the other.
Is there any way to pre-select or cap the rank value, or another way to
search these two tables so they're more "combined"?
Thanks in advance,
Duncan
Probably not as the rank is generated on a per table basis.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <dunc@.ntpcl.f9.co.uk> wrote in message
news:Ouh0hapaFHA.2496@.TK2MSFTNGP14.phx.gbl...
> I had a table that was terribly in need of normalisation that I have now
> split into two tables. The table contains three "similar" fields that I
> were previously indexed using a full-text query. I now need to do the
query
> twice as subqueries, UNION the results, then order by the calculated rank.
> Problem is - the rank appears to be independent between the two tables, to
> the results are coming up with one query always being higher than the
other.
> Is there any way to pre-select or cap the rank value, or another way to
> search these two tables so they're more "combined"?
> Thanks in advance,
> Duncan
>
|||One solution, that wouldn't be the best but would work, would be to
create a third table containing all the fields you wish to index with
the primary key associated with it, then index that table instead.

Combining two tables from different databases

I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.

To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.

To better illustrate:

database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58

The overlapping cusno's are not the same customer.

How can I get all the cusno from the arcus file in database two to the arcus file in database one?

Is this even possible?not without creating all new PK values.

if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)

in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.

the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.

DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.

combining two tables

hi,

does anyone have any good insight to this problem? I will have two tables which contains the same number of columns for same data types, they are related together by a key book_id. I need to combine them together and create some extra totalling data in the new datatable for a report. Here is an example

table1:
book_id new_words cost_of_change
1 3000 2
1 4000 4
2 500 4

table2
book_id old_words cost_of_change
1 1500 1
3 2500 5

I need to combine them into a table like this:

book_id new_words cost_of_change old_words cost_of_change total_cost
1 7000 6 1500 1 7
2 500 4 0 0 4
3 2500 5 0 0 5

whats the best way to do this?

I have been trying to use full outer joins to do this but I find this a difficult way to create new rows in the new combined table, like what will be an easy way for me to say in SQL that only one row should be used for book_id 1, as it's is present in the two source tables 3 times? I think i will be able to find out from using left and right inner joins, before i make the new combined table but this seems like a very ineligant way of doing this, as it seems to require lots of temp tables.

thxIs table1 the only place where there can be duplicate book IDs? I'm going to assume so, but if table2 can have duplicates you'll need to modify this a bit. But the basic idea should work.

You can use an aggregate subquery for table1 that you then join on table2. The subquery looks something like this (all of this is untested code; you may need to tweak):

SELECT SUM(new_words), SUM(cost_of_change) FROM table1 GROUP BY book_id

That sums the two fields for each id and eliminates the dupes. That subquery becomes one of the derived tables in the outer select. Something like this:

SELECT B.book_id, A.new_words, A.new_cost, B.old_words, B.cost_of_change AS old_cost, total_cost FROM table2 AS B
INNER JOIN (SELECT SUM(new_words) AS new_words, SUM(cost_of_change) AS new_cost
FROM table1 GROUP BY book_id) AS A
ON A.book_id = B.book_id

This query doesn't yet aggregate the totals from the two tables, so that will be another outer query, but the idea is the same. And there are almost certainly ways to simplify this query.

One way is to use table variables in SS2K. Then you can do three more straightforward joins.

Is this helpful? Or have I confused things more?

Don|||Something like this should work:

Select
IsNull(A.book_id,B.book_id) as book_id,
IsNull(A.new_words,0.0) as New_Words,
IsNull(A.Cost_of_change,0.0) ACost_of_Change,
IsNull(B.old_words,0.0) as Old_Words,
IsNull(B.Cost_of_change,0.0) BCost_of_Change,
IsNull(A.Cost_of_change,0.0)+IsNull(B.Cost_of_change,0.0) as Cost_of_change
From
(Select book_id, Sum(new_words) New_Words,Sum(Cost_of_change) Cost_of_change FROM Table1 Group By book_id) A
FULL OUTER JOIN
(Select book_id, Sum(old_words) Old_Words,Sum(Cost_of_change) Cost_of_change FROM Table2 Group By book_id) B
ON A.book_id=B.book_id

|||Thanks Guys, that solved my problem. The second method is lot more readable, but which would be the most efficient method?|||Both methods are basically the same thing. The second method could be made clearer by using Table variables as mentioned in the first method. But I don't think that would affect efficiency. You could test this using the Sql Query Analyzer and compare the execution plans and execution times for each.

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure?
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.com
Aditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables
sqlsql

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatena
tion, or expose a
computed column in the table. That way the data doesn't get out of sync in c
ase someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How wil
l
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null space
s
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concate
nation, or expose a
> computed column in the table. That way the data doesn't get out of sync in
case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL i
n either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to
go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:
> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How
> > will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:
> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>> Assuming no nulls in firstname or lastname column:
>> UPDATE tbl
>> SET FULLNAME = FirstName + ' ' + LastName
>> But, why do you need to store this? You can create a view with the concatenation, or expose a
>> computed column in the table. That way the data doesn't get out of sync in case someone modifies
>> the
>> first name or last name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
>> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
>> > LASTNAME, ADDRESS, TELEPHONE,...
>> > Now i have added a new column in the table, naming it FULLNAME. In this i
>> > want to insert the FIRSTNAME and the LASTNAME
>> > combined with a space in between, the rest all remaining the same. How will
>> > i do it?Is it possible with a Stored Procedure'
>> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
>> >
>> > You can also email me at : aditya595@.yahoo.com
>>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining text data rows

I am working with a database derived from text documents.One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column.I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result).How do I combine the rows with the same DocumentID into a single row of text data?This will put the entire text content of each document in its own row.

TEXT001 table as it is

DocumentID

SectionNo

SectionText

1

1

Paragraph 1 of Document 1

1

2

Paragraph 2 of Document 1

1

3

Paragraph 3 of Document 1

2

1

Paragraph 1 of Document 2

2

2

Paragraph 2 of Document 2

New TEXT table

DocumentID

SectionText

1

Entire text of Document 1

2

Entire text of Document 2

I realize that I can use “union” to combine tables with the same data type, but that is not what I am trying to do.Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command.If anyone can tell how to do this, I would appreciate your help.

More modestly, I tried to use the “Group By” clause to combine the SectionText data using this query:

SELECT DocumentID, SectionText FROM TEXT001

GROUP BY DocumentID

And got this error message:

Msg 8120, Level 16, State 1, Line 5

Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be “summed”, so I tried including it in the GROUP BY clause:

SELECT DocumentID, SectionText FROM TEXT001

GROUP BY DocumentID, SectionText

And got his error message:

Msg 306, Level 16, State 2, Line 5

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?

Hi moonshadow, the following will create a stored procedure that will fullfill the requested task. run the sript, it will have only one constraint, is that i supposed that the maximum section length is hundreds of characters i.e: it will be great if you can use nvarchar instead of ntext, but if more characters are needed to be stored then comment and we will work around it.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'myProc' AND type = 'P')
DROP PROCEDURE myProc
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewText' AND type = 'U')
DROP table NewText
CREATE TABLE NewText
( DocumentID int,
Paragraph1 ntext,
)
GO
CREATE PROCEDURE myProc
AS
declare @.a int
declare @.b nvarchar(3000)
declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a
update NewText set Paragraph1 = cast(Paragraph1 as nvarchar) + @.b where DocumentID = @.a
else
insert into NewText(DocumentID,Paragraph1) values (@.a,@.b)
set @.c=@.a
FETCH NEXT FROM myCursor into @.a,@.b
END
CLOSE myCursor
DEALLOCATE myCursor
select * from NewText
go
--ToCall your procedure:
execute myProc

|||

Hi Mario. Thanks for script. This is exactly what I am looking for.

I created a new query, pasted in your script, and clicked Execute.

A new table ("NewText") was created with the appropriate columns.

However the data from "Text001" was not copied to "NewText". When I open the "NewText" table, the content of the rows is "null".

As a Newbie to SQL Server, I am wondering if I should be doing something else to call the "myProc" procedure. Do I need to do another step to combine the rows from "Text001" and copy the combined data to "NewText"?

|||

yes moonshadow,

first it is great that you copied the script and started its excecution.

i am sure that it will work, but look what you will have to do:

EITHER: change the data type of SectionText from ntext to nvarchar, and then test. if you are urged to use ntext, then we can figure it out... but as a first step, just for your test, do not use ntext or at least do not use large text in your records under SectionText.

OR change the datatype of DocumentID to int.

look, the Table Text001 is like the following (i created this table upon your specifications):

CREATE TABLE [Text001] (
[DocumentID] [int] NULL ,
[SectionText] [ntext] COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Thanks for your patience Mario. Here is what I tried:

1. I tried to change the data type of SectionText from ntext to nvarchar but it would only allow nvarchar(50) or nvarchar(max). I chose nvarchar(max) since the SectionText contents are likely be much more than 50 characters. NewText table was created but still empty.

2. I changed the DocumentID to int with the same result as before.

What next?

|||

Mario:To simplify and make it more concrete for working with your query, I created a new database called “Practice”

In that database I ran this query based on your example to create a table called “Text001”:

CREATE TABLE [Text001] (

[DocumentID] [int] NULL ,

[SectionNo] [int] NULL ,

[SectionText] [ntext]

COLLATE SQL_1xCompat_CP850_CI_AS NULL )

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I put data into five rows of the table Text001 as follows:

DocumentID

SectionNo

SectionText

1

1

Blue

1

2

Red

1

3

Green

2

1

White

2

2

Black

I ran the initial query that you provided which was “executed successfully”

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'myProc' AND type = 'P')
DROP PROCEDURE myProc
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewText' AND type = 'U')
DROP table NewText
CREATE TABLE NewText
( DocumentID int,
Paragraph1 ntext,
)
GO
CREATE PROCEDURE myProc
AS
declare @.a int
declare @.b nvarchar(3000)
declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a
update NewText set Paragraph1 = cast(Paragraph1 as nvarchar) + @.b where DocumentID = @.a
else
insert into NewText(DocumentID,Paragraph1) values (@.a,@.b)
set @.c=@.a
FETCH NEXT FROM myCursor into @.a,@.b
END
CLOSE myCursor
DEALLOCATE myCursor
select * from NewText
go

The NewText table that was created looked like this:

DocumentID

Paragraph1

Null

Null

I think the NewText Table should have looked like this:

DocumentID

Paragraph1

1

Blue

Red

Green

2

White

Black

I am learning alot from working with this and am thankful for your help.

|||

moonshadow!can you try this please:

add the following, IN RED,

...

declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b

IF @.@.FETCH_STATUS <> 0
PRINT " ERROR!"

WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a

...

test it, cos it seems it is not entering the loop, if there was no error, try to update the following line:

if @.c = @.a
update NewText set Paragraph1 = Paragraph1 + @.b where DocumentID = @.a
else...

also, replace all field datatypes in tables Text001, and NewText to nvarchar (i.e: do not use ntext)

...CREATE TABLE NewText
( DocumentID int,
Paragraph1 nvarchar(3000), or max
)...

|||

Mario:

1. I changed all "ntext" datatypes in tables Text001, and NewText to "nvarchar(max)"

2. I copied and pasted this

IF @.@.FETCH_STATUS <> 0
PRINT " ERROR!"

as you suggested and got this error message:

Msg 128, Level 15, State 1, Procedure myProc, Line 14 The name "ERROR!" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

3. I also tried to update the following line as you suggested:

if @.c = @.a
update NewText set Paragraph1 = Paragraph1 + @.b where DocumentID = @.a
else...

but got the same error message.

|||ok moon shadow, instead of "ERROR!" just put anything, an insert statement, or a Print 1, just to check if the fetching is occuring with/without errors, just further troubleshooting|||

Mario: I tried numerous things without luck until I typed in a constant expression without the quotations:

IF @.@.FETCH_STATUS <> 0

PRINT 10

The command completed successfully but the content of the "NewText" table was still Null.

I figured 10 (or any number) is a constant expression as per the error message and should be appropriate but may not have served your purpose to see if the fetching is occurring. Any other thoughts on how to proceed?

|||

try to insert a record instead of PRINT, like the following:

IF @.@.FETCH_STATUS <> 0

insert into NewText(DocumentID,Paragraph1) values (1,'Error')

and check the NewText table

good luck

|||

Mario:

I used your new script and the "query executed successfully" but the content of the NewText table was still "null"

|||

moonshadow! run the following:

execute myProc

and then check the result in NewTable

|||

Mario: Yes!! The NewText table is now filled with the combined data. I told you I was a Newbie.

Perhaps you can help me with one refinement. The data in the NewNext table SectionText column is run together (ie., "blueredgreen") which will not work too well with the paragraphs in my original database. Is there a way to automatically format the new data fields (in NewText) so that each row of the original table (Text001) remains on its own line with a space between paragraphs when it is read in an application? Such as this:

blue

red

green

Thanks for your patience and knowledge in getting this far.

|||

Great MoonShadow!!!

now you've got the concept, you can expand it as you like...

regarding your concern, you can add a carriage return while filling each paragraph, i.e: add what is in red to the sql script:

...

update NewText set Paragraph1 = Paragraph1 + char(13)+char(10) + @.b where DocumentID = @.a
...

good luck

Combining Multiple rows into 1 row x 1 column

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?

Employee Name John Mike Dale

There are a number of ways to complete what you wish. Some features that you can take advantage of include:

select with CASE and MAX

User defined functions

SELECT with FOR XML syntax (better in SQL 2005 than SQL 2000)

PIVOT

Transact SQL SELECT extensions|||Very Cool, Thanks.

|||

Just for the sake of completeness, this can also be achieved via cursors:

Assuming #Employee temp table contains the data.

declare @.sql varchar(200), @.k int

set @.sql = ''

set @.k = 0

declare @.EmpName varchar(50)

declare abc cursor for select EmployeeName from #Employee

open abc

fetch next from abc into @.EmpName

while @.@.FETCH_STATUS = 0

begin

if @.k > 0 set @.SQL = @.SQL +', '

set @.SQL = @.SQL + @.EmpName

set @.k = @.k +1

fetch next from abc into @.EmpName

end

close abc

deallocate abc

SELECT @.SQL as Employees

Drop Table #Employee

|||

Code Snippet

declare @.Output varchar(max)

select @.Output = isnull(@.Output + ', ' + [Employee Name] , [Employee Name] )

from MyTable

select @.Output as [OneColumn]

combining many contains()

Hi all,
I have a nvarchar(255) column on a tblKeyword table with many queries in a
verified FTS form. i.e-
"Microsoft Corporation"
("Hewelet Packard") OR HP
Google OR Froogle
Sun
I want to issue a combined FTS query that will gather all (*) information
from the indexed table, based on all existing queries in the tblKeyword
table.
Ie, something like:
SELECT * FROM myftstable
WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
OR (Google OR Froogle) OR (Sun)])
Is it possible to do it dynamiclly in some sort? other then connecting all
strings and sending them all to the contains?
Thanks!
Guy,
Yes, there is a way to do this via a stored proc:
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- Using your example:
EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard" or
"HP") or ("Google" or "Froogle") or ("Sun"))'
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a nvarchar(255) column on a tblKeyword table with many queries in a
> verified FTS form. i.e-
> "Microsoft Corporation"
> ("Hewelet Packard") OR HP
> Google OR Froogle
> Sun
> I want to issue a combined FTS query that will gather all (*) information
> from the indexed table, based on all existing queries in the tblKeyword
> table.
> Ie, something like:
> SELECT * FROM myftstable
> WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
> OR (Google OR Froogle) OR (Sun)])
> Is it possible to do it dynamiclly in some sort? other then connecting all
> strings and sending them all to the contains?
> Thanks!
>
|||Hi John,
I meant how to do it programatically, so that the queries exists in
tblKeyword will be populated automatically as a long (@.vcSearchText varchar)
Guy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
> Guy,
> Yes, there is a way to do this via a stored proc:
> use pubs
> go
> -- DROP PROCEDURE usp_FTSearchPubsInfo
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- returns 2 rows
> EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
> go
> -- Using your example:
> EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard"
or[vbcol=seagreen]
> "HP") or ("Google" or "Froogle") or ("Sun"))'
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
a[vbcol=seagreen]
information[vbcol=seagreen]
HP)[vbcol=seagreen]
all
>
|||Guy,
I'm not sure what you're asking for here... Could you provide some examples?
Are you looking for a client-side (IE-based) solution or a server-side
(T-SQL based) solution? If the former, you may want to checkout KB article
246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> I meant how to do it programatically, so that the queries exists in
> tblKeyword will be populated automatically as a long (@.vcSearchText
varchar)[vbcol=seagreen]
> Guy
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
Packard"[vbcol=seagreen]
> or
in[vbcol=seagreen]
> a
> information
tblKeyword
> HP)
> all
>
|||John hi,
I need a server-side solution (T-SQL based) for connecting all of the
records appear in tblKeyword into 1 long string. Is it possible?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Guy,
> I'm not sure what you're asking for here... Could you provide some
examples?
> Are you looking for a client-side (IE-based) solution or a server-side
> (T-SQL based) solution? If the former, you may want to checkout KB article
> 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries"
at:[vbcol=seagreen]
> http://support.microsoft.com//defaul...b;EN-US;246800
> Regards,
> John
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> varchar)
> Packard"
queries[vbcol=seagreen]
> in
> tblKeyword
OR[vbcol=seagreen]
connecting
>
|||Hi Guy,
Ok. I looked back over your original posting and I didn't realize that you
had two tables - tblKeyword and myftstable - and that in affect you wanted
to "pass" the tblKeyword table values to CONTAINS search_condition clause.
I'm sure there are other ways of doing this, but for now, I've developed two
cursor based solutions - assuming I'm understanding your question properly:
The below examples use the database (pubs) and the FT-enabled table
(authors) and the table (keyword) is your tblKeyword table:
use pubs
go
create table keyword(kword varchar(50))
go
insert into keyword values ('white')
insert into keyword values ('("john" or "paul")')
insert into keyword values ('Yokomoto')
go
select * from keyword
go
-- Simple Cursor Fetch with a CONTAINS statement...
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
OPEN keyword_cursor
DECLARE @.keyword varchar(50)
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE (@.@.fetch_status <> -1)
BEGIN
select * from authors where contains(*,@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
GO
-- Complex Cursor Fetch into a temp table and then select from it...
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
go
-- Clean-up
drop table keyword
go
Let me know if this is what you're looking for.
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> John hi,
> I need a server-side solution (T-SQL based) for connecting all of the
> records appear in tblKeyword into 1 long string. Is it possible?
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> examples?
article[vbcol=seagreen]
> at:
varchar(7800))[vbcol=seagreen]
> queries
Packard")
> OR
> connecting
>
|||Exactly!!
Thank you John!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uwMwhCnKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi Guy,
> Ok. I looked back over your original posting and I didn't realize that you
> had two tables - tblKeyword and myftstable - and that in affect you wanted
> to "pass" the tblKeyword table values to CONTAINS search_condition clause.
> I'm sure there are other ways of doing this, but for now, I've developed
two
> cursor based solutions - assuming I'm understanding your question
properly:[vbcol=seagreen]
> The below examples use the database (pubs) and the FT-enabled table
> (authors) and the table (keyword) is your tblKeyword table:
> use pubs
> go
> create table keyword(kword varchar(50))
> go
> insert into keyword values ('white')
> insert into keyword values ('("john" or "paul")')
> insert into keyword values ('Yokomoto')
> go
> select * from keyword
> go
> -- Simple Cursor Fetch with a CONTAINS statement...
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> select * from authors where contains(*,@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> GO
> -- Complex Cursor Fetch into a temp table and then select from it...
> set nocount on
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> CREATE TABLE #authors_PK (author_pk char(11))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sql = 'insert into #authors_PK (author_pk) select au_id from
> authors where contains(*, ''' + @.keyword + ''')'
> exec(@.sql)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> select * from #authors_PK
> drop table #authors_PK
> go
> -- Clean-up
> drop table keyword
> go
> Let me know if this is what you're looking for.
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...
> article
Queries"
> varchar(7800))
> Packard")
>

combine two columns

have a basic Q.
I have a table which contains two columns
froz_month and froz_year (yes the date has been split by the app into these
two)
I need to be able to "combine" these two back into one
like mmyyyy or yyyymm
I do not know what the proper sql statment is
I tried select froz_month + froz_year AS totdate
clearly that add's it together rather then giving me a combination
can anyone please clue me in on this
thanks
billBill
Lookup CONVERT () system function in the BOL
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:70F915F2-7119-4F9C-BD1D-5E4FA9ED58B7@.microsoft.com...
> have a basic Q.
> I have a table which contains two columns
> froz_month and froz_year (yes the date has been split by the app into
> these
> two)
> I need to be able to "combine" these two back into one
> like mmyyyy or yyyymm
> I do not know what the proper sql statment is
> I tried select froz_month + froz_year AS totdate
> clearly that add's it together rather then giving me a combination
> can anyone please clue me in on this
> thanks
> bill
>|||try...
select convert(varchar,froz_month ) + convert(varchar,froz_year) as totdate
from TABLE
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:70F915F2-7119-4F9C-BD1D-5E4FA9ED58B7@.microsoft.com...
> have a basic Q.
> I have a table which contains two columns
> froz_month and froz_year (yes the date has been split by the app into
> these
> two)
> I need to be able to "combine" these two back into one
> like mmyyyy or yyyymm
> I do not know what the proper sql statment is
> I tried select froz_month + froz_year AS totdate
> clearly that add's it together rather then giving me a combination
> can anyone please clue me in on this
> thanks
> bill
>sqlsql

combine data from different records with same ID

I have a table contains comments. User scan create as many comments they wa
nt.
my job is to combine and rearrange all comments in order of dates and time.
acct date time Comments
-- -- -- ---
08 01/04/2001 170852 0Conveyed stips.
84 01/04/2001 173740 test!
84 01/04/2001 173812 test2!
02 01/04/2001 180502 spoke to mbr and nd
01 01/05/2001 115548 joint life
01 01/05/2001 115550 Please fund loan.
18 01/05/2001 185220 Sent
18 01/05/2001 185238 Sent completed application
Desired Result:
acct Comments
----
--
08 Conveyed stips. 01/04/2001: 170852
84 test! - Ford 01/04/2001: 173740 test2! 01/04/2001: 173812
02 spoke to mbr and nd 01/04/2001: 180502
01 joint life 01/05/2001: 115548 Please fund loan. 01/05/2001: 1155
50
18 Sent 01/05/2001: 185220 Sent completed application 01/05/2001:
185238
Thanks in Advance,
CulamUse a document management system (textbase)and not SQL system.|||You haven't stated what datatypes these columns are.
Do type conversions as required and use the concatenation operator ( + ) to
achieve the results you want. What seems to be the difficulty in doing so?
Anith|||I converted all the data to VARCHAR and using a operator (+) to combine data
,
but I need to roll up all records with same id into one record. That is
what I need help in.
"Anith Sen" wrote:

> You haven't stated what datatypes these columns are.
> Do type conversions as required and use the concatenation operator ( + ) t
o
> achieve the results you want. What seems to be the difficulty in doing so?
> --
> Anith
>
>|||I see. This does not seem to be a right job for SQL Server. One good
approach to such problems is to retrieve the resultset and leverage the
string concatenation and loop-like functionality of a client programming
language to create the result.
The approaches in SQL are all more or less complex and cumbersome. Some of
the such hacks can be found at:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.
On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via droptable.com"
<forum@.nospam.droptable.com> wrote:
Something like:

>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh
|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.
|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via droptable.com"
<forum@.droptable.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via droptable.com"
<forum@.nospam.droptable.com> wrote:
Something like:

>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via droptable.com"
<forum@.droptable.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales),1)),
1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via SQLMonster.com"
<forum@.nospam.SQLMonster.com> wrote:
Something like:
>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via SQLMonster.com"
<forum@.SQLMonster.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!