Member Avatar for ansari.wajid

Hi

I have 2 tables in database as follows:

table1:

id name state id

1 aaa 1,2

2 bbb 1,3

table2:

id state

1 ak

2 ny

3 jk

so the problem is that I want to display all the data of "table1" in a gridview and in place of "state id" I want to display "statename", for example in place of "1,2" I want to display "ak,ny".

How to do this?

Please help me.

Thanks

  • 2 Contributors
  • 4 Replies
  • 101 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ansari.wajid

Dani AI

Generated

Two practical options to get "ak,ny" instead of "1,2" in a GridView: a quick SQL-side transform you can bind directly, or the cleaner long-term fix — normalize the schema with a junction table. posted general tutorials; for a new ASP.NET user like the focused steps below should be faster to apply.

A quick MySQL query (no schema change) uses FIND_IN_SET to match IDs inside the CSV and GROUP_CONCAT to reassemble names. If your column name contains spaces use backticks; if the CSV has spaces after commas, strip them with REPLACE. Example SQL:

SELECT
  t1.id,
  t1.name,
  GROUP_CONCAT(t2.state
    ORDER BY FIND_IN_SET(t2.id, REPLACE(t1.`state id`, ' ', ''))
    SEPARATOR ', ') AS state_names
FROM table1 t1
LEFT JOIN table2 t2 ON FIND_IN_SET(t2.id, REPLACE(t1.`state id`, ' ', '')) > 0
GROUP BY t1.id, t1.name;

Notes: GROUP_CONCAT has a length limit (group_concat_max_len) and can truncate long lists; increase it if needed. FIND_IN_SET prevents normal index use so this is fine for small tables but will slow on large datasets.

Bind the result to a GridView from ASP.NET using MySQL Connector/NET. Minimal C# pattern:

using MySql.Data.MySqlClient;
using System.Data;

string connStr = "server=...;uid=...;pwd=...;database=...;";
string sql = "<the query above>";

using (var conn = new MySqlConnection(connStr))
using (var da = new MySqlDataAdapter(sql, conn))
{
  var dt = new DataTable();
  da.Fill(dt);
  GridView1.DataSource = dt;
  GridView1.DataBind();
}

Recommended long-term fix: create a linking table (e.g., table1_states with columns table1_id and state_id), migrate the CSV rows into that table, then use a GROUP_CONCAT over a proper JOIN. That removes string parsing, restores index use, and is far easier to maintain.

Recommended Answers

All 4 Replies

Member Avatar for ansari.wajid

Hi

Thanks for your reply.
Sorry to say that the link you sent does not match my requirement. Actually I am new to asp.net, If you have any other idea for my requirement please reply back.

Thanks again

Member Avatar for majestic0110

Ok, may I recommend that you watch the videos here :

These videos are great for absolute beginners and definately recommend them. HTH

Member Avatar for ansari.wajid

Thanks a lot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.