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
> > > > >>
> > > >
No comments:
Post a Comment