Hi laurie-789651,

Based on your suggestion, it got me thinking, eventually, i did some hard-coding to resolve it, i don't know if your answer can be more automatic, but this code gives me exactly what i want.

Thanks

Code Below:

with cte as (

select Count(*) as Base, Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector

from

(

select Period, data, Identifier

from TestTable1

) z

pivot

(

max(data)

for Identifier in (Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector)

) piv

group by Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender, BusinessCategory, Location, Sector

)

Select cte.Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector

, b.Data as Question1

, c.data as Question2

, d.data as Question3

, e.data as Question4

from cte inner join TestTable1 b on b.Period = cte.Period and b.Identifier = 'QuestionNo1'

inner join TestTable1 c on c.Period = cte.Period and c.Identifier = 'QuestionNo2'

inner join TestTable1 d on d.Period = cte.Period and d.Identifier = 'QuestionNo3'

left join TestTable1 e on e.Period = cte.Period and e.Identifier = 'QuestionNo4' and e.data = case when d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) then e.Data

when not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and d.Data like 'Others Spec%' AND (NOT (RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) IN ('Metro Wireless', 'Metro Fibre', 'Internet', 'Longhaul [DLC]'))) then e.data

--(select distinct top(1) data from TestTable1 where not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and e.Period = cte.Period and Identifier = 'QuestionNo4' AND (NOT (RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) IN ('Metro Wireless', 'Metro Fibre', 'Internet', 'Longhaul [DLC]'))))

end