Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


concatenate rows using for xml path and new line feed carriage


concatenate rows using for xml path and new line feed carriage

Author
Message
julesbenone
julesbenone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
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
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10315 Visits: 9594
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")%>' />
<aspTongueanel ID="pnlReport" runat="server">
<a href='/Files/Exports/<%#Eval("Filename")%>'><img src="images/buttons/excel_sm.gif" alt="Open File" title="Open File" /></a>
</aspTongueanel>
</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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
julesbenone
julesbenone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
Thanks Lynn .. tried your CTE solution but it did not give the expected outcome .. i may concentrate on looking at a gridview templatefield solution.
julesbenone
julesbenone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
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
julesbenone
julesbenone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
@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
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10315 Visits: 9594
Thanks for the feedback. Glad it helped.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
Since you just want a grid solution maybe something like this would work?


;with StateData as
(
select t1.*, ROW_NUMBER() over(partition by t1.STATE order by t1.city) as RowNum
from #tempCityState t1
join #tempCityState t2 on t1.STATE = t2.STATE and t1.City <> t2.City
group by t1.STATE, t1.CITY
)

select case RowNum when 1 then STATE else '' end as STATE, City
from StateData
order by StateData.State, City



I still say it would be better in the front end but this does what I think you are after.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
julesbenone
julesbenone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
Sean Lange (3/22/2013)
Since you just want a grid solution maybe something like this would work?


;with StateData as
(
select t1.*, ROW_NUMBER() over(partition by t1.STATE order by t1.city) as RowNum
from #tempCityState t1
join #tempCityState t2 on t1.STATE = t2.STATE and t1.City <> t2.City
group by t1.STATE, t1.CITY
)

select case RowNum when 1 then STATE else '' end as STATE, City
from StateData
order by StateData.State, City



I still say it would be better in the front end but this does what I think you are after.


this gives a layout similar to what am looking however it does not group rows . the row concactenation has been lost: its not what am looking for . Tks anyway
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
julesbenone (3/22/2013)
Sean Lange (3/22/2013)
Since you just want a grid solution maybe something like this would work?


;with StateData as
(
select t1.*, ROW_NUMBER() over(partition by t1.STATE order by t1.city) as RowNum
from #tempCityState t1
join #tempCityState t2 on t1.STATE = t2.STATE and t1.City <> t2.City
group by t1.STATE, t1.CITY
)

select case RowNum when 1 then STATE else '' end as STATE, City
from StateData
order by StateData.State, City



I still say it would be better in the front end but this does what I think you are after.


this gives a layout similar to what am looking however it does not group rows . the row concactenation has been lost: its not what am looking for . Tks anyway


Then I would go back to my previous position that this belongs in the front end. I don't think you need to use Item Templates for this though. I tossed together a really quick page that does just what you are looking for.

First your proc needs to be tweaked slightly.


create proc RowsDisplayed as

if object_id('tempdb..#tempCityState') is not null
drop table #tempCityState

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(''),TYPE).value('.','VARCHAR(1000)'),1,0,'')) AS Cities
From #tempCityState t



Now in your .net page you just need to add a grid. The HTML section looks like this.


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="State" HeaderText="State">
<ItemStyle VerticalAlign="Top" />
</asp:BoundField>
<asp:BoundField DataField="Cities" HeaderText="Cities" HtmlEncode="False" />
</Columns>
</asp:GridView>



Notice that the Cities column has HtmlEncode = False. That means it will render any html as html instead of encoding it first.

So to complete this I just created the following snippet in Page_Load:


using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
string sql = "RowsDisplayed";
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader dr = command.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
connection.Close();
}



The output looks like this:



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
julesbenone
julesbenone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
Sean Lange (3/22/2013)
julesbenone (3/22/2013)
Sean Lange (3/22/2013)
Since you just want a grid solution maybe something like this would work?


;with StateData as
(
select t1.*, ROW_NUMBER() over(partition by t1.STATE order by t1.city) as RowNum
from #tempCityState t1
join #tempCityState t2 on t1.STATE = t2.STATE and t1.City <> t2.City
group by t1.STATE, t1.CITY
)

select case RowNum when 1 then STATE else '' end as STATE, City
from StateData
order by StateData.State, City



I still say it would be better in the front end but this does what I think you are after.


this gives a layout similar to what am looking however it does not group rows . the row concactenation has been lost: its not what am looking for . Tks anyway


Then I would go back to my previous position that this belongs in the front end. I don't think you need to use Item Templates for this though. I tossed together a really quick page that does just what you are looking for.

First your proc needs to be tweaked slightly.


create proc RowsDisplayed as

if object_id('tempdb..#tempCityState') is not null
drop table #tempCityState

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(''),TYPE).value('.','VARCHAR(1000)'),1,0,'')) AS Cities
From #tempCityState t



Now in your .net page you just need to add a grid. The HTML section looks like this.


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="State" HeaderText="State">
<ItemStyle VerticalAlign="Top" />
</asp:BoundField>
<asp:BoundField DataField="Cities" HeaderText="Cities" HtmlEncode="False" />
</Columns>
</asp:GridView>



Notice that the Cities column has HtmlEncode = False. That means it will render any html as html instead of encoding it first.

So to complete this I just created the following snippet in Page_Load:


using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
string sql = "RowsDisplayed";
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader dr = command.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
connection.Close();
}



The output looks like this:




Used the htmlencode = false but it didn't change nothing . please note i do already have a gridview.databind() code in my page load(). i was also unable to download the attachment: the link is taking me to the forum home page. In the latest tsql code that you provided i would appreciate if you can combine the first two lines togheter as not sure whether i got them copied correctly. have them as follow:
Select Distinct State, (Select Stuff((Select City + ''

Still believe i will have to use templatefield as a workaround even do i would prefer a boundfield which gives more flexibility in term of sorting and filtering
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search