Aggregate Duplicates

[hansdbase]

I have a table or WorkOrders that simply specifies the workordernumber, name, and description. I have another table of WorkOrderDetails that contains a foreign key linked to the workordernumber (workorderidfk) and that lists the ids for the person, parts, purposes and approvals of the work. It could be on a single workorder that a single part in the details sections might have multiple purposes and that for a single purpose is might have multiple approval codes. Obviously since the workorderdetails table contains id references to other tables such as parts, I have multiple joins to display the actual names of the objects. I have painstakingly tried and failed to use string_agg with a subquery and other methods to produce a view or query that simply aggregates WITHOUT DUPLICATES the details for each WorkOrder. For instance, Workorder Number 1 should show PersonA,Part1\Part2\Part5,Purpose1\Purpose2,Approv alCode1. For this exercise I am not concerned about the actual relationships within the details. I am literally trying to produce a list of each distinct aggregation for each column and combine that into a unique view. But to no avail. I also tried other methods other than string_agg and could not get this to work. It always produces something like PersonA/PersonA, Part1/Part1/Part2,Purpose1/Purpose2/Purpose2,ApprovalCode1. Any ideas? Basically I think if I could create a view or a new table pulling each column from the workorder table where I pull all distinct values from each column and aggregate them into a new field and then combine that with the same operation on every other column in the details table, that would do what I need. Any ideas greatly appreciated!

[Micron]

It seems that you're trying to put all related information in to the same wo form but the best approach is likely wo form as the main form with subforms for the related info. That or main form should open these other forms in separate windows.

[hansdbase]

Thank you. I don't want to use a form here. I don't want to use vba. I just want to create a table listing each unique value for each column without any reference to the relationships between the columns.
For instance,

WorkOrders

WorkorderID

WorkOrderName

WorkOrderDetails

WorkorderDetailsID

WorkorderIDFK

OwnerIDFK

PartsIDFK

PurposeIDFK

ApprovalCodeIDFK

Sample
Work Orders

WorkOrderId	WorkOrderName
1	Ceiling Repair

Work Order Details

WorkorderDetailsID	WorkorderIDFK	OwnerIDFK	PartsIDFK	PurposeIDFK	ApprovalCodeIDFK
1	1	2	3	2	1
1	1	2	3	3	1
1	1	2	3	4	1

I want the result to show this grouping by the WorkorderID

WorkorderID	OwnerIDFK	PartsIDFK	PurposeIDFK	ApprovalCodeidfk
1	2	3	2,3,4	1

Eventually of course, I will do a join so it showing the actual parts name, etc. But I am just trying to get the concept down now. Right now no matter what I do, I get 3,3,3 for the Parts for instance. I can’t seem to get rid of the duplicate values. I am just looking for a unique ingredient list per work order without regard to how many times it may appear in the details table or its relationship to other columns.
Any help is greatly appreciated.

[Micron]

I don't want to use a form here. I don't want to use vba.

Then I'd say you have a task that will be impossible to achieve without code. What you're asking for is to concatenate related data into one field. However it makes no sense to take the data you already have, do that to it and store it in a table because it would be data duplication. At most, you'd do this in a query. If it had to be stored that way, it should be done at the time of data creation.

Allan Browne has posted a topic 'concatenate related' if you decide to change your mind.

[madpiet]

Something like this?

CREATE TABLE WoD (    WorkorderDetailsID INT NOT NULL,
    WorkorderIDFK INT,
    OwnerIDFK INT,
    PartsIDFK INT,
    PurposeIDFK INT,
    ApprovalCodeIDFK INT
);
GO


INSERT INTO WoD VALUES
(1,    1,    2,    3,    2,    1),
(1,    1,    2,    3,    3,    1),
(1,    1,    2,    3,    4,    1);

SELECT 
        WorkOrderIDFK,
        OwnerIDFK,
        PartsIDFK,
        ApprovalCodeIDFK,
        PurposeList = STRING_AGG(PurposeIDFK,', ') 
FROM WoD
GROUP BY 
        WorkOrderIDFK,
        OwnerIDFK,
        PartsIDFK,
        ApprovalCodeIDFK;

Result:

WorkOrderIDFK OwnerIDFK PartsIDFK ApprovalCodeIDFK PurposeList
1 2 3 1 2, 3, 4

