0

I have constructed this SQL query so far. It works as expected but is incomplete.

SELECT OPERATION.WORKORDER_BASE_ID   As [Work Order]
  ,OPERATION.SCHED_START_DATE    AS [Scheduled Start]
  ,OPERATION.SETUP_HRS           AS [Approx Setup Hrs]
  ,OPERATION.RUN_HRS             AS [Approx Run Hrs]
  ,REQUIREMENT.PART_ID           AS [PSU Card]
FROM  OPERATION
INNER JOIN REQUIREMENT ON REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
    AND REQUIREMENT.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID
    AND REQUIREMENT.WORKORDER_LOT_ID = OPERATION.WORKORDER_LOT_ID
WHERE OPERATION.[STATUS] ='R' 
    AND OPERATION.RESOURCE_ID LIKE '%{Root Container.equipmentName}%'
    AND REQUIREMENT.PART_ID LIKE '%PSU%'

The tricky part is that REQUIREMENT.PART_ID actually has 4 columns worth of information. For example. The last column in this current table pulls out values containing "PSU". Another adjacent column would pull out data from the same column containing "PCH" I'm not sure what the best way to handle this is. Would a UNION statement be the best way to go or is this something that could be done with CASE statements?

::EDIT::

Based on the new information I tried both pivot tables and case statements. I don't quite understand how pivot tables will work for this application without conditional statements. It seems like CASE statements would work. Drawing inspiration from here, SQL Server: Examples of PIVOTing String data, I tried this.

SELECT OPERATION.WORKORDER_BASE_ID   As [Work Order]
    ,OPERATION.SCHED_START_DATE    AS [Scheduled Start]
    ,OPERATION.SETUP_HRS           AS [Approx Setup Hrs]
    ,OPERATION.RUN_HRS             AS [Approx Run Hrs]
    ,MAX(
        CASE WHEN REQUIREMENT.PART_ID LIKE '%PSU%' 
        THEN REQUIREMENT.PART_ID ELSE '' END 
    ) AS [PSU Card]
    ,MAX(
        CASE WHEN REQUIREMENT.PART_ID LIKE '%PCH%' 
        THEN REQUIREMENT.PART_ID ELSE '' END 
    ) AS [DIE #]      
FROM  OPERATION
INNER JOIN REQUIREMENT ON REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
    AND REQUIREMENT.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID
    AND REQUIREMENT.WORKORDER_LOT_ID = OPERATION.WORKORDER_LOT_ID
WHERE OPERATION.[STATUS] ='R' 
    AND OPERATION.RESOURCE_ID LIKE '%{Root Container.equipmentName}%'
    AND REQUIREMENT.PART_ID LIKE '%PSU%'

The error message I get is:

database=FV_VM)@5000ms
On: FV_Recipe_Page.Root Container.Table.data
caused by GatewayException: Column 'OPERATION.WORKORDER_BASE_ID' 
is invalid in the select list because it is not contained in 
either an aggregate function or the GROUP BY clause.
caused by SQLServerException: Column 'OPERATION.WORKORDER_BASE_ID'
is invalid in the select list because it is not contained in either
an aggregate function or the GROUP BY clause.

Be gentle. I know my syntax is wrong, thats why I'm asking questions. I'm new to SQL and have never successfully used a PIVOT or CASE statement.

Community
  • 1
  • 1
mreff555
  • 1,049
  • 1
  • 11
  • 21
  • The concept of converting rows into columns is called PIVOT. SQL Server has a function that can be used or you can aggregate functions with CASE - there are a variety of ways to solve this, besides the duplicate, see this question as well - http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Taryn Jan 06 '15 at 14:13
  • I'm not sure this addresses the issue exactly. I den't believe the PIVOT function will work because the data isn't sequential. I had hoped that adding an extra column referencing REQUIREMENT.PART.ID to a different alias and using a CASE statement to adjust the last line would work, however I cannot get the syntax right. – mreff555 Jan 06 '15 at 15:39
  • Then you should post some sample data, the final desired result and your attempts at getting the solution. Based on your description you should be able to use pivot to get the result, but you haven't provided any reason why that wouldn't work in your question. – Taryn Jan 06 '15 at 15:42
  • Ok, based on your edit and the syntax error, you are missing the GROUP BY clause with your query. When aggregating data, any columns in your SELECT list that aren't being aggregated need to be in a GROUP BY clause. So you'll need to add `GROUP BY OPERATION.WORKORDER_BASE_ID, OPERATION.SCHED_START_DATE, OPERATION.SETUP_HRS, OPERATION.RUN_HRS` - that will resolve your error message. – Taryn Jan 06 '15 at 17:22
  • Thats what it was. Thanks! – mreff555 Jan 06 '15 at 18:19

0 Answers0