August 29, 2011 at 9:01 am
I am running a query collecting data from multiple tables and connecting it to a master part number on the same row. The problem that i am running into is in some tables there is more than on piece of data for that particular part number so an extra row is created with the same master part number but different additional data. Please take a look at the example bellow.
Current result
Master Part # | value from data table A | value from data table B
123 X Z
123 X Y
Desired Result: Instead of creating adding a row I would like to know if there is a command to create a new column and have all the available data for the master part number on the same row. By having the data in the same row it will much easier to work with.
Master Part # | value from data table A |value from data table B| value from data table B|
123 X Z Y
Thank you for your help it is very much appreciated,
David
August 29, 2011 at 9:22 am
Possible, but will not scale that better. I can think of one solution
1. UNVPIVOT all rows
2. Collect DISTINCT Data Values
3. Use CASE Statements to PIVOT them back.
You able to get it? I am running late for my office, i will see if i can work on it once i reach office 😀
August 29, 2011 at 9:39 am
Short answer is yes you can, but it will probably take Pivoting work and maybe some Dynamic SQL. It would be easier to do a single column with all part numbers using a concatenation (search SSC for articles) and a grouping on the first 2 columns.
So your table would be:
Master # | Value A | Values B
123 | X | Z, Y
Here's some sample code to do that:
DECLARE @t TABLE(
id INT,
col CHAR(2)
);
INSERT INTO @t VALUES (1,'aa');
INSERT INTO @t VALUES (1,'bb');
INSERT INTO @t VALUES (1,'c>');
INSERT INTO @t VALUES (2,'a<');
SELECT
id,
STUFF(
(
SELECT ',' + col
FROM @t t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(100)')
,1,1,'')
FROM @t t1
GROUP BY id
EDIT: to add example
August 29, 2011 at 10:26 am
Thank you, is it possible to create a new column instead of using the comma?
August 29, 2011 at 10:41 am
We can. TO add to what i already said, it is going to be a complicated method, and it will get ugly when we have inconsistent number values for each ID.
September 1, 2011 at 1:23 pm
I would do this if it is feasible:
SELECT p.PartNum, a.Data, b.1Data, b2.Data, ... , n.Data
FROM PARTS p
LEFT JOIN DataTableA a
ON p.PartNum = a.PartNum,
LEFT JOIN DataTableB b1
ON p.PartNum = b1.PartNum
AND b1.Data = 'Y'
LEFT JOIN DataTableB b2
ON p.PartNum = b2.PartNum
AND b2.Data = 'Z'
...
LEFT JOIN DataTableN n
ON p.PartNum = n.PartNum
If you cannot do this or it is not what you are looking for (i.e. there will be nulls), then you may have to write a cursor.
Can you explain the business case for this? It may help us to come up with a better solution for you.
Thanks,
Jared
Jared
CE - Microsoft
September 2, 2011 at 2:44 am
Although only a relative newbie to SQL I would take the approach that jared-709193 suggested. I have used this myself before and it works for pulling data out into reports for this type of situation.
As a matter of interest if any of the more experienced posters think that this approach is wrong, or could be done better I would be very interested in what they have to say. I am constantly on a learning curve so any improvements on what I currently do are always of interest. I look back at queries I wrote a year ago and cringe so know I have a way to go.:-)
September 2, 2011 at 7:48 am
david 98157 (8/29/2011)
I am running a query collecting data from multiple tables and connecting it to a master part number on the same row. The problem that i am running into is in some tables there is more than on piece of data for that particular part number so an extra row is created with the same master part number but different additional data.
I'm curious as to what you are dealing with here. Could you post the DDL of the tables involved and a little sample data?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
September 5, 2011 at 9:52 am
I think that with the information given, that this is the best approach. Although changing the database and table design may be more beneficial, I am not suggesting anything of the sort because it is my goal to answer the question regarding the query, not design.
Many small size companies cannot afford to make design changes when the request from stakeholders is a report. The design changes can cause many issues with existing software that may not be feasible for the company to take the time and money to change. I am assuming that they are past a design phase and that this user is requesting a way to get the data out in this format without having to change the DDL.
Jared
Jared
CE - Microsoft
September 7, 2011 at 7:05 pm
CELKO (9/5/2011)
tim.kay (9/2/2011)
Would you split a tire into a table for width, material and diameter into their own tables? Or would you use the ISO tire code such as 155SR15 (155 mm steel radial in a 15” rim)? One fact, one place3, one time, represented one way; we hate splits and redundancy
Oddly enough, that's a form of denormalization because it's not just one fact... it's 3 facts. You have to do some form of splitting to find, for example, all tires that will fit a 15 inch rim.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply