Post reply

Group By ..Values into a single row

  • 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:

    StateName CityName

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

    NJ 1. EDISON 2. PARSIPPANY 3. METROPARK 4. PRINCTON 5. HAMILTON 6. ISELIN 7. PATERSON 8. PARAMUS

    VA 1. mClEAN 2. FAIRFAX 3. ARLINGTON 4. STAUNTON 5. NEWCITY 6. OLDCITY

    FL 1. MIAMI 2. TAMPA 3. ORLANDO 4. JACKSONVILLE 5. NEW 6. OLD

  • 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!

  • 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!

  • February 19, 2013 at 11:38 am

    #1589325

    Thank You!...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply