问以单行方式显示结果数据，无论是否。在Sql server中的列。

EN

WITH Ranked ( countryID, rnk, stateName )  
         AS ( SELECT countryID,
                     ROW_NUMBER() OVER( PARTITION BY countryID ORDER BY countryID ),
                     CAST( stateName AS VARCHAR(8000) ) 
                FROM state_master),
AnchorRanked ( countryID, rnk, stateName )  
         AS ( SELECT countryID, rnk, stateName 
                FROM Ranked
               WHERE rnk = 1 ),
RecurRanked ( countryID, rnk, stateName ) 
         AS ( SELECT countryID, rnk, stateName 
                FROM AnchorRanked
               UNION ALL 
              SELECT Ranked.countryID, Ranked.rnk,
                     RecurRanked.stateName + ', ' + Ranked.stateName
                FROM Ranked
               INNER JOIN RecurRanked 
                  ON Ranked.countryID = RecurRanked.countryID 
                 AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT countryName, MAX( stateName ) 
  FROM RecurRanked
  INNER JOIN country_master on RecurRanked.countryID = country_master.countryID
 GROUP BY countryName;

Select t.countryName , isnull(Stuff((SELECT ', ' + StateName
          FROM state_master where countryID = t.countryID
          ORDER BY StateName
          For XML PATH ('')),1,1,''),'') as c
from country_master  t 

select c.countryName,
       stuff((select ' '+s.StateName
              from state_master as s
              where s.countryID = c.countryID
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')
from country_master as c