(Sorry, the editor munges all the spaces. {2, 3, 4} is what's returned in the [PurposeList] column.)

[hansdbase]

Thank you. Where it states "Insert into WoD Values" and then you have the actual array of values, does that actually go into the code? Or is there as an explanation of what it should achieve? Also, except for Workorderidfk, I want to aggregate all of them individually and not just purpose. Will this still work for that?

[Bulzie]

Still confusing. You want to view this in a query, a new table, report? You said not on a form?

You said it is showing duplicates, give an example of that. So you are getting 1,2,3,234,1 three times because of the 234(three different values) in the PurposeId column?

[hansdbase]

Yes. You can see that PartIDFK 3 is listed in three rows of the same table for the same workorderdetailsid. So it is aggregating as 3,3,3 when I want it to just say 3. If I were to run a string aggregate just for that column using a subquery it would pull the distinct values and just state 3. But when I combine it with the other columns in the workorderdetails table, it never works because I suppose of the relationships and the multiple joins with the three rows. Is there a way I could just query each column separately to get a aggregated string of values from each column where because I query each column separately I won't have duplicates in the aggregated string (3 instead of 3,3,3) and then just combine each individual column query result of unique aggregated values into a new view or table?

[madpiet]

the CREATE TABLE and INSERT statements are just setup. I needed them to answer the question. In your case, they should already exist. No, no arrays up my sleeve. (What do you think this is, BigQuery?) It's just T-SQL.

How do you mean "aggregate them individually"?

Also, for a proper test, you need at least two values for WorkOrderID in your table, otherwise, you're not testing the WorkOrderID grouping.

[madpiet]

Okay, now I'm officially confused. the "combine each individual column query result of unique aggregated values into a new table" part... Why not just turn the query into a view, like this?

CREATE VIEW vwWorkOrderInfoAS
SELECT 
		WorkOrderIDFK,
		OwnerIDFK,
		PartsIDFK,
		ApprovalCodeIDFK,
		PurposeList = STRING_AGG(PurposeIDFK,', ') 
FROM WoD
GROUP BY 
		WorkOrderIDFK,
		OwnerIDFK,
		PartsIDFK,
		ApprovalCodeIDFK;

[hansdbase]

the CREATE TABLE and INSERT statements are just setup. I needed them to answer the question. In your case, they should already exist. No, no arrays up my sleeve. (What do you think this is, BigQuery?) It's just T-SQL.

How do you mean "aggregate them individually"?

Also, for a proper test, you need at least two values for WorkOrderID in your table, otherwise, you're not testing the WorkOrderID grouping.

What I mean is this as shown in the table below. I have a workorderdetail table showing that for the very same work order the partidfk is used. It is used for purpose 1 where the approvalcode for purpose 1 is approvecodeid number 2. That same part 3 is also used for purpose number 2 with the same approval code. That same part is used for purpose 3 for a different approval code 4. What I want in the end is simply this:

Workorderdetailid	PartsIDFK	PurposeIDFK	ApprovalCodeIDFK
1	3	1,2,3	2,4

Source table

WorkorderdetailID	PartsIDFK	PurposeIDFK	ApprovalCodeIDFK
1	3	1	2
1	3	2	2
1	3	3	4

[ArviLaanemets]

Maybe you should consider a structure
tblWorkOrders: WorkOrderID, WorkOrderName, ... (Here may be additional fields for info belonging to work order as whole, like client ID, when the work must be done, etc.);
tblWorkOrderOperations: WorkOrderOperationID, WorkOrderID, OperationŃumber, OperationDescription, ... (OperationNumber determines the order operations are done), (Here may be additional fields for info belonging to this operation, like normative time, etc.);
tblWorkOrderOperationParts:WorkOrderOperationPartI D, WorkOrderOperationID, PartID, PartTyp, PartUnit, PartQty, ... (List all parts needed for given operation, and their quantities);
I'm not sure, where your PurposeID's and ApprovalCodeID's belong, to tblWorkOrderOperations, or to tblWorkOrderOperationParts.
Also, you can have an additional table, e.g. tblWorkOrderOperationWorkers: tblWorkOrderOperationWorkerID, WorkOrderOperationID, EmployeeID, ... (Here may be additional fields for info belonging to work made by employee , like the time spent for this operation, etc.)


And of-course various registry tables, like tblParts, tblPurposes. tblApprovalCodes, etc

[madpiet]

Okay, after more coffee and some time, I think I got it. Notes are in the code:

/* I used a Common Table Expression so I could isolate the 
    Purposes aggregate and the Parts aggregate. Then I joined
    them back together in the final query
*/


WITH ctePartPurpose(WorkOrderDetailID, PartsIDFK, Purposes)
AS
(
    SELECT    WorkOrderDetailID, 
        PartsIDFK,
        Purposes = STRING_AGG(PurposeIDFK,',')
    FROM WoD
    GROUP BY WorkOrderDetailID,
        PartsIDFK
),
cteApprovals(WorkOrderDetailID,PartsIDFK,ApprovalCodes)
AS
(
-- do same for ApprovalCode, but only get uniques
    SELECT x.WorkOrderDetailID
        , x.PartsIDFK
        , ApprovalCodes = STRING_AGG(x.ApprovalCodeIDFK,', ')
    FROM (
            /* distinct so it removes duplicates */
            SELECT DISTINCT WorkOrderDetailID, PartsIDFK, ApprovalCodeIDFK
            FROM WOD
        ) x
    GROUP BY 
        x.WorkorderDetailID
        , x.PartsIDFK
)
SELECT pp.WorkOrderDetailID
    ,pp.PartsIDFK
    ,pp.Purposes
    ,a.ApprovalCodes
FROM ctePartPurpose pp
    INNER JOIN cteApprovals a
    ON (pp.PartsIDFK = a.PartsIDFK 
        AND 
        pp.WorkOrderDetailID=a.WorkOrderDetailID)

The formatter is going to mess up my result, but it's like this:

WorkOrderDetailID | PartsIDFK | Purposes | ApprovalCodes
1 | 3 | 1,2,3 | 2,4

(sorry, don't know if you can do tables in this thing... the | means a new column)

And finally, in order to test this to make sure it really works, you should have more than one WorkOrderID in your table. (and maybe remove the {PK, FK} suffixes. If someone looks at the table, those should be obvious).

[hansdbase]

THANK YOU! Glad there are people way smarter than I am.

[madpiet]

THANK YOU! Glad there are people way smarter than I am.

You're welcome! Really interesting challenge. Do you understand the solution? In as few words as possible, Common-table expressions are basically views. The difference between those and "normal" views is that all the intermediate views/queries are not stored anywhere - they only exist during the duration of the execution of the code.

Send more challenges like that! Those are really fun!