views:

79

answers:

3

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.

+1  A: 
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
Quassnoi
Yes, something like this. Don't forget to put filters not only to the from clause of the outer query, but to all subqueries as well.
Stefan Steinegger
You need an order by clause where you order by A,B,C,D. If you order in a different way, the row where the single value of B, C or D appears might not be the first row. Then you actually don't know which value had been there.
Stefan Steinegger
where does the "id" column come from? to me, it looks like the OP is just showing the row numbers (no column name or dashes)
KM
you need to add the "mo" alias after the very last "from mytable". When I run this, I only get char(2) values starting with "d" in columns B, C and D, not the values that should appear because you have "THEN B" every time.
KM
`@KM`: fixing, thanks :)
Quassnoi
when running this, I seem yo get the "opposite values" in B, C and D. however, when I change all of your "WHEN EXISTS" to "WHEN NOT EXISTS" I get the proper results.
KM
A: 

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.

Stefan Steinegger
I disagree, if you can do it in the query, do it. Look at my answer to see how easy this is to achieve. It isn't much harder than a regular select from the table, using row_number() to find the first occurrence of each value.
KM
Just because you can does not mean you should. You also need to consider performance, maintainability, etc. "can" = "should" is too niaive. IMHO.
Dems
@Dems, process the set in SQL or loop in client application? When possible, I like to process the set in SQL, and such is the case with this this query.
KM
+3  A: 

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)
KM