Distinct on row level

Question

It seemed so easy.

I am getting following table by using COALESCE. I need to perform distinct on row level.

 1  1   5   5   5   (null)
 2  2   2   2   25  25
 3  7   35  35  35  35

That's what I am looking for.

 1   5    null
 2   25
 3   7    35

Here's a Demo on http://sqlfiddle.com/#!3/e945b/5/0

I can't think of a good way to accomplish this, partly because I can't imagine why on earth you would want to return a dataset such as you are mentioning. A little more explanation might help us help you. — Christopher Brown, May 13 '14 at 22:01
You might want to [convert the rows into columns](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008) and then use `DISTINCT`. — Isaac Kleinman, May 13 '14 at 22:06
Sorry for asking a difficult question. It removes too much junk from final report. — sqlint, May 13 '14 at 22:20
Is there always 3 distinct values in each row, or can every column have a different value? — Adam Porad, May 13 '14 at 22:33
Row could have all different values. There might be one value in a row. — sqlint, May 13 '14 at 22:40
It's generally a bad sign if data of the same "type" - such that you want to compare values or combine values, as here - are appearing in separate columns. SQL tables are not the same thing as spreadsheets and shouldn't be used like them. — Damien_The_Unbeliever, May 14 '14 at 06:05

score 2 · Accepted Answer · answered May 14 '14 at 05:56

This is the only way I can think of doing it. Do not currently have enough time to explain its operation, so please post questions in comments;

WITH DataCTE (RowID, a, b, c, d, e, f) AS
(
    SELECT 1, 1, 1, 5,  5,   5,   NULL  UNION ALL
    SELECT 2, 2, 2, 2,  2,   25,  25    UNION ALL
    SELECT 3, 3, 7, 35, 35,  35,  35
)
,UnPivotted AS
(
    SELECT   DC.RowID
            ,CA.Distinctcol
            ,OrdinalCol = ROW_NUMBER() OVER (PARTITION BY DC.RowID ORDER BY CA.Distinctcol)
    FROM DataCTE    DC
    CROSS 
    APPLY   (
                SELECT Distinctcol
                FROM
                (
                    SELECT Distinctcol = a  UNION 
                    SELECT b    UNION 
                    SELECT c    UNION 
                    SELECT d    UNION 
                    SELECT e    UNION
                    SELECT f    
                )DT
                WHERE Distinctcol IS NOT NULL

            )       CA(Distinctcol) 
)
SELECT   RowID
        ,Col1   =   MAX(CASE WHEN OrdinalCol = 1 THEN Distinctcol ELSE NULL END)
        ,Col2   =   MAX(CASE WHEN OrdinalCol = 2 THEN Distinctcol ELSE NULL END)
        ,Col3   =   MAX(CASE WHEN OrdinalCol = 3 THEN Distinctcol ELSE NULL END)
        ,Col4   =   MAX(CASE WHEN OrdinalCol = 4 THEN Distinctcol ELSE NULL END)
        ,Col5   =   MAX(CASE WHEN OrdinalCol = 5 THEN Distinctcol ELSE NULL END)                
FROM UnPivotted
GROUP BY RowID

Thank you MarkD. That’s exactly what I need. Is RowID column necessary in WITH DataCTE (RowID, a, b, c, d, e, f)? — sqlint, May 14 '14 at 16:30
You're welcome. Yes at the moment is it, as it is used in the `GROUP BY` — MarkD, May 14 '14 at 19:25

Distinct on row level

1 Answers1