CODEWITHSUNDEEP
sqlsql-server
Russ Rosenlof
Russ Rosenlof

Reputation: 25

Multiple ALIAS GROUP BY issue, returning more rows than needed

I'm having trouble getting an output that is only one line. The GROUP BY gives an error if I don't include the TF.Table_Field_Desc and TFV.Value items, but it's outputting:

Current output

I'm trying to get a result where instead of six rows with the first three columns being repeated and lots of nulls, I get one row with all my columns populated.

I think I need to change the way I'm doing my CASE statements but I'm just not sure how. Thanks!

SELECT V.Var_Desc, RTS.Route_Desc, T.Result,
CASE WHEN TF.Table_Field_Desc = 'TestTime' THEN TFV.Value END AS Duration,
CASE WHEN TF.Table_Field_Desc = 'LongTaskName' THEN TFV.Value END AS TaskName,
CASE WHEN TF.Table_Field_Desc = 'Criteria' THEN TFV.Value END AS Criteria,
CASE WHEN TF.Table_Field_Desc = 'Method' THEN TFV.Value END AS Method,
CASE WHEN TF.Table_Field_Desc = 'Hazards' THEN TFV.Value END AS Hazards,
CASE WHEN TF.Table_Field_Desc = 'TaskType' THEN TFV.Value END AS TaskType
FROM dbo.Tests AS T
    RIGHT JOIN dbo.Table_Fields_Values AS TFV ON T.Var_Id = TFV.KeyId
    RIGHT JOIN dbo.Table_Fields AS TF ON TFV.Table_Field_Id = TF.Table_Field_Id
    RIGHT JOIN dbo.Variables AS V ON T.Var_Id = V.Var_Id
    RIGHT JOIN dbo.RouteTasks AS RTKS ON T.Var_Id = RTKS.Var_Id
    RIGHT JOIN dbo.Routes AS RTS ON RTKS.Route_Id = RTS.Route_Id
    RIGHT JOIN dbo.TeamRoutes AS TMRTS ON RTKS.Route_Id = TMRTS.Route_Id
    RIGHT JOIN dbo.Teams AS TMS ON TMRTS.Team_Id = TMS.Team_Id AND TMS.Team_Desc LIKE '%C Team%'
WHERE TFV.Table_Field_Id IN (31,32,35,40,41,42) AND (T.Result = 'Pending' OR T.Result = 'Late')
GROUP BY TFV.Value, TF.Table_Field_Desc, T.Result, V.Var_Desc, RTS.Route_Desc
ORDER BY Var_Desc

Upvotes: 1

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I am guessing that you want a query more like this:

SELECT V.Var_Desc, RTS.Route_Desc, T.Result,
       MAX(CASE WHEN TF.Table_Field_Desc = 'TestTime' THEN TFV.Value END) AS Duration,
       MAX(CASE WHEN TF.Table_Field_Desc = 'LongTaskName' THEN TFV.Value END AS TaskName,
       MAX(CASE WHEN TF.Table_Field_Desc = 'Criteria' THEN TFV.Value END) AS Criteria,
       MAX(CASE WHEN TF.Table_Field_Desc = 'Method' THEN TFV.Value END) AS Method,
       MAX(CASE WHEN TF.Table_Field_Desc = 'Hazards' THEN TFV.Value END) AS Hazards,
       MAX(CASE WHEN TF.Table_Field_Desc = 'TaskType' THEN TFV.Value END) AS TaskType
FROM dbo.Tests AS T
    RIGHT JOIN dbo.Table_Fields_Values AS TFV ON T.Var_Id = TFV.KeyId
    RIGHT JOIN dbo.Table_Fields AS TF ON TFV.Table_Field_Id = TF.Table_Field_Id
    RIGHT JOIN dbo.Variables AS V ON T.Var_Id = V.Var_Id
    RIGHT JOIN dbo.RouteTasks AS RTKS ON T.Var_Id = RTKS.Var_Id
    RIGHT JOIN dbo.Routes AS RTS ON RTKS.Route_Id = RTS.Route_Id
    RIGHT JOIN dbo.TeamRoutes AS TMRTS ON RTKS.Route_Id = TMRTS.Route_Id
    RIGHT JOIN dbo.Teams AS TMS ON TMRTS.Team_Id = TMS.Team_Id AND TMS.Team_Desc LIKE '%C Team%'
WHERE TFV.Table_Field_Id IN (31, 32, 35, 40, 41, 42) AND
      T.Result IN ('Pending',  'Late')
GROUP BY V.Var_Desc, RTS.Route_Desc, T.Result,
ORDER BY Var_Desc;

Upvotes: 1

Devart
Devart

Reputation: 121912

SELECT 
    V.Var_Desc,
    RTS.Route_Desc,
    T.Result,
    MAX(CASE WHEN TF.Table_Field_Desc = 'TestTime' THEN TFV.Value END) AS Duration,
    MAX(CASE WHEN TF.Table_Field_Desc = 'LongTaskName' THEN TFV.Value END) AS TaskName,
    MAX(CASE WHEN TF.Table_Field_Desc = 'Criteria' THEN TFV.Value END) AS Criteria,
    MAX(CASE WHEN TF.Table_Field_Desc = 'Method' THEN TFV.Value END) AS Method,
    MAX(CASE WHEN TF.Table_Field_Desc = 'Hazards' THEN TFV.Value END) AS Hazards,
    MAX(CASE WHEN TF.Table_Field_Desc = 'TaskType' THEN TFV.Value END) AS TaskType
FROM dbo.Tests AS T
RIGHT JOIN ...
WHERE TFV.Table_Field_Id IN (31,32,35,40,41,42)
    AND T.Result IN ('Pending', 'Late')
GROUP BY TFV.Value, T.Result, V.Var_Desc, RTS.Route_Desc
ORDER BY Var_Desc

Upvotes: 0

Related Questions