I have a list of orders/containers, then run through multiple operations which also have multiple workstations which have multiple duplicate results
i would like to see
[order], [operation] ,[DISTINCT workstation]
SELECT
container.containername,
Operation.OperationName,
STRING_AGG(
CONVERT(
NVARCHAR(max),
ExecuteTaskHistory.ResourceName
),
','
) as workstation
FROM
Operation
RIGHT OUTER JOIN HistoryMainline ON Operation.OperationId = HistoryMainline.OperationId outer apply (
select
distinct w.resourcename
from
ExecuteTaskHistory e
inner join HistoryMainline hml on e.HistoryMainlineId = hml.HistoryMainlineId
inner JOIN ResourceDef AS w on w.ResourceId = e.WorkstationId
where
HistoryMainline.ContainerId = hml.ContainerId
and HistoryMainline.OperationId = hml.OperationId
) ExecuteTaskHistory
LEFT OUTER JOIN Container ON HistoryMainline.ContainerId = Container.ContainerId
Group By
container.containername,
Operation.OperationName
Have tried doing select top 1 but that will not provide correct results.
New contributor
Grant Hamilton is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1