How to select in result set UNION ALL operation

Question

SELECT * FROM 
(SELECT BAR.DIAGNOSES FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.DIAGNOSES IS NOT NULL)
UNION ALL
SELECT BAR.UNDERLYINGCAUSE FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.UNDERLYINGCAUSE IS NOT NULL)
UNION ALL
SELECT BAR.UNDERLYINGCAUSE2 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.UNDERLYINGCAUSE2 IS NOT NULL)
UNION ALL
SELECT BAR.UNDERLYINGCAUSE3 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.UNDERLYINGCAUSE3 IS NOT NULL)
UNION ALL
SELECT BAR.UNDERLYINGCAUSE4 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.UNDERLYINGCAUSE4 IS NOT NULL)
UNION ALL
SELECT BAR.UNDERLYINGCAUSE5 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.UNDERLYINGCAUSE5 IS NOT NULL)
UNION ALL
SELECT BAR.COMPLICATIONS FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.COMPLICATIONS IS NOT NULL)
UNION ALL
SELECT BAR.COMPLICATIONS2 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.COMPLICATIONS2 IS NOT NULL)
UNION ALL
SELECT BAR.COMPLICATIONS3 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.COMPLICATIONS3 IS NOT NULL)
UNION ALL
SELECT BAR.COMPLICATIONS4 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.COMPLICATIONS4 IS NOT NULL)
UNION ALL
SELECT BAR.COMPLICATIONS5 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.COMPLICATIONS5 IS NOT NULL)
UNION ALL
SELECT BAR.OTHERDIAGNOSES FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.OTHERDIAGNOSES IS NOT NULL)
UNION ALL
SELECT BAR.OTHERDIAGNOSES2 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.OTHERDIAGNOSES2 IS NOT NULL)
UNION ALL
SELECT BAR.OTHERDIAGNOSES3 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.OTHERDIAGNOSES3 IS NOT NULL)
UNION ALL
SELECT BAR.OTHERDIAGNOSES4 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.OTHERDIAGNOSES4 IS NOT NULL)
UNION ALL
SELECT BAR.OTHERDIAGNOSES5 FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.OTHERDIAGNOSES5 IS NOT NULL))

That result Msg 102, Level 15, State 1, Line 32 Incorrect syntax near ')'.

Answer 1

Add an alias after the last paren:

select
    *
from
   (.... unions go here ...) a

You don't need to wrap this in a select * to get the result set, but I assume you're using it as a subquery. When you do a subquery as a result set, you need to alias it, otherwise, the columns don't know where they come from.

Answer 2

You don't need the outer select * from - basically just put all the selects with union all interleaved - it should work.

Answer 3

But of course, what you mean is "UNPIVOT".

   SELECT * FROM 
   (SELECT FOO.ADMWARD, BAR.* FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN 
        WHERE FOO.ADMWARD IN (16,17)
   ) t
   UNPIVOT 
   (Diag FOR PropType in (DIAGNOSES
        , UNDERLYINGCAUSE
        , UNDERLYINGCAUSE2
        , UNDERLYINGCAUSE3
        , UNDERLYINGCAUSE4
        , UNDERLYINGCAUSE5
        , COMPLICATIONS
        , COMPLICATIONS2
        , COMPLICATIONS3
        , COMPLICATIONS4
        , COMPLICATIONS5
        , OTHERDIAGNOSES
        , OTHERDIAGNOSES2
        , OTHERDIAGNOSES3
        , OTHERDIAGNOSES4
        , OTHERDIAGNOSES5
        )
   ) u;

Not sure what you want to call the columns. You'll probably want to work out an appropriate subquery to put in for 't'. And this will handle the NULLs for you too.