topic Re: I am looking to use the pivot function with Spark SQL (not Python) in Data Engineering

I am looking to use the pivot function with Spark SQL (not Python)

nickg — Wed, 30 Mar 2022 18:16:10 GMT

Hello. I am trying to using the Pivot function for email addresses. This is what I have so far:

Select fname, lname, awUniqueID, Email1, Email2

From xxxxxxxx

Pivot (

count(Email) as Test

For Email

In (1 as Email1, 2 as Email2)

)

I get everything I need except Email1 and Email2 have null values instead of actual email addresses. I just need a little push to get me over the edge. Thanks.

Re: I am looking to use the pivot function with Spark SQL (not Python)

Hubert-Dudek — Wed, 30 Mar 2022 18:38:43 GMT

Please add example source data and desired output.

"For Email" should be a list of columns to replace. In the source, we have Email1, Email2, so instead, it can be FOR (email1, email2)
count the same probably needed is count(awUniqueID)
Is 1 and 2 values in Email1 and Email2? If not, another solution is necessary, but it is hard to help without an example

Re: I am looking to use the pivot function with Spark SQL (not Python)

nickg — Wed, 30 Mar 2022 18:46:23 GMT

source data:

fname lname awUniqueID Email

John Smith 22 jsmith@gmail.com

JODI JONES 22 jsmith@live.com

Desired output:

fname lname awUniqueID Email1 Email2

John Smith 22 jsmith@gmail.com jsmith@live.com

Re: I am looking to use the pivot function with Spark SQL (not Python)

nickg — Wed, 30 Mar 2022 18:47:45 GMT

Oops. The second name in the source data should be John Smith as well.

Re: I am looking to use the pivot function with Spark SQL (not Python)

Hubert-Dudek — Wed, 30 Mar 2022 19:18:54 GMT

Just create a copy of dataframe (or temporary view) rename the Email column to Email2 and than join on with source awUniqueID.

Re: I am looking to use the pivot function with Spark SQL (not Python)

nickg — Thu, 31 Mar 2022 12:50:36 GMT

Thanks Hubert. I did that and it worked. I still want to get 'Pivot' to work as well.

Re: I am looking to use the pivot function with Spark SQL (not Python)

nickg — Tue, 26 Apr 2022 16:48:15 GMT

Hi Kaniz,

Thanks for your message. I was able to make it work with the workaround that Hubert provided. I would eventually like to make it work with the 'Pivot' command. I have not revisited it and tested the 'Pivot' command as I was on vacation for a couple of weeks.