Showing posts with label tables. Show all posts

Thursday, March 29, 2012

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 Via T-SQL

Hello,

I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.

Thank you for your help!

UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:

Code Snippet

USE Northwind
GO

SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees

CompanyName
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Bill Smith
...

|||

Donnie:

The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:

The number of columns the same The datatypes of corresponding columns be the same|||

Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".

declare @.t1 table(c1 int, c2 int)

declare @.t2 table(c3 int, c4 int)

insertinto @.t1 values(1, 2)

insertinto @.t2 values(3, 4)

select c1, c2 from @.t1

union all

select c3, c4 from @.t2

AMB

|||

Are you certain that it is a UNION that you need to perform, and not a JOIN?

A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1a Column2a Column3a Column1b Column2b Column3b

1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1

2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2

3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3

SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b

A UNION will allow you to horizontally merge the data from both tables, like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1 Column2 Column3

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3

FROM Table1 t1

UNION ALL

SELECT t2.Column1b,
t2.Column2b,
t2.Column3b

FROM Table2 t2

Chris|||

Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?

Thanks for your help!

Table 1 - Sample Data Column1a Column2a Column3a 1 T1C2R1 T1C3R1 2 T1C2R2 T1C3R2 3 T1C2R3 T1C3R3 5 T1C2R5 T1C3R5 Table 2 - Sample Data Column1b Column2b Column3b 4 T2C2R4 T2C3R4 2 T2C2R2 T2C3R2 3 T2C2R3 T2C3R3 6 T2C2R6 T2C3R6 Ouptput: Column1a Column2a Column3a Column1b Column2b Column3b 1 T1C2R1 T1C3R1 NULL NULL NULL 2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2 3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3 NULL NULL NULL 4 T2C2R4 T2C3R4 5 T1C2R5 T1C3R5 NULL NULL NULL NULL NULL NULL 6 T2C2R6 T2C3R6

|||

Yes, a full join should work for you.

SELECT a.Column1a, a.Column2a, a.Column3a,

b.Column1b, b.Column2b, b.Column3b

FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)

There should not be any duplicates in the result set.

Combining two tables to make a third

Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:

>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns from e
ach table instead of using [*],
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mngong@.gmail.com> wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com
..
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
>
> Your help is appreciated have been on this
>

Combining two tables to make a third

Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||This is a multi-part message in MIME format.
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns =from each table instead of using [*],
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<mngong@.gmail.com> wrote in message =news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> > Your help is appreciated have been on this
>
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Based on this limited =information:
INSERT INTO Table3
SELECT =*
FROM =Table1
=CROSS JOIN Table2
If you must control the field order, =you may need to list the columns from each table instead of using [*],
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...> =Need help combining two tables into a third with corresponding fields> of =both tables> table 1> time 12 mike work> =time 13 john sleep> times 24 George jump> table 2> 23 sam run> There is a table3 which has all the columns of the two =tables but i> cannot seem to come around to combine them> time =12 mike work 23 sam run> time 13 john sleep 23 =sam run> times 24 George jump 23 sam run> > Your help =is appreciated have been on this>

--=_NextPart_000_0C6D_01C6B148.4585B4F0--

Combining two Tables into one TempTable, with a condition.

Hi,

I'm very new to Sql, and need to combine information from two tables into one temp table. All tables have the same structure.

The problem is I want to set a condition that if Field 1 is already in the TempTable, dont include that field from the second table. Along the lines of

Table1 Description = Blue, if Table 2 Description = Blue, dont include this row.

I have been trying to use INSERT..SELECT and UNION. , the only problem I have is that I cannot come up with a working conditional statement to prevent both complete sets of data being written to the TempTable.

I'm using Sql Server 2005 deveoper Edition and VS2005Pro.

Any ideas on how to get around this would be appreciated.

Tailor

Hi Tailor,

I'm not 100% clear on what your problem is. Is it that you wanted to union two tables and insert this result into a temp table only if data for a particular column (pk I presume) doesn't already exist in the temp table, or is it that you want to union the result of two tables but only select (and thus insert) distinct rows?

