i have a table like :
ID snapdate createdate 123 2022-10-03 2022-12-29 123 2022-10-10 2022-02-01 246 2022-10-03 2022-02-04 246 2022-10-10 2022-1-11
i want to make snapdate as columns and corresponding values will be populated as below:
ID createdate(for 2022-10-03) createdate(for 2022-10-10) 123 2022-12-29 2022-02-01 246 2022-02-04 2022-1-11
i tried it in excel till now using vlookup but want to automate it using SQL. Any assistance please let me know.
CodePudding user response：
On your current sample data, the final query you're probably aiming for is something like this:
SELECT ID,
MAX(CASE WHEN snapdate='2022-10-03' THEN createdate END) AS '2022-10-03',
MAX(CASE WHEN snapdate='2022-10-10' THEN createdate END) AS '2022-10-10'
FROM mytable
GROUP BY ID;
For the dynamic requirement here, you can try using prepared statement.
In this example, I'll structure the query using two variables for better understanding and I'll include another option where everything is in a single query.
Variables:
SET @columns := NULL; /*for the columns*/
SET @sql := NULL; /*for the final query*/
The first thing here is to create the columns and assigning it to @columns:
SELECT GROUP_CONCAT(
DISTINCT CONCAT("MAX(CASE WHEN snapdate='", snapdate,"' THEN createdate END) AS '",snapdate,"'")
ORDER BY snapdate) INTO @columns
FROM mytable;
Then, combine that variable to make the final query and assign to @sql variable:
SELECT CONCAT("SELECT ID,",@columns,"
FROM mytable
GROUP BY ID") INTO @sql;
Prepare, execute then deallocate the statement:
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
The prepared statement below is the same thing as above but only using a single variable and a single query to create:
SET @sql := NULL;
SELECT CONCAT("SELECT ID,",GROUP_CONCAT(
DISTINCT CONCAT("MAX(CASE WHEN snapdate='", snapdate,"' THEN createdate END) AS '",snapdate,"'")
ORDER BY snapdate),"
FROM mytable
GROUP BY ID") INTO @sql
FROM mytable;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;