select (SELECT (Case A1.AreaAbbr WHEN '' THEN A1.AreaName Else A1.AreaAbbr END)+ ' ' FROM AreaLang A1
WHERE (SELECT AreaNo FROM Area WHERE Area.AreaID= A1.AreaID) IN
(SUBSTRING((SELECT AreaNo FROM Area Where Area.AreaID=P.AreaID),1,1)+'000000000000000'
,SUBSTRING((SELECT AreaNo FROM Area Where Area.AreaID=P.AreaID),1,4)+'000000000000'
,SUBSTRING((SELECT AreaNo FROM Area Where Area.AreaID=P.AreaID),1,7)+'000000000'
,SUBSTRING((SELECT AreaNo FROM Area Where Area.AreaID=P.AreaID),1,10)+'000000'
,SUBSTRING((SELECT AreaNo FROM Area Where Area.AreaID=P.AreaID),1,13)+'000'
,(SELECT AreaNo FROM Area Where Area.AreaID=P.AreaID)) AND A1.LanguageID=1 --ORDER BY P.AreaNo
FOR XML PATH('')
)AS PICAreaName
FROM Area P Where P.AreaNo='2011026017012004'
