SQL Server row formatting using PIVOT, UNPIVOT

Readers are advised to know that the technique described in a previous artivle: SQL Server string column concatenation is also used in here.

Suppose we a table t1 as follows:

ID	Property1	Property2	Property3	Property4
17	v1,v3	v2	v3,v5	v2,v4,v6

Another table t2 as follows:

Value	Description
v1	eye
v2	mouth
v3	ear
v4	nose
v5	neck
v6	hair

And we would like our output to be like this instead:

ID	Property1	Property2	Property3	Property4
17	eye,ear	mouth	ear,neck	mouth,nose,hair

That is, the list of values from t1 has to be translated into more readable contents according to the correspondences in t2. Here are the steps:

1. UNPIVOT t1, converting rows into columns
2. insert converted data, combining with the corresponding values from t2, into temporary table #tmp
3. perform string concatenation
4. PIVOT the resulting table back to it original form

Here are the SQL commands for step 1:

select property, value
from
(
select * from t1 where ID = '17'
) AS p
UNPIVOT
(
value
for property
in (Property1, Property2, Property3, Property4)
) AS unpvt

resulting table:

property	value
Property1	v1,v3
Property2	v2
Property3	v3,v5
Property4	v2,v4,v6

Here are the complete SQL commands for step 1 and 2:

create table #tmp(tmp_property varchar(10), tmp_description varchar(10), tmp_list varchar(100))

insert into #tmp(tmp_property, tmp_description, tmp_list)
select property, Description, NULL
from
(
select property, value
from
(
select * from t1 where ID = '17'
) AS p
UNPIVOT
(
value
for property
in (Property1, Property2, Property3, Property4)
) AS unpvt
) AS tmp
inner join t2 on value like '%' + t2.Description + '%'

resulting table:

tmp_property	tmp_description	tmp_list
Property1	eye	NULL
Property1	ear	NULL
Property2	mouth	NULL
Property3	ear	NULL
Property3	neck	NULL
Property4	mouth	NULL
Property4	nose	NULL
Property4	hair	NULL

step 3:

update #tmp
set @list = tmp_list = (CASE WHEN @last <> tmp_property THEN tmp_description ELSE @list + ',' + tmp_description END), @last = tmp_property

resulting table:

tmp_property	tmp_description	tmp_list
Property1	eye	eye
Property1	ear	eye,ear
Property2	mouth	mouth
Property3	ear	ear
Property3	neck	ear,neck
Property4	mouth	mouth
Property4	nose	mouth,nose
Property4	hair	mouth,nose,hair

step 4:

select *
from
(
select '17' as ID, tmp_property, max(tmp_list) as tmp_list from #tmp_default group by tmp_property
) as p
PIVOT
(
max(tmp_list)
for tmp_property
in (Property1, Property2, Property3, Property4)
) as pvt

resulting table:

ID	Property1	Property2	Property3	Property4
17	eye,ear	mouth	ear,neck	mouth,nose,hair