June 21, 2011 at 10:27 am
Can this be done? Can you construct a QUERY to give these results?
Table 1
==================================
[IDNumber] [NAME] [COLORID]
0011JIM1
0022BILL2
Table 2
========================
[COLORID]
1RED
1BLUE
1GREEN
2YELLOW
2ORANGE
2BLACK
Desired Resuls
=================================================
[NAME][COLOR1][COLOR2][COLOR3]
JIMREDBLUEGREEN
BILLYELLOWORANGEBLACK
Thanks,
Tom
June 21, 2011 at 11:23 am
Yes it can be done using a Cross Tab Query or the PIVOT operator. What have you tried so far?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 11:43 am
I'm kind of trial and error guy. I haven't tried anything.
I just made the observation in my data, that I am going
to need to do this. Thanks for the info, I'll try to get it
working.
Thanks,
Tom
June 21, 2011 at 12:17 pm
If you;re a trial and error guy then show me your trials 🙂 and then we'll work on the errors.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 4:03 pm
tp 25072 (6/21/2011)
I'm kind of trial and error guy. I haven't tried anything.
Isn't a contradiction to have those two statements together, in the same line?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 21, 2011 at 4:30 pm
HI, why would you have a table with colors with the same ID for multiple colours?
this would give you the results for 1 person you just need to follow it for the others.
select name,b.color,c.color,d.color
from #one a inner join #two b on a.color=b.colid
and b.color='blue'
inner join #two c on a.color=c.colid
and c.color='red'
inner join #two d on a.color=d.colid
and d.color='green'
bit of a long winded way of doing it though
***The first step is always the hardest *******
June 21, 2011 at 4:45 pm
Heya... I would look further into your database design.
There are things called primary keys, with the way you have set this your table2 looks something like a lookup table which should ALWAYS have a primary key.
If you want to make the thing the same way take a look at your main table and rethink its structure.
ID - Name - ColourID_1 - ColourID_2
Or there is no point in putting them in a separate table.
Once you have done that properly you can use a more simple query
Give this a whirl:
Select 1.IDNumber, 1.Name, 2.Color from Table1 1 Inner join Table2 2 on 1.ColorID = 2.ColorID
Feel free to change the prefixes and such to what ever makes you happy.
Caz
June 22, 2011 at 7:22 am
These are all great responses. However, they seem not to fit my needs.
The reason I'm looking for a way to do this is to give me a linear result,
a single row of detail data. I have a main table which links to both lookup
tables and extra detail tables. For any record in my main table, I will have
a variable amount of detail data. Even though it varies, it will still fit in a
horizontal display layout. So, I am looking to issue a query on a detail table
which would use title of a column of data and add an incremental suffix to it.
Then create new columns for every row of data from the original column. Its
strictly to display all of the linked data in a single row.
The way we have our application right now is great when a client searches the
main table for a couple of queries. They search, get a grid of results, and some
columns are drop down boxes with a few pieces of extra detail data. However,
if my client is performing 50+ seperate queries, because they are working from
lists, then to constantly have to hover over a drop down box, slows down there
ability to see what they want
Now, I know I can achieve this with code on my server side code behind file. And
thats fine, and I'll probably use that approach. But, if there is a way to achieve
this in an SQL query, it may be more efficient, less taxing on my server.
At this point, my biggest obstacle here is how to structure a query which uses
variable data from another query in a looping fashion.
So, I'm looking to do something like this:
SELECT Table1.Name,
(
SELECT Table2.Color
FROM Table2
WHERE Table1.ColorID = Table2.ColorID
)
FROM Table1
WHERE Table1.IDNumber = @key
However, that second, inner query, would have to act like a loop, and
create columns in Table1 based on the data in the COLOR column of
Table2.
Thanks for your interest,
Tom
I'm paying attention...
PIVOT is aggregate, so its not in my trials. Cross Tab - I'm not sure, I'm still looking at that.
I don't have a problem with syntax errors, just my brain logic errors. So, I'm posting my errors
by explaining my difficulty. Cudo's... It is a contradiction. I hadn't worked on it at that point.
I am trying now. The Colors is just sample data. But, if it helps, lets assume those colors are
attributes of the names in Table1. You are very astute to suggest database design. My sample
is very simple, my real structure is very complex. Redesign would make less complicated queries.
It would also give me tables with hundreds of columns of millions of mostly empty rows. But you
are right on with that observation. Our main client table has a dozen or so fields which were
originally detail tables. Its just an example, but ColorID would be the primary key of Table1.
June 22, 2011 at 7:30 am
opc.three (6/21/2011)
Yes it can be done using a Cross Tab Query or the PIVOT operator.
The fact that they are aggregations is a distraction. Concentrate on the shape of the resultset, and that you are trying to turn a row into a column.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2011 at 11:49 am
You might consider having a look at the related link in my signature (strong hint: the name is related to perform a "Cross Tab" task)...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply