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.

select
[orderID]
,[itemType]
,[price]
,[quantity]
from 
[Antrak].[dbo].[ClothingSale]

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

select
[orderID]
,[itemType]
,[price]
,[quantity]
from 
[Antrak].[dbo].[ClothingSale]
group by 
[orderID]
,[itemType]
,[price]
,[quantity]

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

SELECT 
orderid
,case when itemType = 'casual' then 1 else 0 end as 'casual'
,case when itemType = 'formal' then 1 else 0 end as 'formal'
,case when itemType = 'accessory' then 1 else 0 end as 'accessory'
,case when itemType = 'kids' then 1 else 0 end as 'kids'
,case when itemType = 'leather' then 1 else 0 end as 'leather'
,case when itemType = 'ladies' then 1 else 0 end as 'ladies'
into #itemTypes
FROM 
[Antrak].[dbo].[ClothingSale]
group by 
orderid,
case when itemType = 'casual' then 1 else 0 end
,case when itemType = 'formal' then 1 else 0 end
,case when itemType = 'accessory' then 1 else 0 end
,case when itemType = 'kids' then 1 else 0 end
,case when itemType = 'leather' then 1 else 0 end
,case when itemType = 'ladies' then 1 else 0 end

 

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

select 
orderID
,MAX(casual) AS casual
,MAX(formal) AS formal
,MAX(accessory) AS accessory
,MAX(kids) AS kids
,MAX(leather) AS leather
,MAX(ladies) AS ladies
into #itemTypesFlags
from 
#itemTypes 
group by 
orderID

 

Data quality check if the flags have been assigned appropriately

select * from #itemTypesFlags

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

select 
orderid,
case when casual = 1 then 'casual' else '0' end as 'casual'
,case when formal = 1 then 'formal' else '0' end as 'formal'
,case when accessory = 1 then 'accessory' else '0' end as 'accessory'
,case when kids = 1 then 'kids' else '0' end as 'kids'
,case when leather = 1 then 'leather' else '0' end as 'leather'
,case when ladies = 1 then 'ladies' else '0' end as 'ladies'
into #itemTypesFlagsNames
from 
#itemTypesFlags
group by 
orderid,
case when casual = 1 then 'casual' else '0' end 
,case when formal = 1 then 'formal' else '0' end 
,case when accessory = 1 then 'accessory' else '0' end 
,case when kids = 1 then 'kids' else '0' end 
,case when leather = 1 then 'leather' else '0' end 
,case when ladies = 1 then 'ladies' else '0' end

 

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

SELECT 
cs.[orderID]
,[price]
,[quantity]
, casual 
, formal 
, accessory 
, kids
, leather 
, ladies 
FROM 
[Antrak].[dbo].[ClothingSale] as cs
inner join
#itemTypesFlagsNames as ifn
on cs.orderID = ifn.orderID
group by cs.[orderID]
,[price]
,[quantity]
, casual 
, formal 
, accessory 
, kids
, leather 
, ladies

 

--refresh temporary tables
drop table #itemTypes
drop table #itemTypesFlags
drop table #itemTypesFlagsNames

Let’s check the final result:

Get full code here:

Hope it helps you 🙂