asked
Removing UNION ALL From Query
Hi there, Is there a smarter way to write a SELECT query so that the UNION ALL is removed and that the table is only queried once? Here is the test data: CREATE TABLE t2( ID INT, Condition1 VARCHAR(100), Condition2 VARCHAR(100), Condition3 VARCHAR(100), Condition4 VARCHAR(100)) INSERT INTO t2 SELECT 2425,'New','New','Reasonable',NULL UNION SELECT 2513,'Reasonable','Reasonable','Reasonable ',NULL UNION SELECT 2386,'Reasonable',NULL,'Reasonable','Reasonable' UNION SELECT 2391,'Reasonable','Poor','Poor',NULL UNION SELECT 3401,'New','Poor','New',NULL UNION SELECT 2402,'New','Reasonable','Reasonable',NULL UNION SELECT 2486,'Reasonable',NULL,'Reasonable','Reasonable' UNION SELECT 2955,'Poor',NULL,'Reasonable ',NULL UNION SELECT 4248,'Reasonable',NULL,'Reasonable','Reasonable' UNION SELECT 2399,'New','Reasonable',NULL,'Poor' UNION SELECT 2524,NULL,'Poor','Reasonable','Reasonable' UNION SELECT 2965,'Reasonable','Poor','Reasonable',NULL And here is the query that I'd like to execute but only query the table t2 once thus removing the UNION ALL commands: SELECT ID, 'Condition1 - ' + COALESCE(Condition1,NULL,'Unknown') AS Conditions FROM t2 GROUP BY ID, Condition1 UNION ALL SELECT ID, 'Condition2 - ' + COALESCE(Condition2,NULL,'Unknown') FROM t2 GROUP BY ID, Condition2 UNION ALL SELECT ID, 'Condition3 - ' + COALESCE(Condition3,NULL,'Unknown') FROM t2 GROUP BY ID, Condition3 UNION ALL SELECT ID, 'Condition4 - ' + COALESCE(Condition4,NULL,'Unknown') FROM t2 GROUP BY ID, Condition4 TIA
Based on a [SQLServerCentral "SQL Spackle" article][1], here are a couple of options: SELECT id, Conditions FROM (SELECT id, 'Condition 1 - ' + ISNULL(Condition1,'Unknown') AS Condition1, 'Condition 2 - ' + ISNULL(Condition2,'Unknown') AS Condition2, 'Condition 3 - ' + ISNULL(Condition3,'Unknown') AS Condition3, 'Condition 4 - ' + ISNULL(Condition4,'Unknown') AS Condition4 FROM t2) p UNPIVOT (Conditions FOR Condition IN ([Condition1], [Condition2], [Condition3], [Condition4])) AS u SELECT id, Conditions FROM t2 CROSS APPLY (VALUES ('Condition1', 'Condition 1 - ' + ISNULL(Condition1,'Unknown')), ('Condition2', 'Condition 2 - ' + ISNULL(Condition2,'Unknown')), ('Condition3', 'Condition 3 - ' + ISNULL(Condition3,'Unknown')), ('Condition4', 'Condition 4 - ' + ISNULL(Condition4,'Unknown'))) x(Cond, Conditions) The traditional "UNPIVOT" example is shown first - this is way more efficient than your "UNION ALL" attempt; however, looking at the execution plans, the CROSS APPLY method appears to be more efficient, albeit marginally with this test data. --edit-- I've just read @mjharper's solution, and it seems to fall (in terms of execution plan complexity) between my two solutions... [1]:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Hi, Initially I thought you could do this using UNPIVOT (similar to your previous question). For example: SELECT ID, unpvt.Conditions + ' - ' + unpvt.Value FROM (SELECT Id, Condition1, Condition2, Condition3, Condition4 FROM #t2) p UNPIVOT(Value FOR Conditions IN (Condition1, Condition2, Condition3, Condition4)) AS unpvt However this loses the NULLs. The below query will keep the NULLs - but not sure it's any cleaner than your original query: SELECT
a.ID, b.column_name + ' - ' + CASE b.column_name WHEN 'Condition1' THEN ISNULL(a.Condition1, 'Unknown') WHEN 'Condition2' THEN ISNULL(a.Condition2, 'Unknown') WHEN 'Condition3' THEN ISNULL(a.Condition3, 'Unknown') WHEN 'Condition4' THEN ISNULL(a.Condition4, 'Unknown') END FROM (SELECT ID, Condition1, Condition2, Condition3, Condition4 FROM #t2) a CROSS JOIN ( SELECT 'Condition1' UNION ALL SELECT 'Condition2' UNION ALL SELECT 'Condition3' UNION ALL SELECT 'Condition4' ) b(column_name) By the way COALESCE will take the first non-NULL expression so the NULL you have in there is not required - i.e. should just be COALESCE(Condition4,'Unknown'). This can also be written as ISNULL(a.Condition4, 'Unknown').
18 People are following this question.
