robert.gerald.taylor (9/19/2012)

---

Couldn't you just unpivot your data? Something like:

SELECT DISTINCT SpecificCols
FROM
   (SELECT col1, col2, col3, col4
    FROM @t) t
UNPIVOT
   (SpecificCols FOR AllCols IN (col1, col2, col3, col4)) AS unpvt;

HTH,
Rob

SELECT col 
FROM @t
CROSS APPLY (
    VALUES (col1), (col2), (col3), (col4)) a(col)

dwain.c (9/19/2012)

---

There's a (usually) faster way:

SELECT col 
FROM @t
CROSS APPLY (
    VALUES (col1), (col2), (col3), (col4)) a(col)

This article describes the CROSS APPLY VALUES approach to UNPIVOT: http://www.sqlservercentral.com/Authors/Articles/Dwain_Camps/1444841/ including some information on the performance characteristics. More detailed performance results can be found in the associated discussion thread.