turn columns into rows? 2

[bdog2020]

I need to be able to take data that looks like this:

Item Location1 Location2 Location3 Location4
BC.01 East West
BC.02 West
BC.03 East West North

And display it like this: (which is how it should be stored)

Item Location
BC.01 East
BC.01 West
BC.02 West
BC.03 East
BC.03 West
BC.03 North

I know it's bad form data modeling-wise, but I'm curious if it can be done in sql.

 

[RiverGuy]

SELECT Item, Location1 AS Location FROM YourTable WHERE Location1 <> ''
UNION ALL
SELECT Item, Location2  FROM YourTable WHERE Location2 <> ''
UNION ALL
SELECT Item, Location3  FROM YourTable WHERE Location3 <> ''
UNION ALL
SELECT Item, Location4  FROM YourTable WHERE Location4 <> ''

 

[bdog2020]

wow, fantastic! THANK YOU!