Can this format be created?

[Bill H]

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Lord Yoda]

The only way I can think of is to use programming in a stored procedure
to literally build it 'manually', but that depends on what kind of RDBMS
you're using.

Are you able to store table 2 data in triplets the same as table 1? That
would be easy then.

There seems to be a fight between the data and the way you want to use
it. Are you sure you need to store your data that way? You don't give
any indication of your abilities and it might be that another approach
is better than a solution.
Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Lord Yoda]

The only way I can think of is to use programming in a stored procedure
to literally build it 'manually', but that depends on what kind of RDBMS
you're using.

Are you able to store table 2 data in triplets the same as table 1? That
would be easy then.

There seems to be a fight between the data and the way you want to use
it. Are you sure you need to store your data that way? You don't give
any indication of your abilities and it might be that another approach
is better than a solution.
Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Lord Yoda]

On second thoughts, you need a crosstab query
http://dev.mysql.com/tech-resources/...ard/index.html
Lord Yoda wrote:

The only way I can think of is to use programming in a stored procedure
to literally build it 'manually', but that depends on what kind of RDBMS
you're using.

Are you able to store table 2 data in triplets the same as table 1? That
would be easy then.

There seems to be a fight between the data and the way you want to use
it. Are you sure you need to store your data that way? You don't give
any indication of your abilities and it might be that another approach
is better than a solution.
Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Lord Yoda]

On second thoughts, you need a crosstab query
http://dev.mysql.com/tech-resources/...ard/index.html
Lord Yoda wrote:

The only way I can think of is to use programming in a stored procedure
to literally build it 'manually', but that depends on what kind of RDBMS
you're using.

Are you able to store table 2 data in triplets the same as table 1? That
would be easy then.

There seems to be a fight between the data and the way you want to use
it. Are you sure you need to store your data that way? You don't give
any indication of your abilities and it might be that another approach
is better than a solution.
Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Lord Yoda]

It's similar to a crosstab query format, but yet isn't.
http://dev.mysql.com/tech-resources/...t_version.html
Lord Yoda wrote:

The only way I can think of is to use programming in a stored procedure
to literally build it 'manually', but that depends on what kind of RDBMS
you're using.

Are you able to store table 2 data in triplets the same as table 1? That
would be easy then.

There seems to be a fight between the data and the way you want to use
it. Are you sure you need to store your data that way? You don't give
any indication of your abilities and it might be that another approach
is better than a solution.
Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Lord Yoda]

It's similar to a crosstab query format, but yet isn't.
http://dev.mysql.com/tech-resources/...t_version.html
Lord Yoda wrote:

The only way I can think of is to use programming in a stored procedure
to literally build it 'manually', but that depends on what kind of RDBMS
you're using.

Are you able to store table 2 data in triplets the same as table 1? That
would be easy then.

There seems to be a fight between the data and the way you want to use
it. Are you sure you need to store your data that way? You don't give
any indication of your abilities and it might be that another approach
is better than a solution.
Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[David L]

it would be quite easy in Access using linked mysql tables. A
combination of a crosstab and a select query....

Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[David L]

it would be quite easy in Access using linked mysql tables. A
combination of a crosstab and a select query....

Bill H wrote:

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Isaac Blank]

SELECT Table1.ID,Table1.Data1, Table1.Data2, Table1.Data3,
t2.v20, t2.v30, t2.v40
FROM Table1,
(SELECT ID,
max(CASE WHEN Key = 20 THEN Value ELSE '' END) as v20,
max(CASE WHEN Key = 30 THEN Value ELSE '' END) as v30,
max(CASE WHEN Key = 40 THEN Value ELSE '' END) as v40
FROM Table 2
GROUP BY ID ) as t2
WHERE t2.ID = Table1.ID

"Bill H" <[e-mail]> wrote in message
news:vf********************************@4ax.com...

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)

[Isaac Blank]

SELECT Table1.ID,Table1.Data1, Table1.Data2, Table1.Data3,
t2.v20, t2.v30, t2.v40
FROM Table1,
(SELECT ID,
max(CASE WHEN Key = 20 THEN Value ELSE '' END) as v20,
max(CASE WHEN Key = 30 THEN Value ELSE '' END) as v30,
max(CASE WHEN Key = 40 THEN Value ELSE '' END) as v40
FROM Table 2
GROUP BY ID ) as t2
WHERE t2.ID = Table1.ID

"Bill H" <[e-mail]> wrote in message
news:vf********************************@4ax.com...

I have 2 tables:

Table1 contains (among other things):

ID Data1 Data2 Data3
1 aaa bbb ccc
2 ddd eee fff
3 ggg hhh iii

Table2 contains (among other things:

ID Key Value
1 20 zzz
1 30 yyy
1 40 xxx
2 20 www
2 30 vvv
2 40 uuu
3 20 ttt
3 30 sss
3 40 rrr

I need a query that returns:

ID Data1 Data2 Data3 Value Value Value
(Key=20) (Key=30) (Key=40)
1 aaa bbb ccc zzz yyy xxx
2 ddd eee fff www vvv uuu
3 ggg hhh iii ttt sss rrr

Any ideas?

Thanks in advance for a push in the right direction! :)