Makes columns as row values?

Transact-SQL
1

Hi Friends,

I have a table which looks like this

CustomerName           ALias        ProductCode1            ProductCode2    
John                   Jon              test1

James                  Jam                                       Test2

Jack                     JAc                test3

What I now need is a result set in the following way:
Customer ProductCode Indicator
John_Jon_ProductCode1 ProductCOde1 Test1
John_Jon_ProductCode2 ProductCOde2 null
James_Jam_ProductCode1 ProductCOde1 null
James_Jam_ProductCode2 ProductCOde2 Test2
JAck_Jac_ProductCode1 ProductCOde1 Test3
JAck_Jac_ProductCode1 ProductCode2 null

So basically i want to use the productcode columns as row values and each customer will have as many entries as the count of ProductCode columns. How can I achive this?

Thank You

2

declare @test table (CustomerName varchar(255),ALias varchar(255),ProductCode1 varchar(255),ProductCode2 varchar(255))

insert into @test values
('John','Jon','test1',null)
,('James','Jam',null,'test2')
,('Jack','Joc','test3',null)

--Your Original
select * from @test

--Expected
select
CustomerName+'_'+ALias as Customer
,ProductCode
,Indicator
from (
select * from @test
unpivot(
Indicator for ProductCode in (ProductCode1,ProductCode2)
)unpvt
)main

1 Like
3

Thank you Muj9.

4

No problem and if you wanted to include nulls then

declare @test table (CustomerName varchar(255),ALias varchar(255),ProductCode1 varchar(255),ProductCode2 varchar(255))

insert into @test values
('John','Jon','test1','null')
,('James','Jam','null','test2')
,('Jack','Joc','test3','null')

--Your Original
select * from @test

--Expected
select
CustomerName+''+ALias+''+ProductCode as Customer
,ProductCode
,Indicator
from (
select * from @test
unpivot(
Indicator for ProductCode in (ProductCode1,ProductCode2)
)unpvt
)main