Please help with Query

  • 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

  • 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 😀

  • 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

  • Thank you, is it possible to create a new column instead of using the comma?

  • 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.

  • 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

  • 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.:-)

  • 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.

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply