How can I retrieve data from the following table by using ADF lookup activity and want to get the result as below? Thanks

You can use the a conditional aggregation directly in your ADF Lookup activity : SELECT id, MAX(CASE WHEN address = '100 street' THEN occupied_time END) AS [100 street], MAX(CASE WHEN address = '200 road' THEN occupied_time END) AS [200 road] FROM YourTableName WHERE id = 3 GROUP BY id; If you want to remove the filter for id = 3 so that the query produces results for all id values : SELECT id, MAX(CASE WHEN address = '100 street' THEN occupied_time END) AS [100 street], MAX(CASE WHEN address = '200 road' THEN occupied_time END) AS [200 road] FROM YourTableName GROUP BY id;

How to split rows and columns by using ADF?

Accepted answer

Amira Bedhiafi 15,676 Reputation points

2024-04-15T16:09:10.8966667+00:00
You can use the a conditional aggregation directly in your ADF Lookup activity :

SELECT id, MAX(CASE WHEN address = '100 street' THEN occupied_time END) AS [100 street], MAX(CASE WHEN address = '200 road' THEN occupied_time END) AS [200 road] FROM YourTableName WHERE id = 3 GROUP BY id;

If you want to remove the filter for id = 3 so that the query produces results for all id values :

SELECT id, MAX(CASE WHEN address = '100 street' THEN occupied_time END) AS [100 street], MAX(CASE WHEN address = '200 road' THEN occupied_time END) AS [200 road] FROM YourTableName GROUP BY id;
Please sign in to rate this answer.

1 person found this answer helpful.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Ramesh D 1 Reputation point

2024-04-20T14:40:18.9366667+00:00

Can we use copy activity to store the result in Blog or SQL tables?

Zhu, Yueli YZ [NC] 235 Reputation points

2024-04-26T14:50:54.6133333+00:00

I used the query you provided in the ADF lookup activity, but when the result is null, the output json could not display anything about first row. I got the result below

actually, I would like to have the result as the following

Could you please help?Thanks

Amira Bedhiafi 15,676 Reputation points

2024-04-29T13:15:44.7766667+00:00

Can you provide more details ?

Zhu, Yueli YZ [NC] 235 Reputation points

2024-04-29T15:20:33.59+00:00

for example, original table below does not contain id = 3

if I use query the following queries to get id 3, there are two different results:

Following are the corresponding queries and how to adjust the second query and get the "NULL" in the result. Thanks!

SELECT ISNULL( (SELECT address FROM test WHERE id = 3 ), null) AS [100 street] SELECT id, ISNULL(MAX(CASE WHEN address = '100 street' THEN occupied_time END),null) AS [100 street], ISNULL(MAX(CASE WHEN address = '200 road' THEN occupied_time END),null) AS [200 road] FROM test WHERE id = 3 GROUP BY id;
Sign in to comment

How to split rows and columns by using ADF?

0 additional answers