CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Prateek
Prateek

Reputation:

How to get distinct values from columns?

I have a query that returns a result set similar to the one below 

     A    | B  | C  |    D
     -----|----|----|-----
1    abc  | d0 | e0 | true
2    def  | d0 | e1 | true
3    ghi  | d0 | e2 | true
4    jkl  | d1 | e1 | true
5    mno  | d2 | e2 | false

In Column A each value is unique. but column B,C,D have some duplicate values. I want all the values of Column A but distinct values of Column B,C,D.

Expected result is something like that

     A    | B   | C   |    D
     -----|---- |---- |-----
1    abc  | d0  | e0  | true
2    def  | NULL| e1  | NULL
3    ghi  | NULL| NULL| NULL
4    jkl  | d1  | NULL| NULL
5    mno  | d2  | e2  | false

The only constraint is, I want to achieve this in a Single select statement. No nested Select statements.

Upvotes: 4

Views: 435

Answers (3)

Quassnoi
Quassnoi

Reputation: 425261

SELECT  A,
        CASE
        WHEN EXISTS
                (
                SELECT  NULL
                FROM    mytable mi
                WHERE   mi.id < mo.id
                        AND mi.b = mo.b
                ) THEN NULL
        ELSE    B
        END AS B,
        CASE
        WHEN EXISTS
                (
                SELECT  NULL
                FROM    mytable mi
                WHERE   mi.id < mo.id
                        AND mi.c = mo.c
                ) THEN NULL
        ELSE    c
        END AS c,
        CASE
        WHEN EXISTS
                (
                SELECT  NULL
                FROM    mytable mi
                WHERE   mi.id < mo.id
                        AND mi.d = mo.d
                ) THEN NULL
        ELSE    d
        END AS d
FROM    mytable mo

Upvotes: 1

KM.
KM.

Reputation: 103579

try this:

DECLARE @YourTable table (A char(3), B char(2), C char(2), D varchar(5))
INSERT INTO @YourTable VALUES ('abc','d0','e0','true')
INSERT INTO @YourTable VALUES ('def','d0','e1','true')
INSERT INTO @YourTable VALUES ('ghi','d0','e2','true')
INSERT INTO @YourTable VALUES ('jkl','d1','e1','true')
INSERT INTO @YourTable VALUES ('mno','d2','e2','false')


SELECT
    A
    ,CASE WHEN ROW_NUMBER() OVER(PARTITION BY B ORDER BY A,B)=1 THEN B ELSE NULL END AS B
    ,CASE WHEN ROW_NUMBER() OVER(PARTITION BY C ORDER BY A,C)=1 THEN C ELSE NULL END AS C
    ,CASE WHEN ROW_NUMBER() OVER(PARTITION BY D ORDER BY A,D)=1 THEN D ELSE NULL END AS D
    FROM @YourTable 
    ORDER BY A,B,C,D

OUTPUT:

A    B    C    D
---- ---- ---- -----
abc  d0   e0   true
def  NULL e1   NULL
ghi  NULL e2   NULL
jkl  d1   NULL NULL
mno  d2   NULL false

(5 row(s) affected)

Upvotes: 3

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

What you require is actually against the nature of sql. In sql, the result does not depend on the ordering.

Even if you find a way to get a result like this (eg. as provided by Quassnoi), I would avoid doing this in SQL.

Upvotes: 0

Related Questions