how to use unpivot for sql server 2005

Question

for this , i have many UNION ALL and how to use unpivot same result for this query

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

Answer 1

You could change the query to something like this:

SELECT count(*)
FROM admmaster
INNER JOIN ipdsummary ON admaster.an = ipdsummary.an
UNPIVOT (Value FOR ColName IN (
    DIAGNOSES, UNDERLYINGCAUSE, ... 
)) as UnPvt
WHERE ADMWARD IN (16,17) and Value = 'Q792'

Since Value can't be NULL if it's 'Q792', the NULL check can be eliminated.