Group By ..Values into a single row

• etirem

  SSCommitted

  Points: 1655

  More actions

  February 19, 2013 at 10:47 am

  #394219

  I have the below SQL ..

  IF OBJECT_ID('TempDB..#tTestTB') IS NOT NULL

  DROP TABLE #tTestTB

  CREATE TABLE #tTestTB(

  [StateName] [varchar](2) NULL,

  [CityName] [varchar](25) NULL)

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'PA', 'PHILLY')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'PA', 'PITTSBURG')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'PA', 'WARREN')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'PA', 'UNION')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'EDISON')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'PARSIPPANY')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'METROPARK')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'PRINCTON')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'HAMILTON')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'ISELIN')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'PATERSON')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'NJ', 'PARAMUS')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'VA', 'mClEAN')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'VA', 'FAIRFAX')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'VA', 'ARLINGTON')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'VA', 'STAUNTON')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'VA', 'NEWCITY')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'VA', 'OLDCITY')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'FL', 'MIAMI')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'FL', 'TAMPA')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'FL', 'ORLANDO')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'FL', 'JACKSONVILLE')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'FL', 'NEW')

  INSERT INTO #tTestTB ( StateName,CityName) VALUES ( 'FL', 'OLD')

  SELECT * FROM #tTestTB

  DROP TABLE #tTestTB

  I need the output as:

  StateNameCityName

  PA 1. PHILLY 2. PITTSBURG 3. WARREN 4. UNION

  NJ 1. EDISON 2. PARSIPPANY3. METROPARK4. PRINCTON5. HAMILTON6. ISELIN7. PATERSON8. PARAMUS

  VA 1. mClEAN 2. FAIRFAX3. ARLINGTON4. STAUNTON5. NEWCITY6. OLDCITY

  FL 1. MIAMI2. TAMPA3. ORLANDO4. JACKSONVILLE5. NEW 6. OLD

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 19, 2013 at 11:31 am

  #1589320

  getting the cities as a list wasn't hard, but i'm having trouble numbering them inline; my row_number verisons not quite there yet, but here's the start of it:

  SELECT StateName,stuff(( SELECT ',' + CityName

  FROM #tTestTB s2

  WHERE s2.StateName= s1.StateName --- must match GROUP BY below

  ORDER BY CityName

  FOR XML PATH('')

  ),1,1,'') as [Cities]

  FROM #tTestTB s1

  GROUP BY s1.StateName --- without GROUP BY multiple rows are returned

  ORDER BY s1.StateName

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 19, 2013 at 11:33 am

  #1589322

  ok not as hard as i thought: here you go:

  SELECT StateName,stuff(( SELECT ',' + CONVERT(varchar,RW) + '.' + CityName

  FROM (select row_number() over (partition by StateName order by cityname) As RW,* FROM #tTestTB) s2

  WHERE s2.StateName= s1.StateName --- must match GROUP BY below

  ORDER BY CityName

  FOR XML PATH('')

  ),1,1,'') as [Cities]

  FROM #tTestTB s1

  GROUP BY s1.StateName --- without GROUP BY multiple rows are returned

  ORDER BY s1.StateName

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• etirem

  SSCommitted

  Points: 1655

  More actions

  February 19, 2013 at 11:38 am

  #1589325

  Thank You!...