Splitting Values in one Column to Multiple Columns using CASE Expression Statement SQL Server

Sometimes, you would want to split values in a column into separate rows  in SQL server.  This is a simple guide.

Lets check the fields in the table we will be working with.

Let’s check the result:

We can see that the itemType field has different values wheres as the price and quantity all have the same values.
So even when you do a group by you still get a long list of each ordernumber falling on different rows instead of a single row as we intended with the group by

Let’s check the result:

Now lets use a case statement to flag the item type as 1 when the specific item type we are catering for is available and 2 when it is not available.
Let’s put the result in a temporary table which will we call #itemTypes

Now lets see which item type was present for each of our order ids , by taking the MAX number of each flag for each item type (those we flagged in the steps above)
We will put the result in a temporary table which we will call #itemTypesFlags

Data quality check if the flags have been assigned appropriately

Let’s check the result:

Now let’s assign the actual item-type names to the flags where the flag has one and 0 if that item-type was not bought by the customer.
We will put that in a temporary table which we will call #itemTypesFlagsNames

Now lets get our order ids and the item types all on one row

Let’s check the final result:

Get full code here:

Hope it helps you 🙂

People Who Read The Above Post Also Read This:

Basic SQL – DQL Command Structure Syntax – Quickest Way to Remember It! Get Numbers and Percentages from Tables – Microsoft Sql Server Great Quick Transact-SQL Server Tutorials – Quick Revision and General Understanding Rank a Place Visited By a Customer- Microsoft SQL Server – T-Transact