CODEWITHSUNDEEP
sql-server-2008t-sql
sqlint
sqlint

Reputation: 1089

Distinct on row level

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

Upvotes: 0

Views: 144

Answers (1)

MarkD
MarkD

Reputation: 5316

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

Upvotes: 2

Related Questions