question

David 2 1 avatar image
David 2 1 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
tsqlsql server 2012union-all
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks everyone. I originally tried to get it to work with pivot but couldn't get it to work. Now you've both helped me work it out. Many thanks.
1 Like 1 ·
mjharper avatar image
mjharper answered
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').
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.