Dividing values from a single column into 2 based on a rule

Question

I am trying to create an SQL query to divide into 2 separate columns based on a rule. My table is as follows

Name   | Item ID  | Parent ID
-----  | -------- | ----------
A      | 1        | Null
B      | 2        | Null
C      | 3        | Null
D      | 4        | Null
A1     | 5        | 1
A2     | 6        | 1
A3     | 7        | 1
B1     | 8        | 2
B2     | 9        | 2
B3     | 10       | 2

Is it possible to have the values in the [Name] column be divided into 2 columns?

e.g. if the value of the [Parent ID] is Null, the value in [Name] column be in column 1 and if the value of [Parent ID] is 1, it would be placed in column 2?

Please advice and thanks in advance!

Which DBMS are you using? Postgres? Oracle? – May 22 '15 at 05:47 — , May 22 '15 at 05:47

score 4 · Answer 1 · answered May 22 '15 at 05:28

Use a CASE statement in the column list to check if ParentID is 1 or null.

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

create table YourTable
(
    Name varchar(10),
    ItemID int,
    ParentID int
 );

 insert into YourTable(Name, ItemID, ParentID) values('A', 1, null);
 insert into YourTable(Name, ItemID, ParentID) values('B', 2, null);
 insert into YourTable(Name, ItemID, ParentID) values('C', 3, null);
 insert into YourTable(Name, ItemID, ParentID) values('D', 4, null);
 insert into YourTable(Name, ItemID, ParentID) values('A1', 5, 1);
 insert into YourTable(Name, ItemID, ParentID) values('A2', 6, 1);
 insert into YourTable(Name, ItemID, ParentID) values('A3', 7, 1);
 insert into YourTable(Name, ItemID, ParentID) values('B1', 8, 2);
 insert into YourTable(Name, ItemID, ParentID) values('B2', 9, 2);
 insert into YourTable(Name, ItemID, ParentID) values('B3', 10, 2);

Query 1:

select case when ParentID is null then T.Name end as Column1,
       case when ParentID = 1 then T.Name end as Column2
from YourTable as T;

Results:

| column1 | column2 |
|---------|---------|
|       A |  (null) |
|       B |  (null) |
|       C |  (null) |
|       D |  (null) |
|  (null) |      A1 |
|  (null) |      A2 |
|  (null) |      A3 |
|  (null) |  (null) |
|  (null) |  (null) |
|  (null) |  (null) |

Dividing values from a single column into 2 based on a rule

1 Answers1