Multiple lists merge in to single list

So far i tried like this i think i am close to a solution but unable to display the data with in the tree view.

Countries
-----------
CountryName(single line text)

States
------
StateName     (single line text)
CountryName(Lookup from Countries:CountryName)


City
--------
CityName    (single line text)
StateName   (Lookup from StateName:States)

Attached Jpg( is sample data from all the 3 lists. Now i have written three methods (one to get all countries, one that gets states by countryid, and one for cities by stateid) something like this:

public static SPListItemCollection GetCountries()
        {
            SPListItemCollection items;

            SPSite siteCollection = SPContext.Current.Site;
            SPWeb web = SPContext.Current.Web;
            items = web.Lists["Countries"].GetItems();
            return items;
        }

        public SPListItemCollection GetStates(int countryID)
        {
            SPListItemCollection items;
            SPSite siteCollection = SPContext.Current.Site;
            SPWeb web = SPContext.Current.Web;

            string queryString = "<Where><Eq><FieldRef Name=\"CountryName\" LookupId=\"True\" /><Value Type=\"Lookup\">" + countryID + "</Value></Eq></Where>";
            SPQuery query = new SPQuery(web.Lists["States"].DefaultView);
            query.Query = queryString;
            items = web.Lists["States"].GetItems(query);
            return items;
        }

        public SPListItemCollection GetCities(int stateid)
        {
            SPListItemCollection items;
            SPSite siteCollection = SPContext.Current.Site;
            SPWeb web = SPContext.Current.Web;

            string queryString = "<Where><Eq><FieldRef Name=\"StateName\" LookupId=\"True\" /><Value Type=\"Lookup\">" + stateid + "</Value></Eq></Where>";
            SPQuery query = new SPQuery(web.Lists["City"].DefaultView);
            query.Query = queryString;
            items = web.Lists["City"].GetItems(query);
            return items;
        }

I tried to display the data in the tree view but it is displaying with the Column name Instead of data in that column name . Screen shot below.

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using Microsoft.SharePoint;

namespace TreeViewProject.TreeViewWebpart
{
    public partial class TreeViewWebpartUserControl : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            SPListItemCollection countries = GetCountries();
            foreach (SPItem country in countries)
            {

                TreeNode nodeCountry = new TreeNode(country.Fields["CountryName"].ToString(), country.ID.ToString(), null, "", "_self");
                mytree.Nodes.Add(nodeCountry);
                SPListItemCollection states = GetStates(country.ID);

                foreach (SPItem state in states)
                {
                    TreeNode nodeState = new TreeNode(state.Fields["StateName"].ToString(), country.ID.ToString(), null, "", "_self");
                    nodeCountry.ChildNodes.Add(nodeState);
                    SPListItemCollection cities = GetCities(state.ID);
                    foreach (SPItem city in cities)
                    {
                        TreeNode nodeCity = new TreeNode(city.Fields["CityName"].ToString(), city.ID.ToString(), null, "", "_self");
                        nodeState.ChildNodes.Add(nodeCity);
                    } } }}

Can you please help how to modify this code to display with the correct data from the list. like the below format

Sorry for my late reply. There are a couple of different ways to approach this. Personally I would use ListData.svc and jQuery for this. I tend to use Kendo UI for my frontend work but you could use jQuery UI or just roll your own template with CSS.

Here is an example jQuery call to ListData.svc:

$.ajax({
    type: "GET",
    url: "../_vti_bin/ListData.svc/ListName",
    dataType: "json"
}).done(function (data, textStatus, jqXHR) {
    successFunction(data);
}).fail(function (jqXHR, textStatus, errorThrown) {
    errorFunction(jqXHR);
});

There are some things you have to lookout for when using REST in SharePoint 2010.

1. List names are all together Pascal case. So a list titled "Work Items" would be accessed via the URL ../_vti_bin/ListData.svc/WorkItems.
2. Field names are based on the title, not the internal name. So on the server-side where you would use item["Author"] to get the person who created the item, via the 2010 REST service it is Created. The best advice I have for you in this is to use a tool like Dev HTTP Client for Chrome that can let you craft the HTTP request and see the JSON that you get back.
3. Use $expand and $select and other operators for filtering, etc.

Here is an example of a grid that I have on a production site in SharePoint 2010 using Kendo UI grid and the ListData.svc. I blurred the document titles a little since the might be sensitive.

Here is the code used to generate that grid. Note that I am useing MVVM to declare the grid. You would likely just want to use the programmatic method explained in the Kendo UI API reference.

psApp.releasedProcesses = new kendo.data.DataSource({
        autoSync: false,
        serverPaging: false,
        pageSize:10,
        group: { field: "OwnersGroup", aggregates: [ { field: "OwnersGroup", aggregate: "count" } ] },
        aggregate: [ { field: "OwnersGroup", aggregate: "count" } ],
        pageable: true,
        type: "odata",
        transport: {
            read: function (options) {
                return $.ajax({
                    type: "GET",
                    url: "../_vti_bin/ListData.svc/ReleasedProcesses?&orderby=OwnersGroup&$inlinecount=allpages",
                    dataType: "json",
                    data: options.data
                }).done(function (data, textStatus, jqXHR) {
                    options.success(data);
                }).fail(function (jqXHR, textStatus, errorThrown) {
                    options.error(jqXHR);
                });
            }
        }
});

<div id="released-grid-view"
    data-role="grid"
    data-selectable="single"
    data-auto-bind="true"
    data-pagesize="15"
    data-pageable="{ pagesize: 15, pageSizes: [10,15,20,25], refresh:true}"
    data-columns='[  
        { 
            "field": "ProcessDocID", 
            "title": "Title",
            "template":  kendo.template($("#rel-title-column").html())
        },  
        {
            "field": "DocumentRev", 
            "title": "Revision",
            "width": "100px"
        },  
        {
            "field": "DocTypeValue", 
            "title": "Doc Type",
            "width": "100px"
        },  
        {
            "field": "OwnersGroup",
            "title": "Owner Group",
            "groupHeaderTemplate": "#= value # (#= count#)"
        },
        {  
            "field": "Modified",
            "title": "Last Modified",
            "template":  kendo.template($("#created-column").html()),
            "width": "120px",
            "filterable": {
                "ui": "datepicker",
                extra: false,
                operators: {
                    date: {
                        eq: "Is equal to",
                        neq: "Is not equal to",
                        gte: "Is after or equal to",
                        gt: "Is after",
                        lte: "Is before or equal to",
                        lt: "Is before"
                    }
                }
            }
        }
        ]'
    data-scrollable="true"
    data-filterable="true"
    data-sortable="true"
    data-source="psApp.releasedProcesses" 
