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:

    StateCity

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

    AKNashville,Wynne

    CAFremont,Hanford,Los Anggeles

    COAspen,Denver,Teluride,Vail

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

    http://www.flickr.com/photos/14261289@N02/8580154328/

    Thanks in adv. for your replies

  • I can't get to your image but can you just replace the comma with a carriage return?

    Select Distinct State, (Select Stuff((Select char(10) + City

    From #tempCityState

    Where State = t.State

    FOR Xml Path('')),1,1,'')) AS Cities

    From #tempCityState t

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Tks. tried that before logging this post but it didn't work ...

  • julesbenone (3/22/2013)


    Tks. tried that before logging this post but it didn't work ...

    What do you mean it didn't work? Remember I don't know what you want for final output. I have a feeling that you are wanting to have everything lined up nice and pretty? That is formatting which is best left to the front end.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Maybe this?

    Select Distinct State, (Select Stuff((Select '

    ' + City

    From #tempCityState

    Where State = t.State

    FOR Xml Path(''),TYPE).value('.','VARCHAR(1000)'),1,1,'')) AS Cities

    From #tempCityState t

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Mark-101232 (3/22/2013)


    Maybe this?

    Select Distinct State, (Select Stuff((Select '

    ' + City

    From #tempCityState

    Where State = t.State

    FOR Xml Path(''),TYPE).value('.','VARCHAR(1000)'),1,1,'')) AS Cities

    From #tempCityState t

    Yes, this is what I was going to suggest as well.

  • Thanks Sean ... yes its formatting and i do undertsand tsql is not the best approach for doing same but for technical reason i want to do it on the back end / the asp.net gridview that is being used also to populate results has not been helpfull as well

  • Tks for your adv Mark but i tried something quite the same without success .. below all options that i used so far

    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

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

    3)

    Stuff

    (

    (

    Select char(10) + City As [text()]

    From #tempCityState

    Where State = t.State

    For Xml Path('')

    )

    , 1, 1, ''

    ) As Cities,

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

    4)

    Stuff

    (

    (

    Select Char(10) + City

    From #tempCityState

    Where State = t.State

    For Xml Path(''), Type

    ).value('.', 'varchar(max)')

    ,1,1,''

    ) As Cities

    Thanks anyway

  • The following works, but in GRID mode you won't see it and in TEXT mode they won't line up under the Cities column.

    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;

    with States as (

    select distinct

    State

    from

    #tempCityState

    )

    select

    State,

    stuff((select '' + City + char(13) + char(10)

    from #tempCityState tcs

    where tcs.State = States.State

    order by tcs.City

    for xml path(''),TYPE).value('.','varchar(max)'),1,0,'') as Cities

    from

    States;

    Drop table #tempCityState

  • Trying to put the presentation in the application instead of in SQL is the best approach. You're never going to get everything all nicely lined up in .NET GridView if it's all in one returned column because your probably using a true-type font in your presentation. I've found it best to return individual fields in your DataTable to the page and then let it handle the presentation.

    In your .NET application, you can format the output however you want. In your GridView, instead of using a BoundField, try using a TemplateField and embedding HTML between your child controls. You can probably get it laid out the way you want by using minimal CSS and

    tags.

    If automated pagination isn't an issue, then another option would be to switch to a Repeater control instead, where you can have a greater level of control over the output.

  • Thanks Wagner i would appreciate if you can provide example of using gridview templatefield (with css) that will help to achieve new line feed carriage of below

    City

    ----

    Fremont,Hanford,Los Anggeles

    Even do i still prefer a Tsql solution as my gridview is being generated programmatically ... and using a front end solution means quite a lot additionnal .net lines of code

  • Here's an example of a couple columns in a GridView that include HTML. There's one with a

    tag that links an <img> tag and a column with a <br /> tag. You can use this approach to format your output.

    <asp:GridView ID="grdData" runat="server" AutoGenerateColumns="false"

    AllowPaging="true" ShowFooter="True" AllowSorting="False"

    CssClass="tdat" BorderWidth="0" CellSpacing="1" CellPadding="1" PageSize="20">

    <Columns>

    <asp:TemplateField HeaderText="Report" ItemStyle-Width="60" ItemStyle-HorizontalAlign="Center">

    <ItemTemplate>

    <asp:HiddenField ID="txtID" runat="server" Value='<%#Eval("ID")%>' />

    <asp:Panel ID="pnlReport" runat="server">

    <a href='/Files/Exports/<%#Eval("Filename")%>'><img src="images/buttons/excel_sm.gif" alt="Open File" title="Open File" /></a>

    </asp:Panel>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="Address" ItemStyle-HorizontalAlign="Left">

    <ItemTemplate>

    <asp:Label ID="lblAddress" runat="server" Text='<%#Eval("ShowAddress")%>' />

    <asp:Label ID="lblState" runat="server" Text='<%#Eval("ShowState")%>' />

    </ItemTemplate>

    </asp:TemplateField>

    <asp:BoundField HeaderText="Year" DataField="ShowYear" ItemStyle-Width="80" ItemStyle-HorizontalAlign="Center" ItemStyle-CssClass="pl4" />

    </asp:GridView>

    I have the CSS class "pl4" defined to have padding-left: 4px; I'm showing the image as an <img> instead of an ImageButton for a very specific reason with how the client's browsers are configured, but that's beyond the scope here. My intent is to show how to use HTML inside a GridView control.

    If you want them to appear in different columns in the GridView, simply use BoundColumns for the separate fields.

    I know this is now well outside the realm of T-SQL in this SQL forum, so I don't want to "go down too many rabbit holes" with it and turn it into a discussion of .NET.

    I hope this helps.

  • Thanks Lynn .. tried your CTE solution but it did not give the expected outcome .. i may concentrate on looking at a gridview templatefield solution.

  • Thanks Wagner got your point as its turning to be an asp.net discussion i will try your code and find out if it can be of help

  • @Wagner

    Used this and it did work. i got exactly what i want ...

    <asp:TemplateField HeaderText="City" ItemStyle-Width="5cm" HeaderStyle-ForeColor="Blue"

    HeaderStyle-CssClass="Header_borderline" SortExpression="Cities">

    <ItemTemplate>

    <%# Replace(Container.DataItem("Cities"), ",", "(br)")%>

    </ItemTemplate>

    </asp:TemplateField>

    However if someone out there got a tsql solution would apreciate if he can still share it ....

    replace ( by < and ) by > : had to do that to avoid embedded html being interpreted

    Thanks all for your responses ... much appreciated

Viewing 15 posts - 1 through 15 (of 21 total)

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