Thanks Rudy for the answer of my previous thread.
Here's another scenario of my problem that i hardly get the correct query.
I have a tables with fields and sample records in my database;
Table: tblPo
FIELDS RECORD1
po_number: PO10001
entered_by: chris
approved_by: albert
Table: tblUser
FIELDS RECORD1 RECORD2
user_id: chris albert
first_name: Christopher Alberto
mi: S W
last_name: Lambert Del Puerto
So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.
Now, I need to get a result like this:
PO # Prepared by Approved by
PO10001 Christopher S. Lambert Alberto W. Del Puerto
My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.
Any help? thanks in advance
BernieHere you go :
use pubs
go
create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
go
create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
go
insert into tblPO values('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
go
select
a.po_number,
isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by
from
tblPO a,
tbluser b,
tbluser c
where
a.entered_by = b.user_id
and a.approved_by = c.user_id
go
drop table tbluser
go
drop table tblpo
go|||create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by
po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto
(1 row(s) affected)|||Originally posted by cvandemaele
create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by
po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto
(1 row(s) affected)
Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null|||SALAMAT! Thank you guyz...
I can proceed now with my report...
Berniesql
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 30, 2012
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into sql server 2000 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3 column unique index (alternate key) and a detail table that has a DETAIL_ID (IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Using SPs calls is not the fastest way. The quickest way is to generate files including the identity values and then bcp/.bulk insert the data into SQL. You could use SSIS to do this as well.
The reason you should generate you ID values outside of the database is so that when spliting your input file into your tables you can just insert the data straight into the tables. You can let SQL generate the PK identity values of the detail tables.
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Help Im a Noob - Updating a database entry.
Ok, so I work with a certain program which has a wide open back door into it's data tables. As a program administrator i can go into the actual program and change details of other users etc. However here's what i need to do...
A certain users security level needs to be reduced from administrator to a lower level. If i do that within the program it will automatically require that i change that users password, which i don't know.
The user is not to know that their access level has changed.
Backdooring the database through MSQuery and hunting online a little i found how to update information...
UPDATE User
SET Security = 4
WHERE UserID = 4
This works fine, however the users password is then scrambled, until an administrator edits their password for them.
The password itself is encrypted within the database.
Appearing something like "99171821E244877B4201003E0519EEA7"
It appears to be hexadecimal, but hey, i'm no system engineer. Strange thing is. that encryption represents the password "bill". When i execute the change on security level, the password "bill" becomes invalid, yet the encrypted string remains exactly the same.
What i need if at all possible is a how to... on forcing the database to not change the password field when the security level is updated. Any other suggestions would be most welcome. And of course plain english with syntax explanations would be nice as i am very much a junior, this has been my first SQL code execution, aside from queries.
Cheers,
OostersUnfortunately for you, this seems to be an application issue.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:|||Application problems seem pretty indiscriminant about what engines they affect to me. If an app uses SQL, and that SQL breaks, it breaks for any database that implements SQL at the same level. If an app uses database engine specific code (Oracle, Microsoft, DB2, etc), then that app will probably only run on its target database engine, but that is a failure in the application, not the database.
I think that the problem that Oosters has found is that multiple columns within the users table are used to derive (actually decrypt) the password. It would appear that the application designers wanted to prevent exactly the kind of change that Oosters is trying to make.
-PatP|||Unfortunately for you, this seems to be an application issue.
Indeed.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:
And yet you felt it was imperative to squeeze that into your post regardless.
Quality.
Would you pretty pretty please proceed to regal me with unsolicited opinions and stories about how linux cured your stepdad's brother's cousin's dogs' nephew's sister's case of terminal cancer, or perhaps the one about Microsoft releasing OS's laced with mind control algorhythms that only appear at a particular resolution and refresh rate?|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Meanwhile I think i know what's happening with the password. I think instead of the database automatically changing the password when i update the security level of a user, there may be another field to lock the password when i update security level, and this will only unlock when another password is offered. I can't even get around it by feeding the database it's own encryption string, which should force it to recognise a password change. I'm off to hunt for a password locking field.
I could destroy the integrity of the entire database at the touch of a button, but i can't change a single users security level.|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Because controlling access to your database server probably isn't their job. Who is going to keep the database running if everybody is completely locked out of it?|||One of the really fundamental elements of good security design is to limit the damage that can be done without detection. A fundamental way to achieve this is to require some form of intervention from one or more of the affected parties whenever a change is made. The example you've got is when a security level is changed, make that change require a password change.
This has little to do with the database. It is a fundamental requisite for good security management.
While I'm sure that it frustrates you, I see it as a good thing in the larger context because it makes an otherwise unannounced change visible to the affected party. While it might not be politically expedient, you shouldn't be able (from a systemic point of view) to take away (or grant) administrative privledges without the knowledge of the user.
-PatP|||Yes, In an ideal world, in an ideal database i shouldn't be able to change user security levels without their knowledge. This being said...
<RANT>
Considering the sensitivity of information as this is a financial database representing the business of the whole company, I shouldn't be permitted to backdoor and alter financial information, and yet as long as i make it balance, which can take a bit of figuring, the database security allows such changes.
</RANT>
No need to respond to this, thanks for the knowledge people.
regards,
Matt
A certain users security level needs to be reduced from administrator to a lower level. If i do that within the program it will automatically require that i change that users password, which i don't know.
The user is not to know that their access level has changed.
Backdooring the database through MSQuery and hunting online a little i found how to update information...
UPDATE User
SET Security = 4
WHERE UserID = 4
This works fine, however the users password is then scrambled, until an administrator edits their password for them.
The password itself is encrypted within the database.
Appearing something like "99171821E244877B4201003E0519EEA7"
It appears to be hexadecimal, but hey, i'm no system engineer. Strange thing is. that encryption represents the password "bill". When i execute the change on security level, the password "bill" becomes invalid, yet the encrypted string remains exactly the same.
What i need if at all possible is a how to... on forcing the database to not change the password field when the security level is updated. Any other suggestions would be most welcome. And of course plain english with syntax explanations would be nice as i am very much a junior, this has been my first SQL code execution, aside from queries.
Cheers,
OostersUnfortunately for you, this seems to be an application issue.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:|||Application problems seem pretty indiscriminant about what engines they affect to me. If an app uses SQL, and that SQL breaks, it breaks for any database that implements SQL at the same level. If an app uses database engine specific code (Oracle, Microsoft, DB2, etc), then that app will probably only run on its target database engine, but that is a failure in the application, not the database.
I think that the problem that Oosters has found is that multiple columns within the users table are used to derive (actually decrypt) the password. It would appear that the application designers wanted to prevent exactly the kind of change that Oosters is trying to make.
-PatP|||Unfortunately for you, this seems to be an application issue.
Indeed.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:
And yet you felt it was imperative to squeeze that into your post regardless.
Quality.
Would you pretty pretty please proceed to regal me with unsolicited opinions and stories about how linux cured your stepdad's brother's cousin's dogs' nephew's sister's case of terminal cancer, or perhaps the one about Microsoft releasing OS's laced with mind control algorhythms that only appear at a particular resolution and refresh rate?|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Meanwhile I think i know what's happening with the password. I think instead of the database automatically changing the password when i update the security level of a user, there may be another field to lock the password when i update security level, and this will only unlock when another password is offered. I can't even get around it by feeding the database it's own encryption string, which should force it to recognise a password change. I'm off to hunt for a password locking field.
I could destroy the integrity of the entire database at the touch of a button, but i can't change a single users security level.|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Because controlling access to your database server probably isn't their job. Who is going to keep the database running if everybody is completely locked out of it?|||One of the really fundamental elements of good security design is to limit the damage that can be done without detection. A fundamental way to achieve this is to require some form of intervention from one or more of the affected parties whenever a change is made. The example you've got is when a security level is changed, make that change require a password change.
This has little to do with the database. It is a fundamental requisite for good security management.
While I'm sure that it frustrates you, I see it as a good thing in the larger context because it makes an otherwise unannounced change visible to the affected party. While it might not be politically expedient, you shouldn't be able (from a systemic point of view) to take away (or grant) administrative privledges without the knowledge of the user.
-PatP|||Yes, In an ideal world, in an ideal database i shouldn't be able to change user security levels without their knowledge. This being said...
<RANT>
Considering the sensitivity of information as this is a financial database representing the business of the whole company, I shouldn't be permitted to backdoor and alter financial information, and yet as long as i make it balance, which can take a bit of figuring, the database security allows such changes.
</RANT>
No need to respond to this, thanks for the knowledge people.
regards,
Matt
Help Identifying Full table scans with objectname
Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Help Identifying Full table scans with objectname
Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Help How Make Backup and Restore ?
Hi all. I'm newbie user of sqlserver 7.
I need make Backup from specific tables of my database and my application send this file by mail to other users that restore this information in yours Database.
How i can do it (i dont know the specifics commands to backup and Restore)
Other question
if I only backup the log file and restores this in other database
I'll have the same information in all users ?
Help me please
Thanks in AdvanceCheck BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.|||Check BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.
Excuse me, but i don't understand what is BOL. ?
How i said i'm newbie.|||Books
On
Line|||Originally posted by rdjabarov
Books
On
Line
Thanks, I will Try.|||I connect like database admin and
try to make differential backup using this command
1- Back up Database Cyber to XXX With Differential
2- Back up Log Cyber to XXX
The to test the back up
I run then this restore command
1- Restore database Cyber from XXX with norecovery and
2- Restore Log Cyber from XXX with recovery.
All is perfect
I close the connection
When I try to connect to Cyber database the server send me this message
Server Msg 927, Level 14 State 2
Database Cyber Cannot Be opened. It is in the middle of restore ?
Why the database cannot be opened ?
Thank In advance
Franklin|||Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.|||I'm trying to open from query analizer, because my application must be do it (the backup - restore) using transaq - sql.
thanks
franklin
Originally posted by rdjabarov
Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.sql
I need make Backup from specific tables of my database and my application send this file by mail to other users that restore this information in yours Database.
How i can do it (i dont know the specifics commands to backup and Restore)
Other question
if I only backup the log file and restores this in other database
I'll have the same information in all users ?
Help me please
Thanks in AdvanceCheck BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.|||Check BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.
Excuse me, but i don't understand what is BOL. ?
How i said i'm newbie.|||Books
On
Line|||Originally posted by rdjabarov
Books
On
Line
Thanks, I will Try.|||I connect like database admin and
try to make differential backup using this command
1- Back up Database Cyber to XXX With Differential
2- Back up Log Cyber to XXX
The to test the back up
I run then this restore command
1- Restore database Cyber from XXX with norecovery and
2- Restore Log Cyber from XXX with recovery.
All is perfect
I close the connection
When I try to connect to Cyber database the server send me this message
Server Msg 927, Level 14 State 2
Database Cyber Cannot Be opened. It is in the middle of restore ?
Why the database cannot be opened ?
Thank In advance
Franklin|||Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.|||I'm trying to open from query analizer, because my application must be do it (the backup - restore) using transaq - sql.
thanks
franklin
Originally posted by rdjabarov
Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.sql
Wednesday, March 28, 2012
help getting parameters back from a stored procedure
Hello everyone,
I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.
--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation
--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0')}
--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer
' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn
' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@.RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@.BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@.BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@.BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@.ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@.RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@.BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@.BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@.BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@.DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@.ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum
' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)
' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@.RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@.BulkRan")
gstrBulkForName = rsTableInfo.Fields("@.BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@.BulkJit")
gstrID = rsTableInfo.Fields("@.ID")
intErrorNum = rsTableInfo.Fields("@.ErrorNum")
End Sub
any help would be appreciatedactually i just solved my own problem:
at the end of the stored procedure i selected the columns i wanted to return and that did it.
select @.RQSodfil,@.BulkRan,@.BulkFor,@.BulkJit,@.ID,@.ErrorNum
i also just did a regular sql statement where i called the EXEC command to run my stored procedure.
EXEC CreateTempTables @.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0'
hopefully if someone else has the same problem i've had they can see what i did.
help for T-SQL code generator for DataMart
I replicated a table from DW to DM
but i filtered the table by month.
now i have several tables with the same schema on the datamart
the table has five keys. what i want to do is to write a
sqlwizard code that will automatically write an update statement from the replicated
table. What the wizard will do is read the fields of the DM table then identify the keys and
generate the source code for update.
lets name the proc sqlwiz
exec sqlwiz (DMtable1,dwtable1)
the sp should return the desired update statement like this
update dmtable1 set DM.nonkeyfield1= dw.nonkeyfield1,
DM.nonkeyfield2= dw.nonkeyfield2,
DM.nonkeyfield3= dw.nonkeyfield3
from dwtable1 dw where
dm.keyfield1=dw.keyfield1 and
dm.keyfield2=dw.keyfield2
pls use any of the northwind table with composite pk.
my DM is sql2k5.
the sp can also be used for generating update codes for vb.net
thanks,
joey
1. You will need to use Dynamic SQL
2. Make use of INFORMATION_SCHEMA.COLUMNS
Help for Query
I have two tables; Table1 and Table2 with some sample data:
Table1
CustNum
C041
C042
C043
C044
Table2
A01 A02 A03 A04
100.00 120.00 150.00 160.00
200.00 210.00 250.00 260.00
300.00 310.00 320.00 350.00
150.00 160.00 170.00 180.00
My results should look like this out of above two tables. You will see that
column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName a
s
data against CustName (Table1) and values (Table2).
CustName ColumnName Values
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
Your assistance will be appreciated.
Thanks...FrazIt would be so much easier if your data were Normalized. And you don't have
any form of a PK-FK relationship between the tables.
Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a simp
le JOIN could be used. You might find this article useful: http://www.datamodel.or
g/No...ationRules.html
The following works to provide you the solution you seek -but it is a bit aw
kward and will be difficult to scale.
CREATE TABLE Table1
( CustNum varchar(10) )
GO
INSERT INTO Table1 VALUES ( 'C041' )
INSERT INTO Table1 VALUES ( 'C042' )
INSERT INTO Table1 VALUES ( 'C043' )
INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE Table2
( A01 decimal
, A02 decimal
, A03 decimal
, A04 decimal
)
GO
INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT
CustNum
, 'A01' AS 'ColumnName'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C041'
UNION
SELECT
CustNum
, 'A02'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C042'
UNION
SELECT
CustNum
, 'A03'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C043'
UNION
SELECT
CustNum
, 'A04'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C044'
ORDER BY
CustNum
, ColumnName
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D06-47B6-A2E3-513135
CE387D@.microsoft.com...
>I have two tables; Table1 and Table2 with some sample data:
>
> Table1
> CustNum
> C041
> C042
> C043
> C044
>
> Table2
> A01 A02 A03 A04
> 100.00 120.00 150.00 160.00
> 200.00 210.00 250.00 260.00
> 300.00 310.00 320.00 350.00
> 150.00 160.00 170.00 180.00
>
> My results should look like this out of above two tables. You will see tha
t
> column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName
as
> data against CustName (Table1) and values (Table2).
>
> CustName ColumnName Values
> C041 A01 100.00
> C041 A02 120.00
> C041 A03 150.00
> C041 A04 160.00
> C042 A01 200.00
> C042 A02 210.00
> C042 A03 250.00
> C042 A04 260.00
> C043 A01 300.00
> C043 A02 310.00
> C043 A03 320.00
> C043 A04 350.00
> C044 A01 150.00
> C044 A02 160.00
> C044 A03 170.00
> C044 A04 180.00
>
> Your assistance will be appreciated.
> Thanks...Fraz|||On Tue, 11 Jul 2006 06:44:48 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>It would be so much easier if your data were Normalized. And you don't have
any form of a PK-FK relationship between the tables.
>Based upon this use, Table2 should really be CustNum, ColumnName,
>ColValue. Then a simple JOIN could be used. You might find this
>article useful: http://www.datamodel.org/NormalizationRules.html
Arnie, I think it is even worse than you described. As I read the
original query, the result set assumes that the two tables are matched
up first-row-to-first, second-row-to-second, etc. Even your query
can't fix THAT problem.
Roy|||I appreciate your feedback, Arnie. We receive the data file from external
source.
The CustNum in Table1 has about 1000 records. Table2 also has 17000 records.
If the sample works well then I wil join other tables which are identical to
Table2 and columnname like B01, B02, C01, C02 etc.
I am wondering if there is an easier way because using a UNION for each
CustNum will make the script too big. Thanks again. Please free to provide
your feedback.
Thanks...Fraz
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It would be so much easier if your data were Normalized. And you don't hav
e any form of a PK-FK relationship between the tables.
> Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a si
mple JOIN could be used. You might find this article useful: http://www.datamodel.
org/No...ationRules.html
> The following works to provide you the solution you seek -but it is a bit
awkward and will be difficult to scale.
> CREATE TABLE Table1
> ( CustNum varchar(10) )
> GO
> INSERT INTO Table1 VALUES ( 'C041' )
> INSERT INTO Table1 VALUES ( 'C042' )
> INSERT INTO Table1 VALUES ( 'C043' )
> INSERT INTO Table1 VALUES ( 'C044' )
> CREATE TABLE Table2
> ( A01 decimal
> , A02 decimal
> , A03 decimal
> , A04 decimal
> )
> GO
> INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
> INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
> INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
> INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
>
> SELECT
> CustNum
> , 'A01' AS 'ColumnName'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C041'
> UNION
> SELECT
> CustNum
> , 'A02'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C042'
> UNION
> SELECT
> CustNum
> , 'A03'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C043'
> UNION
> SELECT
> CustNum
> , 'A04'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C044'
> ORDER BY
> CustNum
> , ColumnName
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D0
6-47B6-A2E3-513135CE387D@.microsoft.com...|||I have made some comments below.
On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>I have two tables; Table1 and Table2 with some sample data:
>Table1
>CustNum
>C041
>C042
>C043
>C044
>Table2
>A01 A02 A03 A04
>100.00 120.00 150.00 160.00
>200.00 210.00 250.00 260.00
>300.00 310.00 320.00 350.00
>150.00 160.00 170.00 180.00
These are ALL the rows? No key? Even denormalized tables need a key.
>My results should look like this out of above two tables. You will see that
>column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName
as
>data against CustName (Table1) and values (Table2).
>CustName ColumnName Values
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
It appears that, somehow, a specific CustNum from Table1 (the "first")
is supposed to match up with the "first" row in Table2'
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
Same again, but the "second"?
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
etc.
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
etc.
>Your assistance will be appreciated.
>Thanks...Fraz
I see no way to produce the results you requested from the tables
given. Without CustNum on Table2 there is no way to match the two
tables up the way your sample output specifies. Relational tables
have no order, there is no "first", "next" or "last", so there can be
no joining on such a basis.
Please take the time to learn about data normalization. However long
it takes to learn - and it is really quite simple - compared to the
time you will waste trying to work with things like this it will pay
off easily.
Roy Harvey
Beacon Falls, CT|||My apologies. There are PK and FK keys. Between Table1 and Table2 there is
one-to-one relationship. Each table has 50-60 columns but I will precisely
re-append the tables:
Table1
Id (PK) CustNum
100 C041
101 C042
102 C043
105 C044
Table2
ID (FK) A01 A02 A03 A04
100 100.00 120.00 150.00 160.00
101 200.00 210.00 250.00 260.00
102 300.00 310.00 320.00 350.00
105 150.00 160.00 170.00 180.00
Results required by user in the output file as follows:
CustName ColumnName Value
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
You will notice that values in ColumnName is actually column names in Table2
.
Thanks...Fraz
"Roy Harvey" wrote:
> I have made some comments below.
> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
>
> These are ALL the rows? No key? Even denormalized tables need a key.
>
> It appears that, somehow, a specific CustNum from Table1 (the "first")
> is supposed to match up with the "first" row in Table2'
>
> Same again, but the "second"?
>
> etc.
>
> etc.
>
> I see no way to produce the results you requested from the tables
> given. Without CustNum on Table2 there is no way to match the two
> tables up the way your sample output specifies. Relational tables
> have no order, there is no "first", "next" or "last", so there can be
> no joining on such a basis.
> Please take the time to learn about data normalization. However long
> it takes to learn - and it is really quite simple - compared to the
> time you will waste trying to work with things like this it will pay
> off easily.
> Roy Harvey
> Beacon Falls, CT
>|||Untested, but this should do it.
SELECT CustNum,
ColumName =
CASE N.I
WHEN 1 THEN 'A01'
WHEN 2 THEN 'A02'
WHEN 3 THEN 'A03'
WHEN 4 THEN 'A04'
END,
Value =
CASE N.I
WHEN 1 THEN A01
WHEN 2 THEN A02
WHEN 3 THEN A03
WHEN 4 THEN A04
END
FROM Table1 as A
JOIN Table2 as B
ON A.Id = B.Id
CROSS JOIN
(select 1 as I UNION ALL
select 2 as I UNION ALL
select 3 as I UNION ALL
select 4 as I) as N
Roy Harvey
Beacon Falls, CT
On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>My apologies. There are PK and FK keys. Between Table1 and Table2 there is
>one-to-one relationship. Each table has 50-60 columns but I will precisely
>re-append the tables:
>Table1
>Id (PK) CustNum
>100 C041
>101 C042
>102 C043
>105 C044
>Table2
>ID (FK) A01 A02 A03 A04
>100 100.00 120.00 150.00 160.00
>101 200.00 210.00 250.00 260.00
>102 300.00 310.00 320.00 350.00
>105 150.00 160.00 170.00 180.00
>Results required by user in the output file as follows:
>CustName ColumnName Value
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
>You will notice that values in ColumnName is actually column names in Table
2.
>Thanks...Fraz
>
>"Roy Harvey" wrote:
>|||It looks promising. I will use this concept to add other tables. Thanks a lo
t
Roy and Arnie. Regards, Fraz.
"Roy Harvey" wrote:
> Untested, but this should do it.
> SELECT CustNum,
> ColumName =
> CASE N.I
> WHEN 1 THEN 'A01'
> WHEN 2 THEN 'A02'
> WHEN 3 THEN 'A03'
> WHEN 4 THEN 'A04'
> END,
> Value =
> CASE N.I
> WHEN 1 THEN A01
> WHEN 2 THEN A02
> WHEN 3 THEN A03
> WHEN 4 THEN A04
> END
> FROM Table1 as A
> JOIN Table2 as B
> ON A.Id = B.Id
> CROSS JOIN
> (select 1 as I UNION ALL
> select 2 as I UNION ALL
> select 3 as I UNION ALL
> select 4 as I) as N
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
>
>|||Hi,
Try this querry
SELECT a.custname,b.col,b.val FROM table a,
(
SELECT 'A01' as 'col', a01 as 'val' from table2
UNION ALL
SELECT 'A02' as 'col' , a02 as 'val' from table2
UNION ALL
SELECT 'A03' as 'col' , a03 as 'val' from table2
UNION ALL
SELECT 'A04' as 'col' , a04 as 'val' from table2
) b
ORDER BY a.custname
Amol Lembhe
"Fraz" wrote:
[vbcol=seagreen]
> It looks promising. I will use this concept to add other tables. Thanks a
lot
> Roy and Arnie. Regards, Fraz.
> "Roy Harvey" wrote:
>|||Thanks Amol. Fraz
"Amol Lembhe" wrote:
[vbcol=seagreen]
> Hi,
> Try this querry
> SELECT a.custname,b.col,b.val FROM table a,
> (
> SELECT 'A01' as 'col', a01 as 'val' from table2
> UNION ALL
> SELECT 'A02' as 'col' , a02 as 'val' from table2
> UNION ALL
> SELECT 'A03' as 'col' , a03 as 'val' from table2
> UNION ALL
> SELECT 'A04' as 'col' , a04 as 'val' from table2
> ) b
> ORDER BY a.custname
> Amol Lembhe
>
> "Fraz" wrote:
>
Table1
CustNum
C041
C042
C043
C044
Table2
A01 A02 A03 A04
100.00 120.00 150.00 160.00
200.00 210.00 250.00 260.00
300.00 310.00 320.00 350.00
150.00 160.00 170.00 180.00
My results should look like this out of above two tables. You will see that
column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName a
s
data against CustName (Table1) and values (Table2).
CustName ColumnName Values
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
Your assistance will be appreciated.
Thanks...FrazIt would be so much easier if your data were Normalized. And you don't have
any form of a PK-FK relationship between the tables.
Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a simp
le JOIN could be used. You might find this article useful: http://www.datamodel.or
g/No...ationRules.html
The following works to provide you the solution you seek -but it is a bit aw
kward and will be difficult to scale.
CREATE TABLE Table1
( CustNum varchar(10) )
GO
INSERT INTO Table1 VALUES ( 'C041' )
INSERT INTO Table1 VALUES ( 'C042' )
INSERT INTO Table1 VALUES ( 'C043' )
INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE Table2
( A01 decimal
, A02 decimal
, A03 decimal
, A04 decimal
)
GO
INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT
CustNum
, 'A01' AS 'ColumnName'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C041'
UNION
SELECT
CustNum
, 'A02'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C042'
UNION
SELECT
CustNum
, 'A03'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C043'
UNION
SELECT
CustNum
, 'A04'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C044'
ORDER BY
CustNum
, ColumnName
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D06-47B6-A2E3-513135
CE387D@.microsoft.com...
>I have two tables; Table1 and Table2 with some sample data:
>
> Table1
> CustNum
> C041
> C042
> C043
> C044
>
> Table2
> A01 A02 A03 A04
> 100.00 120.00 150.00 160.00
> 200.00 210.00 250.00 260.00
> 300.00 310.00 320.00 350.00
> 150.00 160.00 170.00 180.00
>
> My results should look like this out of above two tables. You will see tha
t
> column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName
as
> data against CustName (Table1) and values (Table2).
>
> CustName ColumnName Values
> C041 A01 100.00
> C041 A02 120.00
> C041 A03 150.00
> C041 A04 160.00
> C042 A01 200.00
> C042 A02 210.00
> C042 A03 250.00
> C042 A04 260.00
> C043 A01 300.00
> C043 A02 310.00
> C043 A03 320.00
> C043 A04 350.00
> C044 A01 150.00
> C044 A02 160.00
> C044 A03 170.00
> C044 A04 180.00
>
> Your assistance will be appreciated.
> Thanks...Fraz|||On Tue, 11 Jul 2006 06:44:48 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>It would be so much easier if your data were Normalized. And you don't have
any form of a PK-FK relationship between the tables.
>Based upon this use, Table2 should really be CustNum, ColumnName,
>ColValue. Then a simple JOIN could be used. You might find this
>article useful: http://www.datamodel.org/NormalizationRules.html
Arnie, I think it is even worse than you described. As I read the
original query, the result set assumes that the two tables are matched
up first-row-to-first, second-row-to-second, etc. Even your query
can't fix THAT problem.
Roy|||I appreciate your feedback, Arnie. We receive the data file from external
source.
The CustNum in Table1 has about 1000 records. Table2 also has 17000 records.
If the sample works well then I wil join other tables which are identical to
Table2 and columnname like B01, B02, C01, C02 etc.
I am wondering if there is an easier way because using a UNION for each
CustNum will make the script too big. Thanks again. Please free to provide
your feedback.
Thanks...Fraz
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It would be so much easier if your data were Normalized. And you don't hav
e any form of a PK-FK relationship between the tables.
> Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a si
mple JOIN could be used. You might find this article useful: http://www.datamodel.
org/No...ationRules.html
> The following works to provide you the solution you seek -but it is a bit
awkward and will be difficult to scale.
> CREATE TABLE Table1
> ( CustNum varchar(10) )
> GO
> INSERT INTO Table1 VALUES ( 'C041' )
> INSERT INTO Table1 VALUES ( 'C042' )
> INSERT INTO Table1 VALUES ( 'C043' )
> INSERT INTO Table1 VALUES ( 'C044' )
> CREATE TABLE Table2
> ( A01 decimal
> , A02 decimal
> , A03 decimal
> , A04 decimal
> )
> GO
> INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
> INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
> INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
> INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
>
> SELECT
> CustNum
> , 'A01' AS 'ColumnName'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C041'
> UNION
> SELECT
> CustNum
> , 'A02'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C042'
> UNION
> SELECT
> CustNum
> , 'A03'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C043'
> UNION
> SELECT
> CustNum
> , 'A04'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C044'
> ORDER BY
> CustNum
> , ColumnName
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D0
6-47B6-A2E3-513135CE387D@.microsoft.com...|||I have made some comments below.
On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>I have two tables; Table1 and Table2 with some sample data:
>Table1
>CustNum
>C041
>C042
>C043
>C044
>Table2
>A01 A02 A03 A04
>100.00 120.00 150.00 160.00
>200.00 210.00 250.00 260.00
>300.00 310.00 320.00 350.00
>150.00 160.00 170.00 180.00
These are ALL the rows? No key? Even denormalized tables need a key.
>My results should look like this out of above two tables. You will see that
>column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName
as
>data against CustName (Table1) and values (Table2).
>CustName ColumnName Values
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
It appears that, somehow, a specific CustNum from Table1 (the "first")
is supposed to match up with the "first" row in Table2'
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
Same again, but the "second"?
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
etc.
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
etc.
>Your assistance will be appreciated.
>Thanks...Fraz
I see no way to produce the results you requested from the tables
given. Without CustNum on Table2 there is no way to match the two
tables up the way your sample output specifies. Relational tables
have no order, there is no "first", "next" or "last", so there can be
no joining on such a basis.
Please take the time to learn about data normalization. However long
it takes to learn - and it is really quite simple - compared to the
time you will waste trying to work with things like this it will pay
off easily.
Roy Harvey
Beacon Falls, CT|||My apologies. There are PK and FK keys. Between Table1 and Table2 there is
one-to-one relationship. Each table has 50-60 columns but I will precisely
re-append the tables:
Table1
Id (PK) CustNum
100 C041
101 C042
102 C043
105 C044
Table2
ID (FK) A01 A02 A03 A04
100 100.00 120.00 150.00 160.00
101 200.00 210.00 250.00 260.00
102 300.00 310.00 320.00 350.00
105 150.00 160.00 170.00 180.00
Results required by user in the output file as follows:
CustName ColumnName Value
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
You will notice that values in ColumnName is actually column names in Table2
.
Thanks...Fraz
"Roy Harvey" wrote:
> I have made some comments below.
> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
>
> These are ALL the rows? No key? Even denormalized tables need a key.
>
> It appears that, somehow, a specific CustNum from Table1 (the "first")
> is supposed to match up with the "first" row in Table2'
>
> Same again, but the "second"?
>
> etc.
>
> etc.
>
> I see no way to produce the results you requested from the tables
> given. Without CustNum on Table2 there is no way to match the two
> tables up the way your sample output specifies. Relational tables
> have no order, there is no "first", "next" or "last", so there can be
> no joining on such a basis.
> Please take the time to learn about data normalization. However long
> it takes to learn - and it is really quite simple - compared to the
> time you will waste trying to work with things like this it will pay
> off easily.
> Roy Harvey
> Beacon Falls, CT
>|||Untested, but this should do it.
SELECT CustNum,
ColumName =
CASE N.I
WHEN 1 THEN 'A01'
WHEN 2 THEN 'A02'
WHEN 3 THEN 'A03'
WHEN 4 THEN 'A04'
END,
Value =
CASE N.I
WHEN 1 THEN A01
WHEN 2 THEN A02
WHEN 3 THEN A03
WHEN 4 THEN A04
END
FROM Table1 as A
JOIN Table2 as B
ON A.Id = B.Id
CROSS JOIN
(select 1 as I UNION ALL
select 2 as I UNION ALL
select 3 as I UNION ALL
select 4 as I) as N
Roy Harvey
Beacon Falls, CT
On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>My apologies. There are PK and FK keys. Between Table1 and Table2 there is
>one-to-one relationship. Each table has 50-60 columns but I will precisely
>re-append the tables:
>Table1
>Id (PK) CustNum
>100 C041
>101 C042
>102 C043
>105 C044
>Table2
>ID (FK) A01 A02 A03 A04
>100 100.00 120.00 150.00 160.00
>101 200.00 210.00 250.00 260.00
>102 300.00 310.00 320.00 350.00
>105 150.00 160.00 170.00 180.00
>Results required by user in the output file as follows:
>CustName ColumnName Value
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
>You will notice that values in ColumnName is actually column names in Table
2.
>Thanks...Fraz
>
>"Roy Harvey" wrote:
>|||It looks promising. I will use this concept to add other tables. Thanks a lo
t
Roy and Arnie. Regards, Fraz.
"Roy Harvey" wrote:
> Untested, but this should do it.
> SELECT CustNum,
> ColumName =
> CASE N.I
> WHEN 1 THEN 'A01'
> WHEN 2 THEN 'A02'
> WHEN 3 THEN 'A03'
> WHEN 4 THEN 'A04'
> END,
> Value =
> CASE N.I
> WHEN 1 THEN A01
> WHEN 2 THEN A02
> WHEN 3 THEN A03
> WHEN 4 THEN A04
> END
> FROM Table1 as A
> JOIN Table2 as B
> ON A.Id = B.Id
> CROSS JOIN
> (select 1 as I UNION ALL
> select 2 as I UNION ALL
> select 3 as I UNION ALL
> select 4 as I) as N
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
>
>|||Hi,
Try this querry
SELECT a.custname,b.col,b.val FROM table a,
(
SELECT 'A01' as 'col', a01 as 'val' from table2
UNION ALL
SELECT 'A02' as 'col' , a02 as 'val' from table2
UNION ALL
SELECT 'A03' as 'col' , a03 as 'val' from table2
UNION ALL
SELECT 'A04' as 'col' , a04 as 'val' from table2
) b
ORDER BY a.custname
Amol Lembhe
"Fraz" wrote:
[vbcol=seagreen]
> It looks promising. I will use this concept to add other tables. Thanks a
lot
> Roy and Arnie. Regards, Fraz.
> "Roy Harvey" wrote:
>|||Thanks Amol. Fraz
"Amol Lembhe" wrote:
[vbcol=seagreen]
> Hi,
> Try this querry
> SELECT a.custname,b.col,b.val FROM table a,
> (
> SELECT 'A01' as 'col', a01 as 'val' from table2
> UNION ALL
> SELECT 'A02' as 'col' , a02 as 'val' from table2
> UNION ALL
> SELECT 'A03' as 'col' , a03 as 'val' from table2
> UNION ALL
> SELECT 'A04' as 'col' , a04 as 'val' from table2
> ) b
> ORDER BY a.custname
> Amol Lembhe
>
> "Fraz" wrote:
>
Help for Query
I have two tables; Table1 and Table2 with some sample data:
Table1
CustNum
C041
C042
C043
C044
Table2
A01 A02 A03 A04
100.00 120.00 150.00 160.00
200.00 210.00 250.00 260.00
300.00 310.00 320.00 350.00
150.00 160.00 170.00 180.00
My results should look like this out of above two tables. You will see that
column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
data against CustName (Table1) and values (Table2).
CustName ColumnName Values
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
Your assistance will be appreciated.
Thanks...FrazThis is a multi-part message in MIME format.
--=_NextPart_000_05FB_01C6A4B5.80E9A730
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It would be so much easier if your data were Normalized. And you don't =have any form of a PK-FK relationship between the tables.
Based upon this use, Table2 should really be CustNum, ColumnName, =ColValue. Then a simple JOIN could be used. You might find this article =useful: http://www.datamodel.org/NormalizationRules.html
The following works to provide you the solution you seek -but it is a =bit awkward and will be difficult to scale.
CREATE TABLE Table1
( CustNum varchar(10) )
GO
INSERT INTO Table1 VALUES ( 'C041' )
INSERT INTO Table1 VALUES ( 'C042' )
INSERT INTO Table1 VALUES ( 'C043' )
INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE Table2
( A01 decimal
, A02 decimal
, A03 decimal
, A04 decimal
)
GO
INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT CustNum
, 'A01' AS 'ColumnName'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C041'
UNION
SELECT CustNum
, 'A02'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C042'
UNION
SELECT CustNum
, 'A03'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C043'
UNION
SELECT CustNum
, 'A04'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C044'
ORDER BY CustNum
, ColumnName
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message =news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...
>I have two tables; Table1 and Table2 with some sample data: > > Table1
> CustNum
> C041 > C042
> C043
> C044
> > Table2
> A01 A02 A03 A04
> 100.00 120.00 150.00 160.00 > 200.00 210.00 250.00 260.00
> 300.00 310.00 320.00 350.00
> 150.00 160.00 170.00 180.00
> > My results should look like this out of above two tables. You will see =that > column name A01, A02, A03 and A04 from Table2 has been shown in =ColumnName as > data against CustName (Table1) and values (Table2). > > CustName ColumnName Values
> C041 A01 100.00
> C041 A02 120.00
> C041 A03 150.00
> C041 A04 160.00
> C042 A01 200.00
> C042 A02 210.00
> C042 A03 250.00
> C042 A04 260.00
> C043 A01 300.00
> C043 A02 310.00
> C043 A03 320.00
> C043 A04 350.00
> C044 A01 150.00
> C044 A02 160.00
> C044 A03 170.00
> C044 A04 180.00
> > Your assistance will be appreciated.
> Thanks...Fraz
--=_NextPart_000_05FB_01C6A4B5.80E9A730
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It would be so much easier if your data =were Normalized. And you don't have any form of a PK-FK relationship between =the tables.
Based upon this use, Table2 should =really be CustNum, ColumnName, ColValue. Then a simple JOIN could be used. You =might find this article useful:
The following works to provide you =the solution you seek -but it is a bit awkward and will be difficult to scale.
CREATE TABLE =Table1 ( CustNum varchar(10) )GO
INSERT INTO Table1 VALUES ( ='C041' )INSERT INTO Table1 VALUES ( 'C042' )INSERT INTO Table1 VALUES ( ='C043' )INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE =Table2 ( A01 decimal =, A02 decimal , A03 decimal , A04 =decimal )GO
INSERT INTO Table2 VALUES ( =100.00, 120.00, 150.00, 160.00 )INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, =260.00 )INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 =)INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT CustNum , 'A01' AS ='ColumnName' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum ==3D 'C041'
UNION
SELECT CustNum , 'A02' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C042'
UNION
SELECT CustNum , 'A03' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C043'
UNION
SELECT CustNum , 'A04' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C044'
ORDER BY CustNum , ColumnName
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Fraz" wrote in message news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...>I =have two tables; Table1 and Table2 with some sample data: > > =Table1> CustNum> C041 > C042> C043> C044> => Table2> A01 A02 A03 A04> 100.00 120.00 150.00 =160.00 > 200.00 210.00 250.00 260.00> 300.00 310.00 =320.00 350.00> 150.00 160.00 =170.00 180.00> > My results should look like this out of above =two tables. You will see that > column name A01, A02, A03 and A04 =from Table2 has been shown in ColumnName as > data against CustName (Table1) =and values (Table2). > > CustName ColumnName =Values> =C041 A01 &nbs=p; 100.00> =C041 A02 &nbs=p; 120.00> =C041 A03 &nbs=p; 150.00> =C041 A04 &nbs=p; 160.00> =C042 A01 &nbs=p; 200.00> =C042 A02 &nbs=p; 210.00> =C042 A03 &nbs=p; 250.00> =C042 A04 &nbs=p; 260.00> =C043 A01 &nbs=p; 300.00> =C043 A02 &nbs=p; 310.00> =C043 A03 &nbs=p; 320.00> =C043 A04 &nbs=p; 350.00> =C044 A01 &nbs=p; 150.00> =C044 A02 &nbs=p; 160.00> =C044 A03 &nbs=p; 170.00> =C044 A04 &nbs=p; 180.00> > Your assistance will be appreciated.> Thanks...Fraz
--=_NextPart_000_05FB_01C6A4B5.80E9A730--|||On Tue, 11 Jul 2006 06:44:48 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>It would be so much easier if your data were Normalized. And you don't have any form of a PK-FK relationship between the tables.
>Based upon this use, Table2 should really be CustNum, ColumnName,
>ColValue. Then a simple JOIN could be used. You might find this
>article useful: http://www.datamodel.org/NormalizationRules.html
Arnie, I think it is even worse than you described. As I read the
original query, the result set assumes that the two tables are matched
up first-row-to-first, second-row-to-second, etc. Even your query
can't fix THAT problem.
Roy|||I appreciate your feedback, Arnie. We receive the data file from external
source.
The CustNum in Table1 has about 1000 records. Table2 also has 17000 records.
If the sample works well then I wil join other tables which are identical to
Table2 and columnname like B01, B02, C01, C02 etc.
I am wondering if there is an easier way because using a UNION for each
CustNum will make the script too big. Thanks again. Please free to provide
your feedback.
Thanks...Fraz
"Arnie Rowland" wrote:
> It would be so much easier if your data were Normalized. And you don't have any form of a PK-FK relationship between the tables.
> Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a simple JOIN could be used. You might find this article useful: http://www.datamodel.org/NormalizationRules.html
> The following works to provide you the solution you seek -but it is a bit awkward and will be difficult to scale.
> CREATE TABLE Table1
> ( CustNum varchar(10) )
> GO
> INSERT INTO Table1 VALUES ( 'C041' )
> INSERT INTO Table1 VALUES ( 'C042' )
> INSERT INTO Table1 VALUES ( 'C043' )
> INSERT INTO Table1 VALUES ( 'C044' )
> CREATE TABLE Table2
> ( A01 decimal
> , A02 decimal
> , A03 decimal
> , A04 decimal
> )
> GO
> INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
> INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
> INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
> INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
>
> SELECT
> CustNum
> , 'A01' AS 'ColumnName'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C041'
> UNION
> SELECT
> CustNum
> , 'A02'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C042'
> UNION
> SELECT
> CustNum
> , 'A03'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C043'
> UNION
> SELECT
> CustNum
> , 'A04'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C044'
> ORDER BY
> CustNum
> , ColumnName
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...
> >I have two tables; Table1 and Table2 with some sample data:
> >
> > Table1
> > CustNum
> > C041
> > C042
> > C043
> > C044
> >
> > Table2
> > A01 A02 A03 A04
> > 100.00 120.00 150.00 160.00
> > 200.00 210.00 250.00 260.00
> > 300.00 310.00 320.00 350.00
> > 150.00 160.00 170.00 180.00
> >
> > My results should look like this out of above two tables. You will see that
> > column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > data against CustName (Table1) and values (Table2).
> >
> > CustName ColumnName Values
> > C041 A01 100.00
> > C041 A02 120.00
> > C041 A03 150.00
> > C041 A04 160.00
> > C042 A01 200.00
> > C042 A02 210.00
> > C042 A03 250.00
> > C042 A04 260.00
> > C043 A01 300.00
> > C043 A02 310.00
> > C043 A03 320.00
> > C043 A04 350.00
> > C044 A01 150.00
> > C044 A02 160.00
> > C044 A03 170.00
> > C044 A04 180.00
> >
> > Your assistance will be appreciated.
> > Thanks..|||I have made some comments below.
On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>I have two tables; Table1 and Table2 with some sample data:
>Table1
>CustNum
>C041
>C042
>C043
>C044
>Table2
>A01 A02 A03 A04
>100.00 120.00 150.00 160.00
>200.00 210.00 250.00 260.00
>300.00 310.00 320.00 350.00
>150.00 160.00 170.00 180.00
These are ALL the rows? No key? Even denormalized tables need a key.
>My results should look like this out of above two tables. You will see that
>column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
>data against CustName (Table1) and values (Table2).
>CustName ColumnName Values
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
It appears that, somehow, a specific CustNum from Table1 (the "first")
is supposed to match up with the "first" row in Table2'
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
Same again, but the "second"?
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
etc.
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
etc.
>Your assistance will be appreciated.
>Thanks...Fraz
I see no way to produce the results you requested from the tables
given. Without CustNum on Table2 there is no way to match the two
tables up the way your sample output specifies. Relational tables
have no order, there is no "first", "next" or "last", so there can be
no joining on such a basis.
Please take the time to learn about data normalization. However long
it takes to learn - and it is really quite simple - compared to the
time you will waste trying to work with things like this it will pay
off easily.
Roy Harvey
Beacon Falls, CT|||My apologies. There are PK and FK keys. Between Table1 and Table2 there is
one-to-one relationship. Each table has 50-60 columns but I will precisely
re-append the tables:
Table1
Id (PK) CustNum
100 C041
101 C042
102 C043
105 C044
Table2
ID (FK) A01 A02 A03 A04
100 100.00 120.00 150.00 160.00
101 200.00 210.00 250.00 260.00
102 300.00 310.00 320.00 350.00
105 150.00 160.00 170.00 180.00
Results required by user in the output file as follows:
CustName ColumnName Value
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
You will notice that values in ColumnName is actually column names in Table2.
Thanks...Fraz
"Roy Harvey" wrote:
> I have made some comments below.
> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
> >I have two tables; Table1 and Table2 with some sample data:
> >
> >Table1
> >CustNum
> >C041
> >C042
> >C043
> >C044
> >
> >Table2
> >A01 A02 A03 A04
> >100.00 120.00 150.00 160.00
> >200.00 210.00 250.00 260.00
> >300.00 310.00 320.00 350.00
> >150.00 160.00 170.00 180.00
> These are ALL the rows? No key? Even denormalized tables need a key.
> >My results should look like this out of above two tables. You will see that
> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> >data against CustName (Table1) and values (Table2).
> >
> >CustName ColumnName Values
> >C041 A01 100.00
> >C041 A02 120.00
> >C041 A03 150.00
> >C041 A04 160.00
> It appears that, somehow, a specific CustNum from Table1 (the "first")
> is supposed to match up with the "first" row in Table2'
> >C042 A01 200.00
> >C042 A02 210.00
> >C042 A03 250.00
> >C042 A04 260.00
> Same again, but the "second"?
> >C043 A01 300.00
> >C043 A02 310.00
> >C043 A03 320.00
> >C043 A04 350.00
> etc.
> >C044 A01 150.00
> >C044 A02 160.00
> >C044 A03 170.00
> >C044 A04 180.00
> etc.
> >
> >Your assistance will be appreciated.
> >Thanks...Fraz
> I see no way to produce the results you requested from the tables
> given. Without CustNum on Table2 there is no way to match the two
> tables up the way your sample output specifies. Relational tables
> have no order, there is no "first", "next" or "last", so there can be
> no joining on such a basis.
> Please take the time to learn about data normalization. However long
> it takes to learn - and it is really quite simple - compared to the
> time you will waste trying to work with things like this it will pay
> off easily.
> Roy Harvey
> Beacon Falls, CT
>|||Untested, but this should do it.
SELECT CustNum,
ColumName = CASE N.I
WHEN 1 THEN 'A01'
WHEN 2 THEN 'A02'
WHEN 3 THEN 'A03'
WHEN 4 THEN 'A04'
END,
Value = CASE N.I
WHEN 1 THEN A01
WHEN 2 THEN A02
WHEN 3 THEN A03
WHEN 4 THEN A04
END
FROM Table1 as A
JOIN Table2 as B
ON A.Id = B.Id
CROSS JOIN
(select 1 as I UNION ALL
select 2 as I UNION ALL
select 3 as I UNION ALL
select 4 as I) as N
Roy Harvey
Beacon Falls, CT
On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>My apologies. There are PK and FK keys. Between Table1 and Table2 there is
>one-to-one relationship. Each table has 50-60 columns but I will precisely
>re-append the tables:
>Table1
>Id (PK) CustNum
>100 C041
>101 C042
>102 C043
>105 C044
>Table2
>ID (FK) A01 A02 A03 A04
>100 100.00 120.00 150.00 160.00
>101 200.00 210.00 250.00 260.00
>102 300.00 310.00 320.00 350.00
>105 150.00 160.00 170.00 180.00
>Results required by user in the output file as follows:
>CustName ColumnName Value
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
>You will notice that values in ColumnName is actually column names in Table2.
>Thanks...Fraz
>
>"Roy Harvey" wrote:
>> I have made some comments below.
>> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
>> <Fraz@.discussions.microsoft.com> wrote:
>> >I have two tables; Table1 and Table2 with some sample data:
>> >
>> >Table1
>> >CustNum
>> >C041
>> >C042
>> >C043
>> >C044
>> >
>> >Table2
>> >A01 A02 A03 A04
>> >100.00 120.00 150.00 160.00
>> >200.00 210.00 250.00 260.00
>> >300.00 310.00 320.00 350.00
>> >150.00 160.00 170.00 180.00
>> These are ALL the rows? No key? Even denormalized tables need a key.
>> >My results should look like this out of above two tables. You will see that
>> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
>> >data against CustName (Table1) and values (Table2).
>> >
>> >CustName ColumnName Values
>> >C041 A01 100.00
>> >C041 A02 120.00
>> >C041 A03 150.00
>> >C041 A04 160.00
>> It appears that, somehow, a specific CustNum from Table1 (the "first")
>> is supposed to match up with the "first" row in Table2'
>> >C042 A01 200.00
>> >C042 A02 210.00
>> >C042 A03 250.00
>> >C042 A04 260.00
>> Same again, but the "second"?
>> >C043 A01 300.00
>> >C043 A02 310.00
>> >C043 A03 320.00
>> >C043 A04 350.00
>> etc.
>> >C044 A01 150.00
>> >C044 A02 160.00
>> >C044 A03 170.00
>> >C044 A04 180.00
>> etc.
>> >
>> >Your assistance will be appreciated.
>> >Thanks...Fraz
>> I see no way to produce the results you requested from the tables
>> given. Without CustNum on Table2 there is no way to match the two
>> tables up the way your sample output specifies. Relational tables
>> have no order, there is no "first", "next" or "last", so there can be
>> no joining on such a basis.
>> Please take the time to learn about data normalization. However long
>> it takes to learn - and it is really quite simple - compared to the
>> time you will waste trying to work with things like this it will pay
>> off easily.
>> Roy Harvey
>> Beacon Falls, CT|||It looks promising. I will use this concept to add other tables. Thanks a lot
Roy and Arnie. Regards, Fraz.
"Roy Harvey" wrote:
> Untested, but this should do it.
> SELECT CustNum,
> ColumName => CASE N.I
> WHEN 1 THEN 'A01'
> WHEN 2 THEN 'A02'
> WHEN 3 THEN 'A03'
> WHEN 4 THEN 'A04'
> END,
> Value => CASE N.I
> WHEN 1 THEN A01
> WHEN 2 THEN A02
> WHEN 3 THEN A03
> WHEN 4 THEN A04
> END
> FROM Table1 as A
> JOIN Table2 as B
> ON A.Id = B.Id
> CROSS JOIN
> (select 1 as I UNION ALL
> select 2 as I UNION ALL
> select 3 as I UNION ALL
> select 4 as I) as N
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
> >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> >one-to-one relationship. Each table has 50-60 columns but I will precisely
> >re-append the tables:
> >Table1
> >Id (PK) CustNum
> >100 C041
> >101 C042
> >102 C043
> >105 C044
> >
> >Table2
> >ID (FK) A01 A02 A03 A04
> >100 100.00 120.00 150.00 160.00
> >101 200.00 210.00 250.00 260.00
> >102 300.00 310.00 320.00 350.00
> >105 150.00 160.00 170.00 180.00
> >
> >Results required by user in the output file as follows:
> >CustName ColumnName Value
> >C041 A01 100.00
> >C041 A02 120.00
> >C041 A03 150.00
> >C041 A04 160.00
> >C042 A01 200.00
> >C042 A02 210.00
> >C042 A03 250.00
> >C042 A04 260.00
> >C043 A01 300.00
> >C043 A02 310.00
> >C043 A03 320.00
> >C043 A04 350.00
> >C044 A01 150.00
> >C044 A02 160.00
> >C044 A03 170.00
> >C044 A04 180.00
> >
> >You will notice that values in ColumnName is actually column names in Table2.
> >Thanks...Fraz
> >
> >
> >"Roy Harvey" wrote:
> >
> >> I have made some comments below.
> >>
> >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> >> <Fraz@.discussions.microsoft.com> wrote:
> >>
> >> >I have two tables; Table1 and Table2 with some sample data:
> >> >
> >> >Table1
> >> >CustNum
> >> >C041
> >> >C042
> >> >C043
> >> >C044
> >> >
> >> >Table2
> >> >A01 A02 A03 A04
> >> >100.00 120.00 150.00 160.00
> >> >200.00 210.00 250.00 260.00
> >> >300.00 310.00 320.00 350.00
> >> >150.00 160.00 170.00 180.00
> >>
> >> These are ALL the rows? No key? Even denormalized tables need a key.
> >>
> >> >My results should look like this out of above two tables. You will see that
> >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> >> >data against CustName (Table1) and values (Table2).
> >> >
> >> >CustName ColumnName Values
> >> >C041 A01 100.00
> >> >C041 A02 120.00
> >> >C041 A03 150.00
> >> >C041 A04 160.00
> >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> >> is supposed to match up with the "first" row in Table2'
> >>
> >> >C042 A01 200.00
> >> >C042 A02 210.00
> >> >C042 A03 250.00
> >> >C042 A04 260.00
> >> Same again, but the "second"?
> >>
> >> >C043 A01 300.00
> >> >C043 A02 310.00
> >> >C043 A03 320.00
> >> >C043 A04 350.00
> >> etc.
> >>
> >> >C044 A01 150.00
> >> >C044 A02 160.00
> >> >C044 A03 170.00
> >> >C044 A04 180.00
> >> etc.
> >>
> >> >
> >> >Your assistance will be appreciated.
> >> >Thanks...Fraz
> >>
> >> I see no way to produce the results you requested from the tables
> >> given. Without CustNum on Table2 there is no way to match the two
> >> tables up the way your sample output specifies. Relational tables
> >> have no order, there is no "first", "next" or "last", so there can be
> >> no joining on such a basis.
> >>
> >> Please take the time to learn about data normalization. However long
> >> it takes to learn - and it is really quite simple - compared to the
> >> time you will waste trying to work with things like this it will pay
> >> off easily.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>|||Hi,
Try this querry
SELECT a.custname,b.col,b.val FROM table a,
(
SELECT 'A01' as 'col', a01 as 'val' from table2
UNION ALL
SELECT 'A02' as 'col' , a02 as 'val' from table2
UNION ALL
SELECT 'A03' as 'col' , a03 as 'val' from table2
UNION ALL
SELECT 'A04' as 'col' , a04 as 'val' from table2
) b
ORDER BY a.custname
Amol Lembhe
"Fraz" wrote:
> It looks promising. I will use this concept to add other tables. Thanks a lot
> Roy and Arnie. Regards, Fraz.
> "Roy Harvey" wrote:
> > Untested, but this should do it.
> >
> > SELECT CustNum,
> > ColumName => > CASE N.I
> > WHEN 1 THEN 'A01'
> > WHEN 2 THEN 'A02'
> > WHEN 3 THEN 'A03'
> > WHEN 4 THEN 'A04'
> > END,
> > Value => > CASE N.I
> > WHEN 1 THEN A01
> > WHEN 2 THEN A02
> > WHEN 3 THEN A03
> > WHEN 4 THEN A04
> > END
> > FROM Table1 as A
> > JOIN Table2 as B
> > ON A.Id = B.Id
> > CROSS JOIN
> > (select 1 as I UNION ALL
> > select 2 as I UNION ALL
> > select 3 as I UNION ALL
> > select 4 as I) as N
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > <Fraz@.discussions.microsoft.com> wrote:
> >
> > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > >re-append the tables:
> > >Table1
> > >Id (PK) CustNum
> > >100 C041
> > >101 C042
> > >102 C043
> > >105 C044
> > >
> > >Table2
> > >ID (FK) A01 A02 A03 A04
> > >100 100.00 120.00 150.00 160.00
> > >101 200.00 210.00 250.00 260.00
> > >102 300.00 310.00 320.00 350.00
> > >105 150.00 160.00 170.00 180.00
> > >
> > >Results required by user in the output file as follows:
> > >CustName ColumnName Value
> > >C041 A01 100.00
> > >C041 A02 120.00
> > >C041 A03 150.00
> > >C041 A04 160.00
> > >C042 A01 200.00
> > >C042 A02 210.00
> > >C042 A03 250.00
> > >C042 A04 260.00
> > >C043 A01 300.00
> > >C043 A02 310.00
> > >C043 A03 320.00
> > >C043 A04 350.00
> > >C044 A01 150.00
> > >C044 A02 160.00
> > >C044 A03 170.00
> > >C044 A04 180.00
> > >
> > >You will notice that values in ColumnName is actually column names in Table2.
> > >Thanks...Fraz
> > >
> > >
> > >"Roy Harvey" wrote:
> > >
> > >> I have made some comments below.
> > >>
> > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > >> <Fraz@.discussions.microsoft.com> wrote:
> > >>
> > >> >I have two tables; Table1 and Table2 with some sample data:
> > >> >
> > >> >Table1
> > >> >CustNum
> > >> >C041
> > >> >C042
> > >> >C043
> > >> >C044
> > >> >
> > >> >Table2
> > >> >A01 A02 A03 A04
> > >> >100.00 120.00 150.00 160.00
> > >> >200.00 210.00 250.00 260.00
> > >> >300.00 310.00 320.00 350.00
> > >> >150.00 160.00 170.00 180.00
> > >>
> > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > >>
> > >> >My results should look like this out of above two tables. You will see that
> > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > >> >data against CustName (Table1) and values (Table2).
> > >> >
> > >> >CustName ColumnName Values
> > >> >C041 A01 100.00
> > >> >C041 A02 120.00
> > >> >C041 A03 150.00
> > >> >C041 A04 160.00
> > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > >> is supposed to match up with the "first" row in Table2'
> > >>
> > >> >C042 A01 200.00
> > >> >C042 A02 210.00
> > >> >C042 A03 250.00
> > >> >C042 A04 260.00
> > >> Same again, but the "second"?
> > >>
> > >> >C043 A01 300.00
> > >> >C043 A02 310.00
> > >> >C043 A03 320.00
> > >> >C043 A04 350.00
> > >> etc.
> > >>
> > >> >C044 A01 150.00
> > >> >C044 A02 160.00
> > >> >C044 A03 170.00
> > >> >C044 A04 180.00
> > >> etc.
> > >>
> > >> >
> > >> >Your assistance will be appreciated.
> > >> >Thanks...Fraz
> > >>
> > >> I see no way to produce the results you requested from the tables
> > >> given. Without CustNum on Table2 there is no way to match the two
> > >> tables up the way your sample output specifies. Relational tables
> > >> have no order, there is no "first", "next" or "last", so there can be
> > >> no joining on such a basis.
> > >>
> > >> Please take the time to learn about data normalization. However long
> > >> it takes to learn - and it is really quite simple - compared to the
> > >> time you will waste trying to work with things like this it will pay
> > >> off easily.
> > >>
> > >> Roy Harvey
> > >> Beacon Falls, CT
> > >>
> >|||Thanks Amol. Fraz
"Amol Lembhe" wrote:
> Hi,
> Try this querry
> SELECT a.custname,b.col,b.val FROM table a,
> (
> SELECT 'A01' as 'col', a01 as 'val' from table2
> UNION ALL
> SELECT 'A02' as 'col' , a02 as 'val' from table2
> UNION ALL
> SELECT 'A03' as 'col' , a03 as 'val' from table2
> UNION ALL
> SELECT 'A04' as 'col' , a04 as 'val' from table2
> ) b
> ORDER BY a.custname
> Amol Lembhe
>
> "Fraz" wrote:
> > It looks promising. I will use this concept to add other tables. Thanks a lot
> > Roy and Arnie. Regards, Fraz.
> >
> > "Roy Harvey" wrote:
> >
> > > Untested, but this should do it.
> > >
> > > SELECT CustNum,
> > > ColumName => > > CASE N.I
> > > WHEN 1 THEN 'A01'
> > > WHEN 2 THEN 'A02'
> > > WHEN 3 THEN 'A03'
> > > WHEN 4 THEN 'A04'
> > > END,
> > > Value => > > CASE N.I
> > > WHEN 1 THEN A01
> > > WHEN 2 THEN A02
> > > WHEN 3 THEN A03
> > > WHEN 4 THEN A04
> > > END
> > > FROM Table1 as A
> > > JOIN Table2 as B
> > > ON A.Id = B.Id
> > > CROSS JOIN
> > > (select 1 as I UNION ALL
> > > select 2 as I UNION ALL
> > > select 3 as I UNION ALL
> > > select 4 as I) as N
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > >
> > > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > > <Fraz@.discussions.microsoft.com> wrote:
> > >
> > > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > > >re-append the tables:
> > > >Table1
> > > >Id (PK) CustNum
> > > >100 C041
> > > >101 C042
> > > >102 C043
> > > >105 C044
> > > >
> > > >Table2
> > > >ID (FK) A01 A02 A03 A04
> > > >100 100.00 120.00 150.00 160.00
> > > >101 200.00 210.00 250.00 260.00
> > > >102 300.00 310.00 320.00 350.00
> > > >105 150.00 160.00 170.00 180.00
> > > >
> > > >Results required by user in the output file as follows:
> > > >CustName ColumnName Value
> > > >C041 A01 100.00
> > > >C041 A02 120.00
> > > >C041 A03 150.00
> > > >C041 A04 160.00
> > > >C042 A01 200.00
> > > >C042 A02 210.00
> > > >C042 A03 250.00
> > > >C042 A04 260.00
> > > >C043 A01 300.00
> > > >C043 A02 310.00
> > > >C043 A03 320.00
> > > >C043 A04 350.00
> > > >C044 A01 150.00
> > > >C044 A02 160.00
> > > >C044 A03 170.00
> > > >C044 A04 180.00
> > > >
> > > >You will notice that values in ColumnName is actually column names in Table2.
> > > >Thanks...Fraz
> > > >
> > > >
> > > >"Roy Harvey" wrote:
> > > >
> > > >> I have made some comments below.
> > > >>
> > > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > > >> <Fraz@.discussions.microsoft.com> wrote:
> > > >>
> > > >> >I have two tables; Table1 and Table2 with some sample data:
> > > >> >
> > > >> >Table1
> > > >> >CustNum
> > > >> >C041
> > > >> >C042
> > > >> >C043
> > > >> >C044
> > > >> >
> > > >> >Table2
> > > >> >A01 A02 A03 A04
> > > >> >100.00 120.00 150.00 160.00
> > > >> >200.00 210.00 250.00 260.00
> > > >> >300.00 310.00 320.00 350.00
> > > >> >150.00 160.00 170.00 180.00
> > > >>
> > > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > > >>
> > > >> >My results should look like this out of above two tables. You will see that
> > > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > > >> >data against CustName (Table1) and values (Table2).
> > > >> >
> > > >> >CustName ColumnName Values
> > > >> >C041 A01 100.00
> > > >> >C041 A02 120.00
> > > >> >C041 A03 150.00
> > > >> >C041 A04 160.00
> > > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > > >> is supposed to match up with the "first" row in Table2'
> > > >>
> > > >> >C042 A01 200.00
> > > >> >C042 A02 210.00
> > > >> >C042 A03 250.00
> > > >> >C042 A04 260.00
> > > >> Same again, but the "second"?
> > > >>
> > > >> >C043 A01 300.00
> > > >> >C043 A02 310.00
> > > >> >C043 A03 320.00
> > > >> >C043 A04 350.00
> > > >> etc.
> > > >>
> > > >> >C044 A01 150.00
> > > >> >C044 A02 160.00
> > > >> >C044 A03 170.00
> > > >> >C044 A04 180.00
> > > >> etc.
> > > >>
> > > >> >
> > > >> >Your assistance will be appreciated.
> > > >> >Thanks...Fraz
> > > >>
> > > >> I see no way to produce the results you requested from the tables
> > > >> given. Without CustNum on Table2 there is no way to match the two
> > > >> tables up the way your sample output specifies. Relational tables
> > > >> have no order, there is no "first", "next" or "last", so there can be
> > > >> no joining on such a basis.
> > > >>
> > > >> Please take the time to learn about data normalization. However long
> > > >> it takes to learn - and it is really quite simple - compared to the
> > > >> time you will waste trying to work with things like this it will pay
> > > >> off easily.
> > > >>
> > > >> Roy Harvey
> > > >> Beacon Falls, CT
> > > >>
> > >|||Hi Roy and Amol: Just to thank you for your valuable feedback. I got the
results I wanted. Because there were many tables with 50-100 columns, I found
the Case Statement and CROSS JOIN by Roy worked out well for my needs. Thanks
again. Fraz
"Fraz" wrote:
> Thanks Amol. Fraz
> "Amol Lembhe" wrote:
> > Hi,
> > Try this querry
> > SELECT a.custname,b.col,b.val FROM table a,
> > (
> > SELECT 'A01' as 'col', a01 as 'val' from table2
> > UNION ALL
> > SELECT 'A02' as 'col' , a02 as 'val' from table2
> > UNION ALL
> > SELECT 'A03' as 'col' , a03 as 'val' from table2
> > UNION ALL
> > SELECT 'A04' as 'col' , a04 as 'val' from table2
> > ) b
> > ORDER BY a.custname
> >
> > Amol Lembhe
> >
> >
> >
> > "Fraz" wrote:
> >
> > > It looks promising. I will use this concept to add other tables. Thanks a lot
> > > Roy and Arnie. Regards, Fraz.
> > >
> > > "Roy Harvey" wrote:
> > >
> > > > Untested, but this should do it.
> > > >
> > > > SELECT CustNum,
> > > > ColumName => > > > CASE N.I
> > > > WHEN 1 THEN 'A01'
> > > > WHEN 2 THEN 'A02'
> > > > WHEN 3 THEN 'A03'
> > > > WHEN 4 THEN 'A04'
> > > > END,
> > > > Value => > > > CASE N.I
> > > > WHEN 1 THEN A01
> > > > WHEN 2 THEN A02
> > > > WHEN 3 THEN A03
> > > > WHEN 4 THEN A04
> > > > END
> > > > FROM Table1 as A
> > > > JOIN Table2 as B
> > > > ON A.Id = B.Id
> > > > CROSS JOIN
> > > > (select 1 as I UNION ALL
> > > > select 2 as I UNION ALL
> > > > select 3 as I UNION ALL
> > > > select 4 as I) as N
> > > >
> > > > Roy Harvey
> > > > Beacon Falls, CT
> > > >
> > > >
> > > > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > > > <Fraz@.discussions.microsoft.com> wrote:
> > > >
> > > > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > > > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > > > >re-append the tables:
> > > > >Table1
> > > > >Id (PK) CustNum
> > > > >100 C041
> > > > >101 C042
> > > > >102 C043
> > > > >105 C044
> > > > >
> > > > >Table2
> > > > >ID (FK) A01 A02 A03 A04
> > > > >100 100.00 120.00 150.00 160.00
> > > > >101 200.00 210.00 250.00 260.00
> > > > >102 300.00 310.00 320.00 350.00
> > > > >105 150.00 160.00 170.00 180.00
> > > > >
> > > > >Results required by user in the output file as follows:
> > > > >CustName ColumnName Value
> > > > >C041 A01 100.00
> > > > >C041 A02 120.00
> > > > >C041 A03 150.00
> > > > >C041 A04 160.00
> > > > >C042 A01 200.00
> > > > >C042 A02 210.00
> > > > >C042 A03 250.00
> > > > >C042 A04 260.00
> > > > >C043 A01 300.00
> > > > >C043 A02 310.00
> > > > >C043 A03 320.00
> > > > >C043 A04 350.00
> > > > >C044 A01 150.00
> > > > >C044 A02 160.00
> > > > >C044 A03 170.00
> > > > >C044 A04 180.00
> > > > >
> > > > >You will notice that values in ColumnName is actually column names in Table2.
> > > > >Thanks...Fraz
> > > > >
> > > > >
> > > > >"Roy Harvey" wrote:
> > > > >
> > > > >> I have made some comments below.
> > > > >>
> > > > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > > > >> <Fraz@.discussions.microsoft.com> wrote:
> > > > >>
> > > > >> >I have two tables; Table1 and Table2 with some sample data:
> > > > >> >
> > > > >> >Table1
> > > > >> >CustNum
> > > > >> >C041
> > > > >> >C042
> > > > >> >C043
> > > > >> >C044
> > > > >> >
> > > > >> >Table2
> > > > >> >A01 A02 A03 A04
> > > > >> >100.00 120.00 150.00 160.00
> > > > >> >200.00 210.00 250.00 260.00
> > > > >> >300.00 310.00 320.00 350.00
> > > > >> >150.00 160.00 170.00 180.00
> > > > >>
> > > > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > > > >>
> > > > >> >My results should look like this out of above two tables. You will see that
> > > > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > > > >> >data against CustName (Table1) and values (Table2).
> > > > >> >
> > > > >> >CustName ColumnName Values
> > > > >> >C041 A01 100.00
> > > > >> >C041 A02 120.00
> > > > >> >C041 A03 150.00
> > > > >> >C041 A04 160.00
> > > > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > > > >> is supposed to match up with the "first" row in Table2'
> > > > >>
> > > > >> >C042 A01 200.00
> > > > >> >C042 A02 210.00
> > > > >> >C042 A03 250.00
> > > > >> >C042 A04 260.00
> > > > >> Same again, but the "second"?
> > > > >>
> > > > >> >C043 A01 300.00
> > > > >> >C043 A02 310.00
> > > > >> >C043 A03 320.00
> > > > >> >C043 A04 350.00
> > > > >> etc.
> > > > >>
> > > > >> >C044 A01 150.00
> > > > >> >C044 A02 160.00
> > > > >> >C044 A03 170.00
> > > > >> >C044 A04 180.00
> > > > >> etc.
> > > > >>
> > > > >> >
> > > > >> >Your assistance will be appreciated.
> > > > >> >Thanks...Fraz
> > > > >>
> > > > >> I see no way to produce the results you requested from the tables
> > > > >> given. Without CustNum on Table2 there is no way to match the two
> > > > >> tables up the way your sample output specifies. Relational tables
> > > > >> have no order, there is no "first", "next" or "last", so there can be
> > > > >> no joining on such a basis.
> > > > >>
> > > > >> Please take the time to learn about data normalization. However long
> > > > >> it takes to learn - and it is really quite simple - compared to the
> > > > >> time you will waste trying to work with things like this it will pay
> > > > >> off easily.
> > > > >>
> > > > >> Roy Harvey
> > > > >> Beacon Falls, CT
> > > > >>
> > > >
Table1
CustNum
C041
C042
C043
C044
Table2
A01 A02 A03 A04
100.00 120.00 150.00 160.00
200.00 210.00 250.00 260.00
300.00 310.00 320.00 350.00
150.00 160.00 170.00 180.00
My results should look like this out of above two tables. You will see that
column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
data against CustName (Table1) and values (Table2).
CustName ColumnName Values
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
Your assistance will be appreciated.
Thanks...FrazThis is a multi-part message in MIME format.
--=_NextPart_000_05FB_01C6A4B5.80E9A730
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It would be so much easier if your data were Normalized. And you don't =have any form of a PK-FK relationship between the tables.
Based upon this use, Table2 should really be CustNum, ColumnName, =ColValue. Then a simple JOIN could be used. You might find this article =useful: http://www.datamodel.org/NormalizationRules.html
The following works to provide you the solution you seek -but it is a =bit awkward and will be difficult to scale.
CREATE TABLE Table1
( CustNum varchar(10) )
GO
INSERT INTO Table1 VALUES ( 'C041' )
INSERT INTO Table1 VALUES ( 'C042' )
INSERT INTO Table1 VALUES ( 'C043' )
INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE Table2
( A01 decimal
, A02 decimal
, A03 decimal
, A04 decimal
)
GO
INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT CustNum
, 'A01' AS 'ColumnName'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C041'
UNION
SELECT CustNum
, 'A02'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C042'
UNION
SELECT CustNum
, 'A03'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C043'
UNION
SELECT CustNum
, 'A04'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C044'
ORDER BY CustNum
, ColumnName
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message =news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...
>I have two tables; Table1 and Table2 with some sample data: > > Table1
> CustNum
> C041 > C042
> C043
> C044
> > Table2
> A01 A02 A03 A04
> 100.00 120.00 150.00 160.00 > 200.00 210.00 250.00 260.00
> 300.00 310.00 320.00 350.00
> 150.00 160.00 170.00 180.00
> > My results should look like this out of above two tables. You will see =that > column name A01, A02, A03 and A04 from Table2 has been shown in =ColumnName as > data against CustName (Table1) and values (Table2). > > CustName ColumnName Values
> C041 A01 100.00
> C041 A02 120.00
> C041 A03 150.00
> C041 A04 160.00
> C042 A01 200.00
> C042 A02 210.00
> C042 A03 250.00
> C042 A04 260.00
> C043 A01 300.00
> C043 A02 310.00
> C043 A03 320.00
> C043 A04 350.00
> C044 A01 150.00
> C044 A02 160.00
> C044 A03 170.00
> C044 A04 180.00
> > Your assistance will be appreciated.
> Thanks...Fraz
--=_NextPart_000_05FB_01C6A4B5.80E9A730
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It would be so much easier if your data =were Normalized. And you don't have any form of a PK-FK relationship between =the tables.
Based upon this use, Table2 should =really be CustNum, ColumnName, ColValue. Then a simple JOIN could be used. You =might find this article useful:
The following works to provide you =the solution you seek -but it is a bit awkward and will be difficult to scale.
CREATE TABLE =Table1 ( CustNum varchar(10) )GO
INSERT INTO Table1 VALUES ( ='C041' )INSERT INTO Table1 VALUES ( 'C042' )INSERT INTO Table1 VALUES ( ='C043' )INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE =Table2 ( A01 decimal =, A02 decimal , A03 decimal , A04 =decimal )GO
INSERT INTO Table2 VALUES ( =100.00, 120.00, 150.00, 160.00 )INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, =260.00 )INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 =)INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT CustNum , 'A01' AS ='ColumnName' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum ==3D 'C041'
UNION
SELECT CustNum , 'A02' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C042'
UNION
SELECT CustNum , 'A03' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C043'
UNION
SELECT CustNum , 'A04' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C044'
ORDER BY CustNum , ColumnName
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Fraz"
--=_NextPart_000_05FB_01C6A4B5.80E9A730--|||On Tue, 11 Jul 2006 06:44:48 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>It would be so much easier if your data were Normalized. And you don't have any form of a PK-FK relationship between the tables.
>Based upon this use, Table2 should really be CustNum, ColumnName,
>ColValue. Then a simple JOIN could be used. You might find this
>article useful: http://www.datamodel.org/NormalizationRules.html
Arnie, I think it is even worse than you described. As I read the
original query, the result set assumes that the two tables are matched
up first-row-to-first, second-row-to-second, etc. Even your query
can't fix THAT problem.
Roy|||I appreciate your feedback, Arnie. We receive the data file from external
source.
The CustNum in Table1 has about 1000 records. Table2 also has 17000 records.
If the sample works well then I wil join other tables which are identical to
Table2 and columnname like B01, B02, C01, C02 etc.
I am wondering if there is an easier way because using a UNION for each
CustNum will make the script too big. Thanks again. Please free to provide
your feedback.
Thanks...Fraz
"Arnie Rowland" wrote:
> It would be so much easier if your data were Normalized. And you don't have any form of a PK-FK relationship between the tables.
> Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a simple JOIN could be used. You might find this article useful: http://www.datamodel.org/NormalizationRules.html
> The following works to provide you the solution you seek -but it is a bit awkward and will be difficult to scale.
> CREATE TABLE Table1
> ( CustNum varchar(10) )
> GO
> INSERT INTO Table1 VALUES ( 'C041' )
> INSERT INTO Table1 VALUES ( 'C042' )
> INSERT INTO Table1 VALUES ( 'C043' )
> INSERT INTO Table1 VALUES ( 'C044' )
> CREATE TABLE Table2
> ( A01 decimal
> , A02 decimal
> , A03 decimal
> , A04 decimal
> )
> GO
> INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
> INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
> INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
> INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
>
> SELECT
> CustNum
> , 'A01' AS 'ColumnName'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C041'
> UNION
> SELECT
> CustNum
> , 'A02'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C042'
> UNION
> SELECT
> CustNum
> , 'A03'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C043'
> UNION
> SELECT
> CustNum
> , 'A04'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C044'
> ORDER BY
> CustNum
> , ColumnName
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...
> >I have two tables; Table1 and Table2 with some sample data:
> >
> > Table1
> > CustNum
> > C041
> > C042
> > C043
> > C044
> >
> > Table2
> > A01 A02 A03 A04
> > 100.00 120.00 150.00 160.00
> > 200.00 210.00 250.00 260.00
> > 300.00 310.00 320.00 350.00
> > 150.00 160.00 170.00 180.00
> >
> > My results should look like this out of above two tables. You will see that
> > column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > data against CustName (Table1) and values (Table2).
> >
> > CustName ColumnName Values
> > C041 A01 100.00
> > C041 A02 120.00
> > C041 A03 150.00
> > C041 A04 160.00
> > C042 A01 200.00
> > C042 A02 210.00
> > C042 A03 250.00
> > C042 A04 260.00
> > C043 A01 300.00
> > C043 A02 310.00
> > C043 A03 320.00
> > C043 A04 350.00
> > C044 A01 150.00
> > C044 A02 160.00
> > C044 A03 170.00
> > C044 A04 180.00
> >
> > Your assistance will be appreciated.
> > Thanks..|||I have made some comments below.
On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>I have two tables; Table1 and Table2 with some sample data:
>Table1
>CustNum
>C041
>C042
>C043
>C044
>Table2
>A01 A02 A03 A04
>100.00 120.00 150.00 160.00
>200.00 210.00 250.00 260.00
>300.00 310.00 320.00 350.00
>150.00 160.00 170.00 180.00
These are ALL the rows? No key? Even denormalized tables need a key.
>My results should look like this out of above two tables. You will see that
>column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
>data against CustName (Table1) and values (Table2).
>CustName ColumnName Values
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
It appears that, somehow, a specific CustNum from Table1 (the "first")
is supposed to match up with the "first" row in Table2'
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
Same again, but the "second"?
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
etc.
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
etc.
>Your assistance will be appreciated.
>Thanks...Fraz
I see no way to produce the results you requested from the tables
given. Without CustNum on Table2 there is no way to match the two
tables up the way your sample output specifies. Relational tables
have no order, there is no "first", "next" or "last", so there can be
no joining on such a basis.
Please take the time to learn about data normalization. However long
it takes to learn - and it is really quite simple - compared to the
time you will waste trying to work with things like this it will pay
off easily.
Roy Harvey
Beacon Falls, CT|||My apologies. There are PK and FK keys. Between Table1 and Table2 there is
one-to-one relationship. Each table has 50-60 columns but I will precisely
re-append the tables:
Table1
Id (PK) CustNum
100 C041
101 C042
102 C043
105 C044
Table2
ID (FK) A01 A02 A03 A04
100 100.00 120.00 150.00 160.00
101 200.00 210.00 250.00 260.00
102 300.00 310.00 320.00 350.00
105 150.00 160.00 170.00 180.00
Results required by user in the output file as follows:
CustName ColumnName Value
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
You will notice that values in ColumnName is actually column names in Table2.
Thanks...Fraz
"Roy Harvey" wrote:
> I have made some comments below.
> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
> >I have two tables; Table1 and Table2 with some sample data:
> >
> >Table1
> >CustNum
> >C041
> >C042
> >C043
> >C044
> >
> >Table2
> >A01 A02 A03 A04
> >100.00 120.00 150.00 160.00
> >200.00 210.00 250.00 260.00
> >300.00 310.00 320.00 350.00
> >150.00 160.00 170.00 180.00
> These are ALL the rows? No key? Even denormalized tables need a key.
> >My results should look like this out of above two tables. You will see that
> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> >data against CustName (Table1) and values (Table2).
> >
> >CustName ColumnName Values
> >C041 A01 100.00
> >C041 A02 120.00
> >C041 A03 150.00
> >C041 A04 160.00
> It appears that, somehow, a specific CustNum from Table1 (the "first")
> is supposed to match up with the "first" row in Table2'
> >C042 A01 200.00
> >C042 A02 210.00
> >C042 A03 250.00
> >C042 A04 260.00
> Same again, but the "second"?
> >C043 A01 300.00
> >C043 A02 310.00
> >C043 A03 320.00
> >C043 A04 350.00
> etc.
> >C044 A01 150.00
> >C044 A02 160.00
> >C044 A03 170.00
> >C044 A04 180.00
> etc.
> >
> >Your assistance will be appreciated.
> >Thanks...Fraz
> I see no way to produce the results you requested from the tables
> given. Without CustNum on Table2 there is no way to match the two
> tables up the way your sample output specifies. Relational tables
> have no order, there is no "first", "next" or "last", so there can be
> no joining on such a basis.
> Please take the time to learn about data normalization. However long
> it takes to learn - and it is really quite simple - compared to the
> time you will waste trying to work with things like this it will pay
> off easily.
> Roy Harvey
> Beacon Falls, CT
>|||Untested, but this should do it.
SELECT CustNum,
ColumName = CASE N.I
WHEN 1 THEN 'A01'
WHEN 2 THEN 'A02'
WHEN 3 THEN 'A03'
WHEN 4 THEN 'A04'
END,
Value = CASE N.I
WHEN 1 THEN A01
WHEN 2 THEN A02
WHEN 3 THEN A03
WHEN 4 THEN A04
END
FROM Table1 as A
JOIN Table2 as B
ON A.Id = B.Id
CROSS JOIN
(select 1 as I UNION ALL
select 2 as I UNION ALL
select 3 as I UNION ALL
select 4 as I) as N
Roy Harvey
Beacon Falls, CT
On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>My apologies. There are PK and FK keys. Between Table1 and Table2 there is
>one-to-one relationship. Each table has 50-60 columns but I will precisely
>re-append the tables:
>Table1
>Id (PK) CustNum
>100 C041
>101 C042
>102 C043
>105 C044
>Table2
>ID (FK) A01 A02 A03 A04
>100 100.00 120.00 150.00 160.00
>101 200.00 210.00 250.00 260.00
>102 300.00 310.00 320.00 350.00
>105 150.00 160.00 170.00 180.00
>Results required by user in the output file as follows:
>CustName ColumnName Value
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
>You will notice that values in ColumnName is actually column names in Table2.
>Thanks...Fraz
>
>"Roy Harvey" wrote:
>> I have made some comments below.
>> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
>> <Fraz@.discussions.microsoft.com> wrote:
>> >I have two tables; Table1 and Table2 with some sample data:
>> >
>> >Table1
>> >CustNum
>> >C041
>> >C042
>> >C043
>> >C044
>> >
>> >Table2
>> >A01 A02 A03 A04
>> >100.00 120.00 150.00 160.00
>> >200.00 210.00 250.00 260.00
>> >300.00 310.00 320.00 350.00
>> >150.00 160.00 170.00 180.00
>> These are ALL the rows? No key? Even denormalized tables need a key.
>> >My results should look like this out of above two tables. You will see that
>> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
>> >data against CustName (Table1) and values (Table2).
>> >
>> >CustName ColumnName Values
>> >C041 A01 100.00
>> >C041 A02 120.00
>> >C041 A03 150.00
>> >C041 A04 160.00
>> It appears that, somehow, a specific CustNum from Table1 (the "first")
>> is supposed to match up with the "first" row in Table2'
>> >C042 A01 200.00
>> >C042 A02 210.00
>> >C042 A03 250.00
>> >C042 A04 260.00
>> Same again, but the "second"?
>> >C043 A01 300.00
>> >C043 A02 310.00
>> >C043 A03 320.00
>> >C043 A04 350.00
>> etc.
>> >C044 A01 150.00
>> >C044 A02 160.00
>> >C044 A03 170.00
>> >C044 A04 180.00
>> etc.
>> >
>> >Your assistance will be appreciated.
>> >Thanks...Fraz
>> I see no way to produce the results you requested from the tables
>> given. Without CustNum on Table2 there is no way to match the two
>> tables up the way your sample output specifies. Relational tables
>> have no order, there is no "first", "next" or "last", so there can be
>> no joining on such a basis.
>> Please take the time to learn about data normalization. However long
>> it takes to learn - and it is really quite simple - compared to the
>> time you will waste trying to work with things like this it will pay
>> off easily.
>> Roy Harvey
>> Beacon Falls, CT|||It looks promising. I will use this concept to add other tables. Thanks a lot
Roy and Arnie. Regards, Fraz.
"Roy Harvey" wrote:
> Untested, but this should do it.
> SELECT CustNum,
> ColumName => CASE N.I
> WHEN 1 THEN 'A01'
> WHEN 2 THEN 'A02'
> WHEN 3 THEN 'A03'
> WHEN 4 THEN 'A04'
> END,
> Value => CASE N.I
> WHEN 1 THEN A01
> WHEN 2 THEN A02
> WHEN 3 THEN A03
> WHEN 4 THEN A04
> END
> FROM Table1 as A
> JOIN Table2 as B
> ON A.Id = B.Id
> CROSS JOIN
> (select 1 as I UNION ALL
> select 2 as I UNION ALL
> select 3 as I UNION ALL
> select 4 as I) as N
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
> >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> >one-to-one relationship. Each table has 50-60 columns but I will precisely
> >re-append the tables:
> >Table1
> >Id (PK) CustNum
> >100 C041
> >101 C042
> >102 C043
> >105 C044
> >
> >Table2
> >ID (FK) A01 A02 A03 A04
> >100 100.00 120.00 150.00 160.00
> >101 200.00 210.00 250.00 260.00
> >102 300.00 310.00 320.00 350.00
> >105 150.00 160.00 170.00 180.00
> >
> >Results required by user in the output file as follows:
> >CustName ColumnName Value
> >C041 A01 100.00
> >C041 A02 120.00
> >C041 A03 150.00
> >C041 A04 160.00
> >C042 A01 200.00
> >C042 A02 210.00
> >C042 A03 250.00
> >C042 A04 260.00
> >C043 A01 300.00
> >C043 A02 310.00
> >C043 A03 320.00
> >C043 A04 350.00
> >C044 A01 150.00
> >C044 A02 160.00
> >C044 A03 170.00
> >C044 A04 180.00
> >
> >You will notice that values in ColumnName is actually column names in Table2.
> >Thanks...Fraz
> >
> >
> >"Roy Harvey" wrote:
> >
> >> I have made some comments below.
> >>
> >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> >> <Fraz@.discussions.microsoft.com> wrote:
> >>
> >> >I have two tables; Table1 and Table2 with some sample data:
> >> >
> >> >Table1
> >> >CustNum
> >> >C041
> >> >C042
> >> >C043
> >> >C044
> >> >
> >> >Table2
> >> >A01 A02 A03 A04
> >> >100.00 120.00 150.00 160.00
> >> >200.00 210.00 250.00 260.00
> >> >300.00 310.00 320.00 350.00
> >> >150.00 160.00 170.00 180.00
> >>
> >> These are ALL the rows? No key? Even denormalized tables need a key.
> >>
> >> >My results should look like this out of above two tables. You will see that
> >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> >> >data against CustName (Table1) and values (Table2).
> >> >
> >> >CustName ColumnName Values
> >> >C041 A01 100.00
> >> >C041 A02 120.00
> >> >C041 A03 150.00
> >> >C041 A04 160.00
> >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> >> is supposed to match up with the "first" row in Table2'
> >>
> >> >C042 A01 200.00
> >> >C042 A02 210.00
> >> >C042 A03 250.00
> >> >C042 A04 260.00
> >> Same again, but the "second"?
> >>
> >> >C043 A01 300.00
> >> >C043 A02 310.00
> >> >C043 A03 320.00
> >> >C043 A04 350.00
> >> etc.
> >>
> >> >C044 A01 150.00
> >> >C044 A02 160.00
> >> >C044 A03 170.00
> >> >C044 A04 180.00
> >> etc.
> >>
> >> >
> >> >Your assistance will be appreciated.
> >> >Thanks...Fraz
> >>
> >> I see no way to produce the results you requested from the tables
> >> given. Without CustNum on Table2 there is no way to match the two
> >> tables up the way your sample output specifies. Relational tables
> >> have no order, there is no "first", "next" or "last", so there can be
> >> no joining on such a basis.
> >>
> >> Please take the time to learn about data normalization. However long
> >> it takes to learn - and it is really quite simple - compared to the
> >> time you will waste trying to work with things like this it will pay
> >> off easily.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>|||Hi,
Try this querry
SELECT a.custname,b.col,b.val FROM table a,
(
SELECT 'A01' as 'col', a01 as 'val' from table2
UNION ALL
SELECT 'A02' as 'col' , a02 as 'val' from table2
UNION ALL
SELECT 'A03' as 'col' , a03 as 'val' from table2
UNION ALL
SELECT 'A04' as 'col' , a04 as 'val' from table2
) b
ORDER BY a.custname
Amol Lembhe
"Fraz" wrote:
> It looks promising. I will use this concept to add other tables. Thanks a lot
> Roy and Arnie. Regards, Fraz.
> "Roy Harvey" wrote:
> > Untested, but this should do it.
> >
> > SELECT CustNum,
> > ColumName => > CASE N.I
> > WHEN 1 THEN 'A01'
> > WHEN 2 THEN 'A02'
> > WHEN 3 THEN 'A03'
> > WHEN 4 THEN 'A04'
> > END,
> > Value => > CASE N.I
> > WHEN 1 THEN A01
> > WHEN 2 THEN A02
> > WHEN 3 THEN A03
> > WHEN 4 THEN A04
> > END
> > FROM Table1 as A
> > JOIN Table2 as B
> > ON A.Id = B.Id
> > CROSS JOIN
> > (select 1 as I UNION ALL
> > select 2 as I UNION ALL
> > select 3 as I UNION ALL
> > select 4 as I) as N
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > <Fraz@.discussions.microsoft.com> wrote:
> >
> > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > >re-append the tables:
> > >Table1
> > >Id (PK) CustNum
> > >100 C041
> > >101 C042
> > >102 C043
> > >105 C044
> > >
> > >Table2
> > >ID (FK) A01 A02 A03 A04
> > >100 100.00 120.00 150.00 160.00
> > >101 200.00 210.00 250.00 260.00
> > >102 300.00 310.00 320.00 350.00
> > >105 150.00 160.00 170.00 180.00
> > >
> > >Results required by user in the output file as follows:
> > >CustName ColumnName Value
> > >C041 A01 100.00
> > >C041 A02 120.00
> > >C041 A03 150.00
> > >C041 A04 160.00
> > >C042 A01 200.00
> > >C042 A02 210.00
> > >C042 A03 250.00
> > >C042 A04 260.00
> > >C043 A01 300.00
> > >C043 A02 310.00
> > >C043 A03 320.00
> > >C043 A04 350.00
> > >C044 A01 150.00
> > >C044 A02 160.00
> > >C044 A03 170.00
> > >C044 A04 180.00
> > >
> > >You will notice that values in ColumnName is actually column names in Table2.
> > >Thanks...Fraz
> > >
> > >
> > >"Roy Harvey" wrote:
> > >
> > >> I have made some comments below.
> > >>
> > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > >> <Fraz@.discussions.microsoft.com> wrote:
> > >>
> > >> >I have two tables; Table1 and Table2 with some sample data:
> > >> >
> > >> >Table1
> > >> >CustNum
> > >> >C041
> > >> >C042
> > >> >C043
> > >> >C044
> > >> >
> > >> >Table2
> > >> >A01 A02 A03 A04
> > >> >100.00 120.00 150.00 160.00
> > >> >200.00 210.00 250.00 260.00
> > >> >300.00 310.00 320.00 350.00
> > >> >150.00 160.00 170.00 180.00
> > >>
> > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > >>
> > >> >My results should look like this out of above two tables. You will see that
> > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > >> >data against CustName (Table1) and values (Table2).
> > >> >
> > >> >CustName ColumnName Values
> > >> >C041 A01 100.00
> > >> >C041 A02 120.00
> > >> >C041 A03 150.00
> > >> >C041 A04 160.00
> > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > >> is supposed to match up with the "first" row in Table2'
> > >>
> > >> >C042 A01 200.00
> > >> >C042 A02 210.00
> > >> >C042 A03 250.00
> > >> >C042 A04 260.00
> > >> Same again, but the "second"?
> > >>
> > >> >C043 A01 300.00
> > >> >C043 A02 310.00
> > >> >C043 A03 320.00
> > >> >C043 A04 350.00
> > >> etc.
> > >>
> > >> >C044 A01 150.00
> > >> >C044 A02 160.00
> > >> >C044 A03 170.00
> > >> >C044 A04 180.00
> > >> etc.
> > >>
> > >> >
> > >> >Your assistance will be appreciated.
> > >> >Thanks...Fraz
> > >>
> > >> I see no way to produce the results you requested from the tables
> > >> given. Without CustNum on Table2 there is no way to match the two
> > >> tables up the way your sample output specifies. Relational tables
> > >> have no order, there is no "first", "next" or "last", so there can be
> > >> no joining on such a basis.
> > >>
> > >> Please take the time to learn about data normalization. However long
> > >> it takes to learn - and it is really quite simple - compared to the
> > >> time you will waste trying to work with things like this it will pay
> > >> off easily.
> > >>
> > >> Roy Harvey
> > >> Beacon Falls, CT
> > >>
> >|||Thanks Amol. Fraz
"Amol Lembhe" wrote:
> Hi,
> Try this querry
> SELECT a.custname,b.col,b.val FROM table a,
> (
> SELECT 'A01' as 'col', a01 as 'val' from table2
> UNION ALL
> SELECT 'A02' as 'col' , a02 as 'val' from table2
> UNION ALL
> SELECT 'A03' as 'col' , a03 as 'val' from table2
> UNION ALL
> SELECT 'A04' as 'col' , a04 as 'val' from table2
> ) b
> ORDER BY a.custname
> Amol Lembhe
>
> "Fraz" wrote:
> > It looks promising. I will use this concept to add other tables. Thanks a lot
> > Roy and Arnie. Regards, Fraz.
> >
> > "Roy Harvey" wrote:
> >
> > > Untested, but this should do it.
> > >
> > > SELECT CustNum,
> > > ColumName => > > CASE N.I
> > > WHEN 1 THEN 'A01'
> > > WHEN 2 THEN 'A02'
> > > WHEN 3 THEN 'A03'
> > > WHEN 4 THEN 'A04'
> > > END,
> > > Value => > > CASE N.I
> > > WHEN 1 THEN A01
> > > WHEN 2 THEN A02
> > > WHEN 3 THEN A03
> > > WHEN 4 THEN A04
> > > END
> > > FROM Table1 as A
> > > JOIN Table2 as B
> > > ON A.Id = B.Id
> > > CROSS JOIN
> > > (select 1 as I UNION ALL
> > > select 2 as I UNION ALL
> > > select 3 as I UNION ALL
> > > select 4 as I) as N
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > >
> > > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > > <Fraz@.discussions.microsoft.com> wrote:
> > >
> > > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > > >re-append the tables:
> > > >Table1
> > > >Id (PK) CustNum
> > > >100 C041
> > > >101 C042
> > > >102 C043
> > > >105 C044
> > > >
> > > >Table2
> > > >ID (FK) A01 A02 A03 A04
> > > >100 100.00 120.00 150.00 160.00
> > > >101 200.00 210.00 250.00 260.00
> > > >102 300.00 310.00 320.00 350.00
> > > >105 150.00 160.00 170.00 180.00
> > > >
> > > >Results required by user in the output file as follows:
> > > >CustName ColumnName Value
> > > >C041 A01 100.00
> > > >C041 A02 120.00
> > > >C041 A03 150.00
> > > >C041 A04 160.00
> > > >C042 A01 200.00
> > > >C042 A02 210.00
> > > >C042 A03 250.00
> > > >C042 A04 260.00
> > > >C043 A01 300.00
> > > >C043 A02 310.00
> > > >C043 A03 320.00
> > > >C043 A04 350.00
> > > >C044 A01 150.00
> > > >C044 A02 160.00
> > > >C044 A03 170.00
> > > >C044 A04 180.00
> > > >
> > > >You will notice that values in ColumnName is actually column names in Table2.
> > > >Thanks...Fraz
> > > >
> > > >
> > > >"Roy Harvey" wrote:
> > > >
> > > >> I have made some comments below.
> > > >>
> > > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > > >> <Fraz@.discussions.microsoft.com> wrote:
> > > >>
> > > >> >I have two tables; Table1 and Table2 with some sample data:
> > > >> >
> > > >> >Table1
> > > >> >CustNum
> > > >> >C041
> > > >> >C042
> > > >> >C043
> > > >> >C044
> > > >> >
> > > >> >Table2
> > > >> >A01 A02 A03 A04
> > > >> >100.00 120.00 150.00 160.00
> > > >> >200.00 210.00 250.00 260.00
> > > >> >300.00 310.00 320.00 350.00
> > > >> >150.00 160.00 170.00 180.00
> > > >>
> > > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > > >>
> > > >> >My results should look like this out of above two tables. You will see that
> > > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > > >> >data against CustName (Table1) and values (Table2).
> > > >> >
> > > >> >CustName ColumnName Values
> > > >> >C041 A01 100.00
> > > >> >C041 A02 120.00
> > > >> >C041 A03 150.00
> > > >> >C041 A04 160.00
> > > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > > >> is supposed to match up with the "first" row in Table2'
> > > >>
> > > >> >C042 A01 200.00
> > > >> >C042 A02 210.00
> > > >> >C042 A03 250.00
> > > >> >C042 A04 260.00
> > > >> Same again, but the "second"?
> > > >>
> > > >> >C043 A01 300.00
> > > >> >C043 A02 310.00
> > > >> >C043 A03 320.00
> > > >> >C043 A04 350.00
> > > >> etc.
> > > >>
> > > >> >C044 A01 150.00
> > > >> >C044 A02 160.00
> > > >> >C044 A03 170.00
> > > >> >C044 A04 180.00
> > > >> etc.
> > > >>
> > > >> >
> > > >> >Your assistance will be appreciated.
> > > >> >Thanks...Fraz
> > > >>
> > > >> I see no way to produce the results you requested from the tables
> > > >> given. Without CustNum on Table2 there is no way to match the two
> > > >> tables up the way your sample output specifies. Relational tables
> > > >> have no order, there is no "first", "next" or "last", so there can be
> > > >> no joining on such a basis.
> > > >>
> > > >> Please take the time to learn about data normalization. However long
> > > >> it takes to learn - and it is really quite simple - compared to the
> > > >> time you will waste trying to work with things like this it will pay
> > > >> off easily.
> > > >>
> > > >> Roy Harvey
> > > >> Beacon Falls, CT
> > > >>
> > >|||Hi Roy and Amol: Just to thank you for your valuable feedback. I got the
results I wanted. Because there were many tables with 50-100 columns, I found
the Case Statement and CROSS JOIN by Roy worked out well for my needs. Thanks
again. Fraz
"Fraz" wrote:
> Thanks Amol. Fraz
> "Amol Lembhe" wrote:
> > Hi,
> > Try this querry
> > SELECT a.custname,b.col,b.val FROM table a,
> > (
> > SELECT 'A01' as 'col', a01 as 'val' from table2
> > UNION ALL
> > SELECT 'A02' as 'col' , a02 as 'val' from table2
> > UNION ALL
> > SELECT 'A03' as 'col' , a03 as 'val' from table2
> > UNION ALL
> > SELECT 'A04' as 'col' , a04 as 'val' from table2
> > ) b
> > ORDER BY a.custname
> >
> > Amol Lembhe
> >
> >
> >
> > "Fraz" wrote:
> >
> > > It looks promising. I will use this concept to add other tables. Thanks a lot
> > > Roy and Arnie. Regards, Fraz.
> > >
> > > "Roy Harvey" wrote:
> > >
> > > > Untested, but this should do it.
> > > >
> > > > SELECT CustNum,
> > > > ColumName => > > > CASE N.I
> > > > WHEN 1 THEN 'A01'
> > > > WHEN 2 THEN 'A02'
> > > > WHEN 3 THEN 'A03'
> > > > WHEN 4 THEN 'A04'
> > > > END,
> > > > Value => > > > CASE N.I
> > > > WHEN 1 THEN A01
> > > > WHEN 2 THEN A02
> > > > WHEN 3 THEN A03
> > > > WHEN 4 THEN A04
> > > > END
> > > > FROM Table1 as A
> > > > JOIN Table2 as B
> > > > ON A.Id = B.Id
> > > > CROSS JOIN
> > > > (select 1 as I UNION ALL
> > > > select 2 as I UNION ALL
> > > > select 3 as I UNION ALL
> > > > select 4 as I) as N
> > > >
> > > > Roy Harvey
> > > > Beacon Falls, CT
> > > >
> > > >
> > > > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > > > <Fraz@.discussions.microsoft.com> wrote:
> > > >
> > > > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > > > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > > > >re-append the tables:
> > > > >Table1
> > > > >Id (PK) CustNum
> > > > >100 C041
> > > > >101 C042
> > > > >102 C043
> > > > >105 C044
> > > > >
> > > > >Table2
> > > > >ID (FK) A01 A02 A03 A04
> > > > >100 100.00 120.00 150.00 160.00
> > > > >101 200.00 210.00 250.00 260.00
> > > > >102 300.00 310.00 320.00 350.00
> > > > >105 150.00 160.00 170.00 180.00
> > > > >
> > > > >Results required by user in the output file as follows:
> > > > >CustName ColumnName Value
> > > > >C041 A01 100.00
> > > > >C041 A02 120.00
> > > > >C041 A03 150.00
> > > > >C041 A04 160.00
> > > > >C042 A01 200.00
> > > > >C042 A02 210.00
> > > > >C042 A03 250.00
> > > > >C042 A04 260.00
> > > > >C043 A01 300.00
> > > > >C043 A02 310.00
> > > > >C043 A03 320.00
> > > > >C043 A04 350.00
> > > > >C044 A01 150.00
> > > > >C044 A02 160.00
> > > > >C044 A03 170.00
> > > > >C044 A04 180.00
> > > > >
> > > > >You will notice that values in ColumnName is actually column names in Table2.
> > > > >Thanks...Fraz
> > > > >
> > > > >
> > > > >"Roy Harvey" wrote:
> > > > >
> > > > >> I have made some comments below.
> > > > >>
> > > > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > > > >> <Fraz@.discussions.microsoft.com> wrote:
> > > > >>
> > > > >> >I have two tables; Table1 and Table2 with some sample data:
> > > > >> >
> > > > >> >Table1
> > > > >> >CustNum
> > > > >> >C041
> > > > >> >C042
> > > > >> >C043
> > > > >> >C044
> > > > >> >
> > > > >> >Table2
> > > > >> >A01 A02 A03 A04
> > > > >> >100.00 120.00 150.00 160.00
> > > > >> >200.00 210.00 250.00 260.00
> > > > >> >300.00 310.00 320.00 350.00
> > > > >> >150.00 160.00 170.00 180.00
> > > > >>
> > > > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > > > >>
> > > > >> >My results should look like this out of above two tables. You will see that
> > > > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > > > >> >data against CustName (Table1) and values (Table2).
> > > > >> >
> > > > >> >CustName ColumnName Values
> > > > >> >C041 A01 100.00
> > > > >> >C041 A02 120.00
> > > > >> >C041 A03 150.00
> > > > >> >C041 A04 160.00
> > > > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > > > >> is supposed to match up with the "first" row in Table2'
> > > > >>
> > > > >> >C042 A01 200.00
> > > > >> >C042 A02 210.00
> > > > >> >C042 A03 250.00
> > > > >> >C042 A04 260.00
> > > > >> Same again, but the "second"?
> > > > >>
> > > > >> >C043 A01 300.00
> > > > >> >C043 A02 310.00
> > > > >> >C043 A03 320.00
> > > > >> >C043 A04 350.00
> > > > >> etc.
> > > > >>
> > > > >> >C044 A01 150.00
> > > > >> >C044 A02 160.00
> > > > >> >C044 A03 170.00
> > > > >> >C044 A04 180.00
> > > > >> etc.
> > > > >>
> > > > >> >
> > > > >> >Your assistance will be appreciated.
> > > > >> >Thanks...Fraz
> > > > >>
> > > > >> I see no way to produce the results you requested from the tables
> > > > >> given. Without CustNum on Table2 there is no way to match the two
> > > > >> tables up the way your sample output specifies. Relational tables
> > > > >> have no order, there is no "first", "next" or "last", so there can be
> > > > >> no joining on such a basis.
> > > > >>
> > > > >> Please take the time to learn about data normalization. However long
> > > > >> it takes to learn - and it is really quite simple - compared to the
> > > > >> time you will waste trying to work with things like this it will pay
> > > > >> off easily.
> > > > >>
> > > > >> Roy Harvey
> > > > >> Beacon Falls, CT
> > > > >>
> > > >
Subscribe to:
Posts (Atom)