written Query to this scnario

Hi!

this one is also works ...please don't forget to mark it as answer...

SELECT STATE,
       SUBSTRING(
                   (SELECT(', '+ Cities)
                    FROM Cities t2
                    WHERE t1.Code =t2.Code
                    ORDER BY STATE, Cities
                    FOR XML Path ('')),3,1000)
FROM Cities t1
GROUP BY STATE

Hi try this,
DECLARE @table AS TABLE
  (
  [state] VARCHAR(64) ,
  city VARCHAR(512)
  )
INSERT  INTO @table
VALUES
  ( 'AP', 'hyderabad' ),
  ( 'AP', 'thirupathi' ),
  ( 'AP', 'vijag' ),
  ( 'KT', 'bangalore' ),
  ( 'KT', 'mysure' ),
  ( 'TN', 'chennai' ),
  ( 'TN', 'coembattore' )

SELECT DISTINCT
  [state] ,
  ( SELECT DISTINCT
  city + ','
  FROM
  @table st
  WHERE
  st.state = t.state
  FOR
  XML PATH('')
  ) AS city
FROM
  @table AS t

please accept the answer if it help you