How to get individual values ​​from columns?

Question

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 columns B, C, D have multiple duplicate values. I want all the values ​​of column A, but the different values ​​of columns B, C, D.

The expected result is something like this.

  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 limitation is that I want to achieve this in the Single select statement. There are no nested Select statements.

Answer 1

try the following:

 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:

 ABCD ---- ---- ---- ----- abc d0 e0 true def NULL e1 NULL ghi NULL e2 NULL jkl d1 NULL NULL mno d2 NULL false (5 row(s) affected) 

Answer 2

 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 

Answer 3

What you require actually contradicts the nature of sql. In sql, the result is order independent.

Even if you find a way to get such a result (e.g. provided by Quassnoi), I would avoid doing this in SQL.