How to create new columns from rows value
up vote
0
down vote
favorite
I want to create new columns from rows value using spark sql 2.x and java 8 ; something like that :
Original :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX |
--------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 |
1234 | JILL | HILL | 10/10/1980 | 1 |
5678 | JAMES | PIKE | 05/05/1950 | 0 |
5678 | JOAN | MAY | 04/04/1955 | 1 |
expected results :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2
---------------------------------------------------------------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1
5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1
Thank you
java sql apache-spark
asked Nov 9 at 9:29
Mks
85118
add a comment |
up vote
0
down vote
favorite
I want to create new columns from rows value using spark sql 2.x and java 8 ; something like that :
Original :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX |
--------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 |
1234 | JILL | HILL | 10/10/1980 | 1 |
5678 | JAMES | PIKE | 05/05/1950 | 0 |
5678 | JOAN | MAY | 04/04/1955 | 1 |
expected results :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2
---------------------------------------------------------------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1
5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1
Thank you
java sql apache-spark
asked Nov 9 at 9:29
Mks
85118
What's your dbms?
– D-Shih
Nov 9 at 9:34
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to create new columns from rows value using spark sql 2.x and java 8 ; something like that :
Original :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX |
--------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 |
1234 | JILL | HILL | 10/10/1980 | 1 |
5678 | JAMES | PIKE | 05/05/1950 | 0 |
5678 | JOAN | MAY | 04/04/1955 | 1 |
expected results :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2
---------------------------------------------------------------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1
5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1
Thank you
java sql apache-spark
asked Nov 9 at 9:29
Mks
85118
I want to create new columns from rows value using spark sql 2.x and java 8 ; something like that :
Original :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX |
--------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 |
1234 | JILL | HILL | 10/10/1980 | 1 |
5678 | JAMES | PIKE | 05/05/1950 | 0 |
5678 | JOAN | MAY | 04/04/1955 | 1 |
expected results :
POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2
---------------------------------------------------------------------------------------------------------------
1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1
5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1
Thank you
java sql apache-spark
java sql apache-spark
asked Nov 9 at 9:29
Mks
85118
asked Nov 9 at 9:29
Mks
85118
asked Nov 9 at 9:29
Mks
85118
asked Nov 9 at 9:29
Mks
85118
asked Nov 9 at 9:29
Mks
85118
85118
What's your dbms?
– D-Shih
Nov 9 at 9:34
add a comment |
What's your dbms?
– D-Shih
Nov 9 at 9:34
What's your dbms?
– D-Shih
Nov 9 at 9:34
What's your dbms?
– D-Shih
Nov 9 at 9:34
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You can try to use condition aggregate function.
Query #1
SELECT POLICY_NO,
MAX(CASE WHEN SEX= 0 THEN FIRST_NAME END) FIRST_NAME,
MAX(CASE WHEN SEX= 0 THEN LAST_NAME END) LAST_NAME,
MAX(CASE WHEN SEX= 0 THEN DOB END) DOB,
MAX(CASE WHEN SEX= 0 THEN SEX END) SEX,
MAX(CASE WHEN SEX= 1 THEN FIRST_NAME END) FIRST_NAME2,
MAX(CASE WHEN SEX= 1 THEN LAST_NAME END) LAST_NAME2,
MAX(CASE WHEN SEX= 1 THEN DOB END) DOB2,
MAX(CASE WHEN SEX= 1 THEN SEX END) SEX2
FROM T
GROUP BY POLICY_NO;
| POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2 |
| --------- | ---------- | --------- | ---------- | --- | ----------- | ---------- | ---------- | ---- |
| 1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1 |
| 5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1 |
View on DB Fiddle
answered Nov 9 at 9:32
D-Shih
24.1k61331
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You can try to use condition aggregate function.
Query #1
SELECT POLICY_NO,
MAX(CASE WHEN SEX= 0 THEN FIRST_NAME END) FIRST_NAME,
MAX(CASE WHEN SEX= 0 THEN LAST_NAME END) LAST_NAME,
MAX(CASE WHEN SEX= 0 THEN DOB END) DOB,
MAX(CASE WHEN SEX= 0 THEN SEX END) SEX,
MAX(CASE WHEN SEX= 1 THEN FIRST_NAME END) FIRST_NAME2,
MAX(CASE WHEN SEX= 1 THEN LAST_NAME END) LAST_NAME2,
MAX(CASE WHEN SEX= 1 THEN DOB END) DOB2,
MAX(CASE WHEN SEX= 1 THEN SEX END) SEX2
FROM T
GROUP BY POLICY_NO;
| POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2 |
| --------- | ---------- | --------- | ---------- | --- | ----------- | ---------- | ---------- | ---- |
| 1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1 |
| 5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1 |
View on DB Fiddle
answered Nov 9 at 9:32
D-Shih
24.1k61331
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
add a comment |
up vote
1
down vote
accepted
You can try to use condition aggregate function.
Query #1
SELECT POLICY_NO,
MAX(CASE WHEN SEX= 0 THEN FIRST_NAME END) FIRST_NAME,
MAX(CASE WHEN SEX= 0 THEN LAST_NAME END) LAST_NAME,
MAX(CASE WHEN SEX= 0 THEN DOB END) DOB,
MAX(CASE WHEN SEX= 0 THEN SEX END) SEX,
MAX(CASE WHEN SEX= 1 THEN FIRST_NAME END) FIRST_NAME2,
MAX(CASE WHEN SEX= 1 THEN LAST_NAME END) LAST_NAME2,
MAX(CASE WHEN SEX= 1 THEN DOB END) DOB2,
MAX(CASE WHEN SEX= 1 THEN SEX END) SEX2
FROM T
GROUP BY POLICY_NO;
| POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2 |
| --------- | ---------- | --------- | ---------- | --- | ----------- | ---------- | ---------- | ---- |
| 1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1 |
| 5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1 |
View on DB Fiddle
answered Nov 9 at 9:32
D-Shih
24.1k61331
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You can try to use condition aggregate function.
Query #1
SELECT POLICY_NO,
MAX(CASE WHEN SEX= 0 THEN FIRST_NAME END) FIRST_NAME,
MAX(CASE WHEN SEX= 0 THEN LAST_NAME END) LAST_NAME,
MAX(CASE WHEN SEX= 0 THEN DOB END) DOB,
MAX(CASE WHEN SEX= 0 THEN SEX END) SEX,
MAX(CASE WHEN SEX= 1 THEN FIRST_NAME END) FIRST_NAME2,
MAX(CASE WHEN SEX= 1 THEN LAST_NAME END) LAST_NAME2,
MAX(CASE WHEN SEX= 1 THEN DOB END) DOB2,
MAX(CASE WHEN SEX= 1 THEN SEX END) SEX2
FROM T
GROUP BY POLICY_NO;
| POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2 |
| --------- | ---------- | --------- | ---------- | --- | ----------- | ---------- | ---------- | ---- |
| 1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1 |
| 5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1 |
View on DB Fiddle
answered Nov 9 at 9:32
D-Shih
24.1k61331
You can try to use condition aggregate function.
Query #1
SELECT POLICY_NO,
MAX(CASE WHEN SEX= 0 THEN FIRST_NAME END) FIRST_NAME,
MAX(CASE WHEN SEX= 0 THEN LAST_NAME END) LAST_NAME,
MAX(CASE WHEN SEX= 0 THEN DOB END) DOB,
MAX(CASE WHEN SEX= 0 THEN SEX END) SEX,
MAX(CASE WHEN SEX= 1 THEN FIRST_NAME END) FIRST_NAME2,
MAX(CASE WHEN SEX= 1 THEN LAST_NAME END) LAST_NAME2,
MAX(CASE WHEN SEX= 1 THEN DOB END) DOB2,
MAX(CASE WHEN SEX= 1 THEN SEX END) SEX2
FROM T
GROUP BY POLICY_NO;
| POLICY_NO | FIRST_NAME | LAST_NAME | DOB | SEX | FIRST_NAME2 | LAST_NAME2 | DOB2 | SEX2 |
| --------- | ---------- | --------- | ---------- | --- | ----------- | ---------- | ---------- | ---- |
| 1234 | JOHN | HILL | 01/01/1978 | 0 | JILL | HILL | 10/10/1980 | 1 |
| 5678 | JAMES | PIKE | 05/05/1950 | 0 | JOAN | MAY | 04/04/1955 | 1 |
View on DB Fiddle
answered Nov 9 at 9:32
D-Shih
24.1k61331
answered Nov 9 at 9:32
D-Shih
24.1k61331
answered Nov 9 at 9:32
D-Shih
24.1k61331
answered Nov 9 at 9:32
D-Shih
24.1k61331
24.1k61331
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
add a comment |
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
Thank you D-Shih. Any idea to make it dynamic ( for any input )
– Mks
Nov 9 at 9:48
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
@Mks If you are using a programming language then just include identified grouping column. But if you have more than one person as in this case sharing a policy number then you will have more columns. That I believe may not be possible programmatically. You may need to make the additional columns as an array.
– Nikhil
Nov 9 at 12:40
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
Thank you @Nikhil. I will try this
– Mks
Nov 9 at 14:45
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53223030%2fhow-to-create-new-columns-from-rows-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What's your dbms?
– D-Shih
Nov 9 at 9:34