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)sqlsql

Combine two int columns into one bigint column

Hello
I have created a database that uses an int for the table primary keys.
This database is being deployed at several sites. Each site has a
unique site ID which is less than 100.
There is another database that acts as a master viewer. This database
holds a copy of all the site databases to do reports across all the
sites. Basically each site database gets 'merged' with the master
database once a month.
The master viewer database uses exactly the same structure as the site
database, but needs to store the site ID with every record. I have
currently achieved this by changing the table primary key to be a
bigint and store the site ID in 30 bits and the actual site record
primary in the remaining 34 bits (ok I could have used 32/32). The
reason for not adding another primary key column is because I use the
exact same sql queries in the master database and site databases, and
adding another column would mean creating two seperate queries when
joining tables (one to work on the master and one to work on the sites
- more work and difficult to maintain).
It is relatively simple to extract the site ID and record primary key
from the bigint using bitwise operations and bit shifting.
Unfortunately sql does not support bit shifting and I use division for
the same affect. The only downside I see is the performance issue when
extracting the site primary key in a sql query. If I want to test the
site primary key I use "WHERE ((MaintenanceTransaction_PRK &
-1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
1073741824. If the table has tens of thousands/millions of records I
can see this taking a while.
Does anyone have any other suggestions that I could use?
Many thanks
PaulChange your database in the master and client sites to include site ID as
part of the key in all of them. Then you can write one SQL that will run
against all of your databases equally. This will server you better in the
long run.
Or you can simply multiply the PK by 10000 and add the site ID to it to get
the new ID. This will give you a new combined ID without all that screwing
around with bits. Much simpler and you will have room for 10000 customers
before you run out of site IDs. This really is a kludge, however, and not
the best way to solve the problem.
<kerplunkwhoops@.yahoo.co.uk> wrote in message
news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...
> Hello
> I have created a database that uses an int for the table primary keys.
> This database is being deployed at several sites. Each site has a
> unique site ID which is less than 100.
> There is another database that acts as a master viewer. This database
> holds a copy of all the site databases to do reports across all the
> sites. Basically each site database gets 'merged' with the master
> database once a month.
> The master viewer database uses exactly the same structure as the site
> database, but needs to store the site ID with every record. I have
> currently achieved this by changing the table primary key to be a
> bigint and store the site ID in 30 bits and the actual site record
> primary in the remaining 34 bits (ok I could have used 32/32). The
> reason for not adding another primary key column is because I use the
> exact same sql queries in the master database and site databases, and
> adding another column would mean creating two seperate queries when
> joining tables (one to work on the master and one to work on the sites
> - more work and difficult to maintain).
> It is relatively simple to extract the site ID and record primary key
> from the bigint using bitwise operations and bit shifting.
> Unfortunately sql does not support bit shifting and I use division for
> the same affect. The only downside I see is the performance issue when
> extracting the site primary key in a sql query. If I want to test the
> site primary key I use "WHERE ((MaintenanceTransaction_PRK &
> -1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
> MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
> 1073741824. If the table has tens of thousands/millions of records I
> can see this taking a while.
> Does anyone have any other suggestions that I could use?
> Many thanks
> Paul
>|||Hello
I did consider changing the whole of the project to include the site ID
as a seperate column in the site database, but that would be a huge
amount of work and the master viewer is a special case for one
customer. I was trying to make it as simple as possible. It all works
correctly now, but I am just concerned about the performance.
Your solution about mutliplying by 10000 is the same concept to what I
do currently. It still has the problem of division to extract the
primary key.
Thanks
Paul
Jim Underwood wrote:
> Change your database in the master and client sites to include site ID as
> part of the key in all of them. Then you can write one SQL that will run
> against all of your databases equally. This will server you better in the
> long run.
> Or you can simply multiply the PK by 10000 and add the site ID to it to ge
t
> the new ID. This will give you a new combined ID without all that screwin
g
> around with bits. Much simpler and you will have room for 10000 customers
> before you run out of site IDs. This really is a kludge, however, and not
> the best way to solve the problem.
> <kerplunkwhoops@.yahoo.co.uk> wrote in message
> news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...|||I am not sure if you will be able to get the Site Id by using multiplication
and division.
Consider the following scenario
Site Id Other Id
10 1000 = 10X1000 = 10000
20 500 = 20X500 = 10000
You will not be able to find out the site Id using division as both the
multiplication
results in the same value.
You could try changing the datatype of Master database's Id col to VARCHAR
and have Id values as
10 concatenated with 00001000 as 1000001000
20 concatenated with 00000500 as 1000000500.
I hope doing this will not affect your Queries as there are not new column
and only a DataTypeChange. You could also extract the site id by using
substring (first 2 chrs) functions.
- Sha Anand
"kerplunkwhoops@.yahoo.co.uk" wrote:

> Hello
> I did consider changing the whole of the project to include the site ID
> as a seperate column in the site database, but that would be a huge
> amount of work and the master viewer is a special case for one
> customer. I was trying to make it as simple as possible. It all works
> correctly now, but I am just concerned about the performance.
> Your solution about mutliplying by 10000 is the same concept to what I
> do currently. It still has the problem of division to extract the
> primary key.
> Thanks
> Paul
>
> Jim Underwood wrote:
>

Column space utilization

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
NavaneethIt will only use the space for your text.|||Thank you marcel,

I am planning to implement search functionality for my website. So choosing which datatype for search keywords will be optimum for me ? I need to store large number of data in this filed. I planned for text type. Is it a good choice ? Will text type utilize more space than any other datatype ? How much big my SQL database can grow ?|||You might was to look into using Full Text Search. Search in Book On Line for this topic.

Thanks,|||

Hello!

Moving forward you should NOT USE TEXT data types. TEXT has been obsoleted. VARCHAR(MAX) is the replacement.

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||Thank you,

But upto my knowledge I think varchar can store only 8000 characters ? In new versions is it increased ?|||varchar(max) can store as much information as text. (so upto 2GB)

Thanks,

Collation question

Hello!
I am building a Russian web site. Site request and response encoding is set
to windows-1251, data is displayed correctly. But in MS SQL Manager
evrything is messed up... like this ? ? ?
Database table field colaltion is set to: COLLATE Cyrillic_General_CI_AS
What I am doing wrong?
Thank you!
James
James
Did you define NVARCHAR(n) datatype for this column?
"James T." <gimenei@.hotmail.com> wrote in message
news:OLA2iyXUFHA.1148@.tk2msftngp13.phx.gbl...
> Hello!
> I am building a Russian web site. Site request and response encoding is
set
> to windows-1251, data is displayed correctly. But in MS SQL Manager
> evrything is messed up... like this ? ? ?
> Database table field colaltion is set to: COLLATE Cyrillic_General_CI_AS
> What I am doing wrong?
> Thank you!
> James
>

Collation question

Hello!
I am building a Russian web site. Site request and response encoding is set
to windows-1251, data is displayed correctly. But in MS SQL Manager
evrything is messed up... like this ? ' '
Database table field colaltion is set to: COLLATE Cyrillic_General_CI_AS
What I am doing wrong?
Thank you!
JamesJames
Did you define NVARCHAR(n) datatype for this column?
"James T." <gimenei@.hotmail.com> wrote in message
news:OLA2iyXUFHA.1148@.tk2msftngp13.phx.gbl...
> Hello!
> I am building a Russian web site. Site request and response encoding is
set
> to windows-1251, data is displayed correctly. But in MS SQL Manager
> evrything is messed up... like this ? ' '
> Database table field colaltion is set to: COLLATE Cyrillic_General_CI_AS
> What I am doing wrong?
> Thank you!
> James
>