Split one column into multiple column

@Steven Henry Suhendra - Thanks for the question and using MS Q&A platform.

To split a single column into multiple columns based on certain conditions, you can use CASE statements to populate each column separately. Here’s an example based on your provided table.

Your goal is to separate the "Department" column into "Department" and "Special Department" columns. You can use SQL like this:

SELECT 
    No,
    Code,
    CASE 
        WHEN Code IN ('D001', 'D002') THEN Department 
        ELSE NULL 
    END AS Department,
    CASE 
        WHEN Code IN ('SD001', 'SD002') THEN Department 
        ELSE NULL 
    END AS Special_Department
FROM 
    db.table;

1. The first CASE statement checks if the Code is either 'D001' or 'D002' and assigns the value to the "Department" column. If the code doesn’t match, it returns NULL.
2. The second CASE statement checks if the Code is either 'SD001' or 'SD002' and assigns the value to the "Special_Department" column. If the code doesn’t match, it returns NULL.

This approach will give you the output you’re looking for, with "Department" values in one column and "Special Department" values in another.

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Hi @Steven Henry Suhendra

Try this code:

SELECT [No]
      ,[Code]
	  ,CASE WHEN Code LIKE 'D%' THEN Departement END AS Departement
	  ,CASE WHEN Code LIKE 'SD%' THEN Departement END AS Special_Departement
FROM db.table;

Best regards,

Cosmog

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".