Convert columns values into multiple records

Question

In SQL Server 2008, I have a table with following columns and data

Date    Name    Colors  Color1  Color2  Color3
Nov01   John    Red
Nov02   Mike    Green   Blue    Grey
Nov03   Melissa Yellow  Orange
Nov10   Rita    Pink    Red

I want to make new table or change above table with data shown as

Date    Name    Colors
Nov01   John    Red
Nov02   Mike    Green
Nov02   Mike    Blue
Nov02   Mike    Grey
Nov03   Melissa Yellow
Nov03   Melissa Orange
Nov10   Rita    Pink
Nov10   Rita    Red

Thanks

Answer 1

Or using PIVOT & UNPIVOT

SELECT  t.Date,
        unpvt.Name,
        unpvt.Color
FROM 
   (SELECT  Name, Colors, Color1, Color2, Color3
   FROM dbo.MYTABLE) p
UNPIVOT
   (Color FOR [Date] IN 
      (Colors, Color1, Color2, Color3)
)AS unpvt
Join dbo.MYTABLE t on t.[Name] = unpvt.[Name]
Where unpvt.Color != ''

Answer 2

Not exactly my most elegant answer but i think it will still work

Select Date, Name, Colors as Colors
Where Colors is not null
Union All
Select Date, Name, Color1 as Colors
Where Color1 is not null
Union All
Select Date, Name, Color2 as Colors
Where Color2 is not null
Union All
Select Date, Name, Color3 as Colors
Where Color3 is not null

Answer 3

You may be interested in UNPIVOT

SELECT [Date], [Name], [Colors]
FROM 
   (SELECT [Date], [Name], [Color1], [Color2], [Color3]
   FROM myColors) p
UNPIVOT
   ([Colors] FOR x IN 
      ([Color1], [Color2], [Color3])
) AS unpvt;