473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Can this format be created?

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! :)
Jul 20 '05 #1
10 2107
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! :)


Jul 20 '05 #2
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! :)


Jul 20 '05 #3
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! :)


Jul 20 '05 #4
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! :)


Jul 20 '05 #5
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! :)


Jul 20 '05 #6
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! :)


Jul 20 '05 #7
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! :)

Jul 20 '05 #8
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! :)

Jul 20 '05 #9
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" <maylar69@hot----.com> 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! :)


Jul 20 '05 #10
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" <maylar69@hot----.com> 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! :)


Jul 20 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Danny Lesandrini | last post by:
I'm using an Access database to drive a web site and the colors of various table backgrounds are stored in Access. I want users of the Access database to be able to select colors for the site, but...
1
by: jty202 | last post by:
I know alot of people have the problem with indexed pixel format. I hope someone can show me the solution to this.I am have problem with graphics with the following code giving the error: "A...
7
by: Edward Mitchell | last post by:
I have a number of DateTimePicker controls, some set to dates, some set to a format of Time. The controls are all embedded in dialogs. I created the controls by dragging the DateTime picker from...
8
by: ZhangZQ | last post by:
Is there an API to format the HD? I found to use the DeviceIoControl with IOCTL_DISK_FORMAT_TRACKS code can format the floppy, but how to format the HD, I don't want the SHFormatDrvie way. ...
8
by: John Brock | last post by:
I am creating an Excel workbook using VB.NET, and have run into a problem. Excel at times insists on reformatting data that I enter into cells, e.g., converting "01234" to "1234", and this screws...
0
by: Peter Nofelt | last post by:
Hi all, ISSUE: ==================== In SQL 2005 (sp2) I get the following error when preforming a bulk insert with an associated xml format file: "Could not bulk insert. Unknown version of...
3
by: Academia | last post by:
I have a vs2003 project (actually 44 of them) that I want to convert to VS2005 format. To fix a usercontrol file I created the Designer.vb file and moved the Inherited and Class ( change to...
8
by: joemacbusiness | last post by:
Hi All, How do I format printed data in python? I could not find this in the Python Reference Manual: http://docs.python.org/ref/print.html Nor could I find it in Matloff's great tutorial:...
7
by: pamela fluente | last post by:
My numericUpDowns show numbers in the format 1.500,56 (italy). Instead, I need *invariantly* that they show and accept the format 1,500.56, as in the USA. What's the right way to do that? I...
1
by: Joe Humburg | last post by:
Hi everyone, Looking for some help or ideas, on the folloiwng problem. Have an Access 2003 application that creates an Excel file containing data from an Access parameter query. This is...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.