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: