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! :) 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! :)
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! :)
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! :)
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! :)
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! :)
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! :)
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! :)
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! :)
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! :)
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! :)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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:...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |