CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008pivot
user219628
user219628

Reputation: 3905

Convert columns values into multiple records

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

Upvotes: 2

Views: 569

Answers (3)

bobs
bobs

Reputation: 22184

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;

Upvotes: 1

codingbadger
codingbadger

Reputation: 43984

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 != ''

Upvotes: 3

Jagmag
Jagmag

Reputation: 10356

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

Upvotes: 1

Related Questions