transpose row data into columns

Question

Hope I'm asking this correctly. I have a table in this format:

I need to have the Unique ID and the account numbers from both stores all in the same record. Like this:

How can I accomplish this in SQL? Is this some kind of PIVOT or UNPIVOT? What would the syntax be?

Answer 1

SELECT "Unique Patron ID",
        MIN(CASE WHEN Store = 'STORE_1' THEN Account# END) AS Store1_Acct,
        MIN(CASE WHEN Store = 'STORE_2' THEN Account# END) AS Store2_Acct
FROM  tbl
GROUP BY "Unique Patron ID"

You could also use the PIVOT operator for this, but the pattern above is more flexible.

Answer 2

Hi @Dom

Try this query using PIVOT:

SELECT * 
FROM YourTable 
PIVOT (MAX([Account#]) FOR STORE IN([STORE_1],[STORE_2])) AS P

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".