Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query to combine headers and values from two tables

Posted on 2007-04-03
8
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 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