></div>

<script type="text/x-kendo-ui-template" id="rel-title-column">
   <div class="grid-title">
      <a href="${__metadata.media_src}">
    <img style="height:25px;" src="icons/office/pdf-small.png" > ${Title}
      </a>
   </div>
</script>
<script type="text/x-kendo-ui-template" id="created-column">
      #= moment(Created).format("MM/DD/YYYY")#
</script>

I think you can do this with a data view in designer.

Create a new data source in designer. I forget which option it is but it lets you combine lists into a single data source. Then add an empty dataview to a page and attach that new data source to it.

Hope this pushes you in the right direction.

Display data from multiple sources in a single Data View

Edit: Per Roberts recommendation here is a high level summary of the steps based on the blog he was kind enough to link.

• In SharePoint Designer create a new Linked Data Source by selecting Data Sources and then the Linked Data Source button on the ribbon.
• Give your new data source a name and under the general tab of the pop up window specify the lists you want to link.
• Click next then select the type of merge you want to do. The two options are explained pretty well on the page, but i believe if you want to join all items its the first option.
• Click finish and your new data source will be created and ready.
• Open a new web part page or other type of page in SharePoint Designer and edit it (you may have to detach it from the layout in order to make these changes.)
• Click anywhere in an editable area of the page and on the Insert tab click the drop down on the Data View button and select Empty Data View.
• From there, you will see your page update and you will be shown a link on the data view to select the data source. Click the link and connect the data view to your new data source.
• From here its like working with a normal data view. Select the fields you want to display on the Data Source Details window and use the formatting, filtering, and other options on the Data View Tools / Options menu to tweak your display.

PS. As an added note, if you ever want to add groupings of more than two levels to a view, you can use the same process but link to your list rather than a custom data source. The data view web part gives you a bit more functionality than the default views give you.