Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

concatenate rows using for xml path and new line feed carriage Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 23, 2013 8:09 AM
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

Post #1434390
Posted Friday, March 22, 2013 10:56 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 4,339, Visits: 3,382
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1434400
Posted Friday, March 22, 2013 11:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 23, 2013 8:09 AM
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.

Post #1434411
Posted Friday, March 22, 2013 11:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 23, 2013 8:09 AM
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
Post #1434425
Posted Friday, March 22, 2013 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 23, 2013 8:09 AM
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
Post #1434450
Posted Friday, March 22, 2013 12:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 4,339, Visits: 3,382
Thanks for the feedback. Glad it helped.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1434464
Posted Friday, March 22, 2013 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 13,463, Visits: 12,322
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)
Post #1434466
Posted Friday, March 22, 2013 12:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 23, 2013 8:09 AM
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
Post #1434484
Posted Friday, March 22, 2013 1:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 13,463, Visits: 12,322
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)
Post #1434504
Posted Saturday, March 23, 2013 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 23, 2013 8:09 AM
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
Post #1434597
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse