Solved

SQL Query to combine headers and values from two tables

Posted on 2007-04-03
8
230 Views
Last Modified: 2008-03-19
Hello All,

I am trying to write an sql query via query analyzer in excel, or ms access that will combine the fields from one table as the header information and the values from another table as the row information.
For example....Talble1 has headeritem1, headeritem2, headeritem3,.......and table2 has value1, value2, value3...
I want to get a grid of values like
header1,header2,header3
value1,value2,value3

I am hoping someone can help me sort this out
Thanks for the help
0
Comment
Question by:pattersonr
8 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18846910
select field1, field2, field3 from yourtable
union
select field1, field2, field3 from yourtable;

the union query will do the job
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18847035
pattersonr,
first you said
<that will combine the fields from one table as the header information and the values from another table as the row information.>

then

I want to get a grid of values like
header1,header2,header3
value1,value2,value3

can you make this  clearer?

0
 
LVL 1

Author Comment

by:pattersonr
ID: 18847267
I hope so...
I have two linked tables in an sql database.....
TABLE1 has the following
break1, break2, break3
With values like 3+,10+,25+
TABLE2 has the following
value1,value2,value3
With values like
121.75,131,25,111,12
222.32,234.56,232,12



When the query is finished I woudlike to see

3+,10+,25+
121.75,131,25,111,12
222.32,234.56,232,12

With the , being field seperators...

I hope that helps alittle...
I think the union query mentioned above might be the right track...I just havent got it to work yet...
Will take any and all solutions....:)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 18847994
The union would work...but not to produce the commas..these values would simply appear in the same columns as your heaings. the key to the union query is that each table brought in must have the same number of fields. if you want groupings of Table2 divided by commas, to appear under specific heaidngs, then we need more information.

Field1     field2   field3    field4   field5
3+          10+     25+      ?           ?
121.75   131      25        111      12
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18854961
Missed your response...sorry for the delay...
My apologies....some of the commas in the above post should have been periods...So you are correct that the uniion query would work as you mention...
I guess let me explain the end result of what I want to achieve....I am wanted to make a pivot table report in something like excel...That would allow the end user to select a specific column or multiple columns.  Each of these headers is also associated with a specific company so that would allow the user to select specific companies as well.  The basic problem as I see it so far is that the header information and the subvalues are stored in the two different tables.  I must combine them and somehow get excel/or access to see the first datarow as the header not as an additional data row.  Excel may be able to handle this...I just havent figured it out yet.

Let me konw if that helps clarify anything or gives you some additional thoughts.  I am a bit stuck on what to do after the uniion of the two datafields to acheive the desired behavior.

Thanks again
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 18855027
you can use the transferspreadsheet command with your union query to get your data into the spreadsheet, and there is an option to tell the export that the first row contains column headings.

You can get to this through your macro builder.

So as long as your header table produces only 1 row, you're golden
J
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21160007
Forced accept.

Computer101
EE Admin
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how the fundamental information of how to create a table.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question