Can you provide a bit more info?

Cheers

Rob

|||

Hi Rob,

Thanks for your reply, I'll try to make this a little clearer with some code.

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

UNION

SELECT Description,Id,Reference

FROM Table2

WHERE (Id = 'STR001') AND Description != Table1.Description.

Without the AND statement, all data from both tables is inserted in tempTable, however with the And statement, I get the error message.

'The multi-part identifier Table1.Description could not be bound'

If the description from Table2, matches the Description in Table1, I dont want to include the row in the TempTable.

Sorry if i'm not too clear. At 64, trying to learn VS2005, Sql2005, and intergrating excel in my code, and write a reasonable sized application, all in six months is not something I can recommend. There is just so much to learn, and I dont have a good grasp of a lot of the basics.

If you can suggest a workable solution to my problem, it would be much appreciated.

John

|||

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

FROM Table2 t left outer join temptable tt on t.description = tt.description

WHERE (t.Id = 'STR001') AND tt.description is null

Regards

|||

This is a bit of a messy solution

either

1. Insert the data from both tables with a UNION, but place the UNION statement into a subquery and alias the columns to be inserted.

2. Insert data from table1. Then do the same insert from table2 where NOT EXISTS in the temp table (or table1, either will work). This is a common task you will need to learn to do in SQL Server

|||

Hi JMattias and SHughes,

Thank you both, very much, for your replies. It not only solved the problem, I learnt a lot more on the way through.

Your assistance is greatly appreciated.

John

sql sql

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

If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3select A.col1,B.col1
FROM table_A A,table_B B

good luck with school.|||hahaha, been using only joins, didn't remember about that, thanks

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 table into a single table

I have two tables with a 1 to many relationship:

tblCarCompanies
ID Company
1 Mazda
2 Nissan

tblCarModels
ID Company_fk Model
1 1 Miata
2 1 Mazda3
3 2 Sentra
4 2 Pathfinder
5 2 Maxima

What's the best way to query these two table into one result set like:
tblCars
Company Models
Mazda Miata, Mazda3
Nissan Sentra, Pathfinder, Maxima

Maybe something like this:

declare @.carCompany table
( ID integer,
Company varchar(10)
)
insert into @.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'

declare @.carModel table
( ID integer,
Company_fk integer,
Model varchar(12)
)
insert into @.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'

select company,
reverse(substring(reverse(
( select model + ', ' as [text()]
from @.carModel b
where a.id = b.company_Fk
order by model
for xml path('')
)), 3, 200)) as Models
from @.carCompany a

/*
company Models
- --
Mazda Mazda3, Miata
Nissan Maxima, Pathfinder, Sentra
*/

|||One thing I noticed is that if my Models field contains an "&" it will return "&".
I'm guessing it has to do with the for xml path('') conversion. I can simply do a replace(myOutput,'&','&') but I'm not sure if it'll affect any other characters.

What exactly does the for xml path do and is there a way to convert it back without the replace?
|||Yes, I have seen aberations before because of the path(''); you definitely need to look out for it and you might even need to choose a different solution if it becomes a significant problem. Another alternative is to use a function -- preferably an inline function -- in conjunction with the CROSS APPLY operator. Would you like to see an example of such an alternative?|||Sure, an example would be great. Thanks!
|||

I didn't come up with a good way to create an INLINE function for this. Maybe somebody else sees a straight-forward way to do this. I mocked up this test with these tables:

create table dbo.carCompany
( ID integer,
Company varchar(10)
)
go
insert into dbo.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'
go

create table dbo.carModel
( ID integer,
Company_fk integer,
Model varchar(12)
)
go
insert into dbo.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'
go

An example of a scalar function is like this:

alter function dbo.listModels
( @.prm_companyID integer
)
returns varchar(300)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return @.modelList

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

set @.modelList = reverse(substring(reverse(@.modelList), 3, 300))

return @.modelList

end

go

select id,
dbo.listModels (id) as Models
from carCompany

