Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38

    Unanswered: concatenate rows using for xml path and new line feed carriage

    Good day Guys

    i have this code

    --------

    Create TABLE #tempCityState (State Varchar(5), City Varchar(50))

    Insert Into #tempCityState
    Select 'CO', 'Denver' Union
    Select 'CO', 'Teluride' Union
    Select 'CO', 'Vail' Union
    Select 'CO', 'Aspen' Union
    Select 'CA', 'Los Anggeles' Union
    Select 'CA', 'Hanford' Union
    Select 'CA', 'Fremont' Union
    Select 'AK', 'Wynne' Union
    Select 'AK', 'Nashville'


    Select Distinct State, (Select Stuff((Select ',' + City
    From #tempCityState
    Where State = t.State
    FOR Xml Path('')),1,1,'')) AS Cities
    From #tempCityState t

    -- or tentatively

    --Select Distinct State, (Select Substring((Select ',' + City
    -- From #tempCityState
    -- Where State = t.State
    -- FOR Xml Path('')),2,200000)) AS Cities
    --From #tempCityState t


    Drop table #tempCityState

    -----------

    it gives this results:

    State City
    ------------------
    AK Nashville,Wynne
    CA Fremont,Hanford,Los Anggeles
    CO Aspen,Denver,Teluride,Vail


    but i want output like this (3 rows exactly with new line feed carriage after each item)

    3 | Flickr - Photo Sharing!


    Thanks in adv. for your replies
    Benone

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't see your photo, as Flickr is blocked at my office (others may have the same problem).
    I suspect you need different parameters on your FOR XML clause.
    Try this: FOR Xml Path(''), type
    ...or this: FOR Xml Path(''), elements, type
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use SSRS (SQL Server Reporting Services). You just need to hide the group headers after the first line, and can find more details at Working with Group Features (Reporting Services)

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38
    Quote Originally Posted by blindman View Post
    Can't see your photo, as Flickr is blocked at my office (others may have the same problem).
    I suspect you need different parameters on your FOR XML clause.
    Try this: FOR Xml Path(''), type
    ...or this: FOR Xml Path(''), elements, type
    -----------

    I tried below but didn't work ... i will look for front end formatting solution

    1)
    Stuff
    (
    (
    Select ',' + City As [text()]
    From #tempCityState
    Where State = t.State
    For Xml Path('')
    )
    , 1, 1, ''
    ) As Cities,

    ------------

    2)

    Stuff
    (

    (
    Select ',' + City
    From #tempCityState
    Where State = t.State
    For Xml Path(''), Type
    ).value('.', 'varchar(max)')

    ,1,1,''
    ) As Cities
    Benone

  5. #5
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38
    Quote Originally Posted by Pat Phelan View Post
    Use SSRS (SQL Server Reporting Services). You just need to hide the group headers after the first line, and can find more details at Working with Group Features (Reporting Services)

    -PatP
    --------------


    the sql results is sent out to an asp.net gridview and not ssrs
    Thanks anyway
    Benone

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sure that I'm gonna burn for this, but:
    Code:
    CREATE TABLE #tempCityState 
    (  State    VARCHAR(5)
    ,  City     VARCHAR(50)
    )
    
    INSERT INTO #tempCityState
       SELECT 'CO', 'Denver' UNION
       SELECT 'CO', 'Teluride' UNION
       SELECT 'CO', 'Vail' UNION
       SELECT 'CO', 'Aspen' UNION
       SELECT 'CA', 'Los Anggeles' UNION
       SELECT 'CA', 'Hanford' UNION
       SELECT 'CA', 'Fremont' UNION
       SELECT 'AK', 'Wynne' UNION
       SELECT 'AK', 'Nashville'
    
    SELECT DISTINCT State
    ,  Replace((SELECT Stuff(
          (SELECT ',' + z.City
             FROM #tempCityState AS z
             WHERE z.State = t.State
             FOR Xml Path(''))
    ,		 1, 1, '')
       ), ',', Char(13) + Char(10)) AS Cities
       FROM #tempCityState t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38
    i used a templatefield solution in the asp.net and got my formatting working .. but will anyway check out your tsql solution and confirm whether it has worked or not

    Thanks for your response
    Benone

  8. #8
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38
    Quote Originally Posted by Pat Phelan View Post
    I'm sure that I'm gonna burn for this, but:
    Code:
    CREATE TABLE #tempCityState 
    (  State    VARCHAR(5)
    ,  City     VARCHAR(50)
    )
    
    INSERT INTO #tempCityState
       SELECT 'CO', 'Denver' UNION
       SELECT 'CO', 'Teluride' UNION
       SELECT 'CO', 'Vail' UNION
       SELECT 'CO', 'Aspen' UNION
       SELECT 'CA', 'Los Anggeles' UNION
       SELECT 'CA', 'Hanford' UNION
       SELECT 'CA', 'Fremont' UNION
       SELECT 'AK', 'Wynne' UNION
       SELECT 'AK', 'Nashville'
    
    SELECT DISTINCT State
    ,  Replace((SELECT Stuff(
          (SELECT ',' + z.City
             FROM #tempCityState AS z
             WHERE z.State = t.State
             FOR Xml Path(''))
    ,		 1, 1, '')
       ), ',', Char(13) + Char(10)) AS Cities
       FROM #tempCityState t
    -PatP

    unfortunately like others multiple solutions i hv tried so far it didn't work .... i believe the solution definitely lies at the front end ...
    Benone

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •