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

[nickg]

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.

[Hubert-Dudek · Accepted solution]

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

[nickg]

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

[nickg]

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

[Hubert-Dudek]

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

[nickg]

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

[Kaniz]

Hi @Nick Genova​ , How are you doing? Hope you were able to resolve your problem with @Hubert Dudek​ 's help.

[nickg]

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.