/*
id Models
--
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

An example with a table function and cross apply is like:

alter function dbo.companyModels
( @.prm_companyID integer
)
returns @.companyModels table
( modelList varchar(300)
)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

insert into @.companyModels
select reverse(substring(reverse(@.modelList), 3, 300))

return

end

go

select id,
m.modelList as Models
from carCompany
cross apply dbo.companyModels (id) m

/*
id Models
-- --
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

There are a couple of additional things to note:

It is critical to these functions that you have an index on the MODEL table based on COMPANY_FK; otherwise, you will table scan You might be able to get away with a NOLOCK optimizer hint in these functions; if you are not sure, do NOT add the NOLOCK hint.

Combining two seperate tables into one

Hello!
I don`t know how to do some query. I have two tables which looks like
it:
First table:
MRPC 200504 200505 200506
C01 1 2 3
C02 2 3 4
C03 3 3 2
Second table:
MRPC 200504 200505 2000506
C01 20% 20% 50%
C02 10% 30% 70%
C03 30% 40% 15%
I would like to combine these two tables into one table, which would
look like it:
MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
C01 1 20% 2 20% 3 50%
C02 2 10% 3 30% 4 70%
C03 3 30% 3 40% 2 15%
The number of columns is changeable, because once a w

there is extra
column added. Is it possible to link these two tables and create one
score table? As you can see the second table has the same columns as
first one and don`t have a string "PRC" in the name of column.
Thank you for your help
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***>> Is it possible to link these two tables and create one score table?
Yes it is possible, but unless you are working towards achieving some
performance benefits (for instance, by materializing data) for specific
queries, such an attempt is of little use. You can always derive the
resultset using a simple JOIN -- in many cases a view should be the
solution.
Keeping them separate, on the other hand, allows you to manipulate data in
each table separately without affecting the other.
It is not a show-stopper, just the matter of aliasing the column names.
Anith|||On Fri, 05 Aug 2005 09:05:05 -0700, Marcin Zmyslowski wrote:

>
>Hello!
>I don`t know how to do some query. I have two tables which looks like
>it:
>First table:
>MRPC 200504 200505 200506
>C01 1 2 3
>C02 2 3 4
>C03 3 3 2
>Second table:
>MRPC 200504 200505 2000506
>C01 20% 20% 50%
>C02 10% 30% 70%
>C03 30% 40% 15%
>I would like to combine these two tables into one table, which would
>look like it:
>MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
>C01 1 20% 2 20% 3 50%
>C02 2 10% 3 30% 4 70%
>C03 3 30% 3 40% 2 15%
>
>The number of columns is changeable, because once a w

there is extra
>column added. Is it possible to link these two tables and create one
>score table? As you can see the second table has the same columns as
>first one and don`t have a string "PRC" in the name of column.
Hi Marcin,
Instead of adding columns to your tables for each w

, why not add one
column to the table to hold the w

, then just add rows?
The first table would look like this:
MRPC W

Data
C01 200504 1
C01 200505 2
C01 200506 3
C02 200504 2
C02 200505 3
C02 200506 4
C03 200504 3
C03 200505 3
C03 200506 2
The second table would be similar. Depending on actual business
requirements, it might also be possible to combine these two tables:
MRPC W

Data PRC
C01 200504 1 20%
C01 200505 2 20%
C01 200506 3 50%
C02 200504 2 10%
C02 200505 3 30%
C02 200506 4 70%
C03 200504 3 30%
C03 200505 3 40%
C03 200506 2 15%
(BTW, what datatype do you use for the percentages?)
Tables with a seperate column for each w

/month/year/whatever tend to
bring lots of probles and no gain.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi!
I could have data in rows, but I can only do a crosstab query which let
me create one columns (wk), not two columns at the same time: "wk" and
"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
still don`t know how to combine these two tables into one. Could you
give me a code example' I cannot find it in archieve. I would be very
grateful for help.
Thanx, Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 08 Aug 2005 00:54:37 -0700, Marcin Zmyslowski wrote:

>Hi!
>I could have data in rows, but I can only do a crosstab query which let
>me create one columns (wk), not two columns at the same time: "wk" and
>"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
>still don`t know how to combine these two tables into one. Could you
>give me a code example' I cannot find it in archieve. I would be very
>grateful for help.
>Thanx, Marcin from Poland
Hi Marcin,
Doing a cross tab is actually better handled by the presentation tier.
But if there is no way that the client can handle this and you must do
it server side, use something like this untested code:
SELECT MRPC,
MAX(CASE WHEN W

= '200504' THEN Data END) AS '200504',
MAX(CASE WHEN W

= '200504' THEN PRC END) AS '200504 PRC',
MAX(CASE WHEN W

= '200505' THEN Data END) AS '200505',
MAX(CASE WHEN W

= '200505' THEN PRC END) AS '200505 PRC'
FROM YourTable
GROUP BY MRPC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql sql

Combining two pivot tables and displaying the data

Hi all,
I have the following tables

Tbl_Request

RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
-

Tbl_User

ID SkillCategory Experienced
--
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0

Experience is a bool column.

Required Output:

SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply

.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
-

Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.

Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.

I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.

You can combine using join statement

Sample

Code Snippet

TempTable1 - SkillCategory,Req1,Req2,Req3,TotalDemand

TempTable2 - SkillCategory,Exp,NonExp,TotalSupply

Select T1.SkillCategory,T1.Req1,T1.Req2,T1.Req3,T1.TotalDemand,T2.Exp,T2.NonExp,T2.TotalSupply

from TempTable1 T1

left join TempTable2 T2 on T2.SkillCategory = T1.SkillCategory

|||

Hi Vidhura,

That solution works fine for my web application.But for using that procedure as dataset for the reporting services I face an error. I can't use two temp tables in a procedure.I want to know if this can be achieved without making use of temporary table

|||

Hi,

Using the above SQL Statement just replace the two temptables with select statements, as below

Code Snippet

--create table #Tbl_Request (

--RequestType varchar(20),

--NoOfPositionsRequired int ,

--SkillCategory varchar(20)

--)

--insert into #Tbl_Request values ('Req1', 10,'.Net')

--insert into #Tbl_Request values ('Req2',3,'Java')

--insert into #Tbl_Request values ('Req1',2,'SQL')

--insert into #Tbl_Request values ('Req3',5,'Java')

--create table #Tbl_User (ID int, SkillCategory varchar(20) ,Experienced int)

--insert into #Tbl_User values (101, 'Java', 0)

--insert into #Tbl_User values (102, '.Net', 1)

--insert into #Tbl_User values (103, 'Java', 1)

--insert into #Tbl_User values (104, 'SQL', 1)

--insert into #Tbl_User values (105, '.Net', 0)

--insert into #Tbl_User values (106, 'J2EE', 0)

select

from (

SELECT

SkillCategory,

isnull(Req1,0) Req1,

isnull(Req2,0)Req2,

isnull(Req3,0) Req3,

isnull(Req1,0)+ isnull(Req2,0)+ isnull(Req3,0) as TotalDemand

FROM

(select SkillCategory,NoOfPositionsRequired,RequestType from #Tbl_Request) p

PIVOT

(

SUM (NoOfPositionsRequired)

FOR RequestType IN

( Req1, Req2, Req3 )

) AS pvt ) as firsttable

left join

(

SELECT SkillCategory, [0] as Exper,[1] as NonExp

FROM

(select ID, SkillCategory,Experienced from #Tbl_User ) p

PIVOT

(

COUNT (ID)

FOR Experienced IN

( [1], [0] )

) AS pvt

) as secondtable on

firsttable.skillcategory = secondtable.skillcategory

Hope that helps

Matt

|||

1 - Your result does not make much sense.

1.1 'J2EE' is not in the final result

1.2 '.Net' appears just one time in table [Tbl_Request] and the value of [NoOfPositionsRequired] is 10. So the value 12 for [Req1] in the final result is not correct

2 - For this kind of problem / question, is very helpful to post DDL, including constraints and indexes, sample data in the form of "insert" statements and expected result. That way we do not have to waste our time simulating your environment. The help should be in both way, shouldn't it?

Code Snippet

-- POST DDL

create table dbo.Tbl_Request (

RequestType varchar(10) not null,

NoOfPositionsRequired int not null,

SkillCategory varchar(15) not null

)

create table dbo.Tbl_User (

ID int not null,

SkillCategory varchar(15) not null,

Experienced smallint not null

)

-- POST SAMPLE DATA

insert into dbo.Tbl_Request values('Req1', 10, '.Net')

insert into dbo.Tbl_Request values('Req2', 3, 'Java')

insert into dbo.Tbl_Request values('Req1', 2, 'SQL')

insert into dbo.Tbl_Request values('Req3', 5, 'Java')

insert into dbo.Tbl_User values(101, 'Java', 0)

insert into dbo.Tbl_User values(102, '.Net', 1)

insert into dbo.Tbl_User values(103, 'Java', 1)

insert into dbo.Tbl_User values(104, 'SQL', 1)

insert into dbo.Tbl_User values(105, '.Net', 0)

insert into dbo.Tbl_User values(106, 'J2EE', 0)

declare @.pvt_columns nvarchar(max)

declare @.sel_columns nvarchar(max)

declare @.isnull nvarchar(max)

declare @.sum_col nvarchar(max)

declare @.sql nvarchar(max)

set @.pvt_columns = stuff(

(

select ',' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.isnull = N'isnull(' + replace(@.pvt_columns, ',', N',0),isnull(') + N', 0)'

set @.sum_col = replace(@.isnull, ',isnull', '+isnull')

set @.sel_columns = stuff(

(

select ',isnull(' + quotename(RequestType) + ',0) as ' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

exec sp_executesql @.sql

drop table dbo.Tbl_User, dbo.Tbl_Request

You have to be careful with SQL injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||Thank you hunchback..Thanks a lot. Apologies for the lack of schema. I will keep that in mind the next time i post some qustions on the forum.

Firstly my view requires me to have only those skills as demanded to appear and not all the skills that are possible. Hence J2EE was not included. Because there are some160 skills that covers all the associates and not all of whom are to be shown i.e not all those skill category people are to be shown. Secondly that was a small mistake and you are right about the count being 10 not 12 for .Net.

So could you tell me how I can achieve this..

Thanks again

|||

Hi,

A simple where clause would do it, in both select statement

Code Snippet

WHERE

SkillCategory IN ('.NET', 'SQL' .... )

Sure you could amend the above procedure to pass a list in as a variable

Code Snippet

declare @.listSkills varchar(100)

set @.ListSkills = '''.NET'',''SQL'''

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

where

skillcategory IN ('@.ListSkills')

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

where

skillcategory IN ('@.ListSkills')

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

Hope that helps

Matt

Combining two pivot tables and displaying the data

Hi all,
I have the following tables

Tbl_Request

RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
-

Tbl_User

ID SkillCategory Experienced
--
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0

Experience is a bool column.

Required Output:

SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply

.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
-

Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.

I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.

You can combine using join statement

Sample

Code Snippet

TempTable1 - SkillCategory,Req1,Req2,Req3,TotalDemand

TempTable2 - SkillCategory,Exp,NonExp,TotalSupply

Select T1.SkillCategory,T1.Req1,T1.Req2,T1.Req3,T1.TotalDemand,T2.Exp,T2.NonExp,T2.TotalSupply

from TempTable1 T1

left join TempTable2 T2 on T2.SkillCategory = T1.SkillCategory

|||

Hi Vidhura,

|||

Hi,

Using the above SQL Statement just replace the two temptables with select statements, as below

Code Snippet

--create table #Tbl_Request (

--RequestType varchar(20),

--NoOfPositionsRequired int ,

--SkillCategory varchar(20)

--)

--insert into #Tbl_Request values ('Req1', 10,'.Net')

--insert into #Tbl_Request values ('Req2',3,'Java')

--insert into #Tbl_Request values ('Req1',2,'SQL')

--insert into #Tbl_Request values ('Req3',5,'Java')

--create table #Tbl_User (ID int, SkillCategory varchar(20) ,Experienced int)

--insert into #Tbl_User values (101, 'Java', 0)

--insert into #Tbl_User values (102, '.Net', 1)

--insert into #Tbl_User values (103, 'Java', 1)

--insert into #Tbl_User values (104, 'SQL', 1)

--insert into #Tbl_User values (105, '.Net', 0)

--insert into #Tbl_User values (106, 'J2EE', 0)

select

from (

SELECT

SkillCategory,

isnull(Req1,0) Req1,

isnull(Req2,0)Req2,

isnull(Req3,0) Req3,

isnull(Req1,0)+ isnull(Req2,0)+ isnull(Req3,0) as TotalDemand

FROM

(select SkillCategory,NoOfPositionsRequired,RequestType from #Tbl_Request) p

PIVOT

(

SUM (NoOfPositionsRequired)

FOR RequestType IN

( Req1, Req2, Req3 )

) AS pvt ) as firsttable

left join

(

SELECT SkillCategory, [0] as Exper,[1] as NonExp

FROM

(select ID, SkillCategory,Experienced from #Tbl_User ) p

PIVOT

(

COUNT (ID)

FOR Experienced IN

( [1], [0] )

) AS pvt

) as secondtable on

firsttable.skillcategory = secondtable.skillcategory

Hope that helps

Matt

|||

1 - Your result does not make much sense.

1.1 'J2EE' is not in the final result

1.2 '.Net' appears just one time in table [Tbl_Request] and the value of [NoOfPositionsRequired] is 10. So the value 12 for [Req1] in the final result is not correct

Code Snippet

-- POST DDL

create table dbo.Tbl_Request (

RequestType varchar(10) not null,

NoOfPositionsRequired int not null,

SkillCategory varchar(15) not null

)

create table dbo.Tbl_User (

ID int not null,

SkillCategory varchar(15) not null,

Experienced smallint not null

)

-- POST SAMPLE DATA

insert into dbo.Tbl_Request values('Req1', 10, '.Net')

insert into dbo.Tbl_Request values('Req2', 3, 'Java')

insert into dbo.Tbl_Request values('Req1', 2, 'SQL')

insert into dbo.Tbl_Request values('Req3', 5, 'Java')

insert into dbo.Tbl_User values(101, 'Java', 0)

insert into dbo.Tbl_User values(102, '.Net', 1)

insert into dbo.Tbl_User values(103, 'Java', 1)

insert into dbo.Tbl_User values(104, 'SQL', 1)

insert into dbo.Tbl_User values(105, '.Net', 0)

insert into dbo.Tbl_User values(106, 'J2EE', 0)

declare @.pvt_columns nvarchar(max)

declare @.sel_columns nvarchar(max)

declare @.isnull nvarchar(max)

declare @.sum_col nvarchar(max)

declare @.sql nvarchar(max)

set @.pvt_columns = stuff(

(

select ',' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.isnull = N'isnull(' + replace(@.pvt_columns, ',', N',0),isnull(') + N', 0)'

set @.sum_col = replace(@.isnull, ',isnull', '+isnull')

set @.sel_columns = stuff(

(

select ',isnull(' + quotename(RequestType) + ',0) as ' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

exec sp_executesql @.sql

drop table dbo.Tbl_User, dbo.Tbl_Request

You have to be careful with SQL injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||Thank you hunchback..Thanks a lot. Apologies for the lack of schema. I will keep that in mind the next time i post some qustions on the forum.

So could you tell me how I can achieve this..

Thanks again

|||

Hi,

A simple where clause would do it, in both select statement

Code Snippet

WHERE

SkillCategory IN ('.NET', 'SQL' .... )

Sure you could amend the above procedure to pass a list in as a variable

Code Snippet

declare @.listSkills varchar(100)

set @.ListSkills = '''.NET'',''SQL'''

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

where

skillcategory IN ('@.ListSkills')

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

where

skillcategory IN ('@.ListSkills')

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

Hope that helps

Matt

Combining Time with a Date

Let's say that I have two tables, one of which has a StartDate field (say,
TableA) as well as a foreign key reference to TableB which has a field
called BeginTime. Suppose that values in TableA.StartDate field have varying
dates together with a time of 00:00:00.000 (as it's a datetime field) and
TableB.BeginTime fields have varying time values (e.g. 07:00:00.000). NOTE:
TableB.BeginTime field is currently an nvarchar field.
What I want to do is compare the current date (using GETDATE()) to the
datetime value that results from combining TableA.StartDate with
TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare the
current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
determine if the difference between them is greater than a certain # of
minutes. How could I do this using SQL?Bob
Look at DATEDIFF system function.
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Let's say that I have two tables, one of which has a StartDate field (say,
> TableA) as well as a foreign key reference to TableB which has a field
> called BeginTime. Suppose that values in TableA.StartDate field have
varying
> dates together with a time of 00:00:00.000 (as it's a datetime field) and
> TableB.BeginTime fields have varying time values (e.g. 07:00:00.000).
NOTE:
> TableB.BeginTime field is currently an nvarchar field.
> What I want to do is compare the current date (using GETDATE()) to the
> datetime value that results from combining TableA.StartDate with
> TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
> 00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare
the
> current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
> determine if the difference between them is greater than a certain # of
> minutes. How could I do this using SQL?
>|||I am familiar with the DATEDIFF function, but that will not combine separate
DATE and TIME values together to give me a DATETIME. That's the piece I'm
missing here.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1zyYHDNFHA.3076@.tk2msftngp13.phx.gbl...
> Bob
> Look at DATEDIFF system function.
>
> "BobRoyAce" <bob@.decisioncritical.com> wrote in message
> news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> varying
> NOTE:
> the
>|||Since you express datetime as a string, it is just a matter of building a st
ring expression which
can safely be converted to datetime. I didn't follow your first post, but le
ts assume that one value
is datetime and the other is a string:
DECLARE @.a datetime, @.b nvarchar(40)
SET @.a = getdate()
SET @.b = '07:00:00'
SELECT CAST(CONVERT(char(8), @.a, 112) + ' ' + @.b AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23mQ5VNDNFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am familiar with the DATEDIFF function, but that will not combine separat
e DATE and TIME values
>together to give me a DATETIME. That's the piece I'm missing here.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:u1zyYHDNFHA.3076@.tk2
msftngp13.phx.gbl...
>|||What's the 112 for?|||>> What's the 112 for?
It is the argument for CONVERT to return the ISO format( yymmdd ) for dates
represented as a string. See the topic CAST and CONVERT in SQL Server Books
Online.
Anithsql sql

Combining the content of two tables

Hi all,

How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks

Table A

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Table B

Client Weight Purchase

Jim 2 5

Lee 4 3

Bob 6 7

Combination table (result)

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Jim 2 5

Lee 4 3

Bob 6 7

Give a look to the UNION and UNION ALL operators in books online. It should look something like this:

Code Snippet

select client,
weight,
purchase
from [table a]
union all -- or perhaps union
select client,
weight,
purchase
from [table b]

|||Works perfectly. Thanks.sql sql

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

Paragraph 1 of Document 1

Paragraph 2 of Document 1

Paragraph 3 of Document 1

Paragraph 1 of Document 2

Paragraph 2 of Document 2

New TEXT table

DocumentID

SectionText

Entire text of Document 1

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]

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

DocumentID

SectionNo

SectionText

Blue

Red

Green

White

Black

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

The NewText table that was created looked like this:

DocumentID

Paragraph1

Null

I think the NewText Table should have looked like this:

DocumentID

Paragraph1

Blue

Red

Green

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

Thursday, March 29, 2012

combining two tables with a full-text search

Combining Two Tables Via T-SQL

Combining two tables to make a third

Combining two tables to make a third

Combining two Tables into one TempTable, with a condition.

Combining two tables from different databases

combining two tables

combining two tables

Combining two table into a single table

Combining two seperate tables into one

Combining two pivot tables and displaying the data

Combining two pivot tables and displaying the data

Combining Time with a Date

Combining the content of two tables

Combining text data rows

Null

Null

Collation problem

Blog Archive

About Me