How to split different rows into different columns

Question

How to split different rows into different columns

Zhu, Yueli YZ [NC] 280

I need to retrieve data from azure sql database through ADF. The original table is as following

User's image

I would like to get the output as below

User's image

What kind query can I use in one lookup activity?

Thanks

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Amira Bedhiafi 41,111 Volunteer Moderator

You can use the a SQL query to achieve the desired result, I just assumed you know there are at most 2 addresses per id. If there are more, you'll need to extend the pattern:

SQLCopy


SELECT

    id,

    MAX(CASE WHEN rn = 1 THEN address END) AS address_1,

    MAX(CASE WHEN rn = 2 THEN address END) AS address_2

FROM (

    SELECT 

        id,

        address,

        ROW_NUMBER() OVER (PARTITION BY id ORDER BY address) as rn

    FROM YourTableName

) AS sub

GROUP BY id;

Then you can use the above SQL query directly in your ADF Lookup activity :

In your pipeline, add a Lookup activity.
Configure it to use a dataset linked to your Azure SQL Database.
Enter the SQL query as provided above.

Zhu, Yueli YZ [NC] 280 Reputation points

2024-04-15T14:35:54.28+00:00

Thanks a lot for your quick response. This works well. But if I have more rows but do not need all of them. for example, the original table as below

But I need the result of id 1 like below

I used the where clause, but it got errors. Thanks

Amira Bedhiafi 41,111 Volunteer Moderator

If you want to filter by ID, use the following query :


SELECT
id,

MAX(CASE WHEN rn = 1 THEN address END) AS address_1,

MAX(CASE WHEN rn = 2 THEN address END) AS address_2
FROM (


SELECT 
id,

address,

ROW_NUMBER() OVER (PARTITION BY id ORDER BY address) as rn

FROM YourTableName

) AS sub

WHERE id = 2

GROUP BY id;

Zhu, Yueli YZ [NC] 280 Reputation points

2024-04-15T15:24:45.16+00:00

Thanks a lot. What if I adjust the original table as following

and want to get the result as below

Thanks again
Amira Bedhiafi 41,111 Reputation points Volunteer Moderator

2024-04-15T15:49:55.3266667+00:00

Can you please close this question and then post a new one with the new requirements ?
Zhu, Yueli YZ [NC] 280 Reputation points

2024-04-15T15:58:33.01+00:00

Just created a new question as you suggested. Thanks!

Share via

How to split different rows into different columns

0 additional answers

Your answer