Retrieve data from multiple columns and display within a single column

Question

I am using VS2005 C# and SQL2005.

I would like to retrieve data from multiple columns and merge them, separating them by comma.

E.g. Data in my SQL Table UserData:

Data I would like to display in my GridView GridView1:

No. | Username | Roles |

1 | Jimmy | USER, READONLY, WRITE |

How can I use a SELECT statement to merge data from the 3 columns and merge them, separating the data by commas and list them in the GridView?

Thank you

EDIT

I have tried using the method provided by Shark and Mun, but I would like to remove the commas if the data in a Role column is empty.

Situation now is that Roles column will look like this if Role 2 and Role 3 is empty: USER,,

May I know how can I group , into the variable as well so it will not display if the value is empty?

You may need to check for null, as concatenating columns with null values will result in a null value. Try this SQL query instead: select username, IsNull([role 1], '') + ', ' + IsNull([role 2], '') + ', ' + IsNull([role 3], '') as Roles from UserData — Mun
– Mun, Commented Jan 6, 2012 at 3:03
@RUiHAO Check out my edited post. It uses ISNULL() to get your desired result. — user596075
– user596075, Commented Jan 6, 2012 at 3:43
@Mun that query will put a leading comma or double commas with different NULL values. Check out my answer. Combine the column and the comma character in the ISNULL() function and that should be what the OP is looking for. — user596075
– user596075, Commented Jan 6, 2012 at 3:44
@RUiHAO I see your edit. See my updated answer, that new query and code should give you your desired result. — user596075
– user596075, Commented Jan 6, 2012 at 3:46
@Shark Looks good. Haven't come across NullIf command before, but that's definitely something that I can see myself using in future. Thanks! :-) — Mun
– Mun, Commented Jan 6, 2012 at 19:36

el ninho · Accepted Answer · 2012-01-06 02:07:10Z

2

You should do this in stored procedure, and then bind GridView to that stored procedure:

SELECT No., Username, CAST(Role1 AS varchar(1024)+ ', ' + Role2 + ', ' + Role3)

answered Jan 6, 2012 at 2:07

el ninho

4,24316 gold badges60 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2012-01-06 03:55:10Z

1

Here is the SQL query:

select
    username,
    isnull(nullif([role 1], '') + ', ', '') + 
    isnull(nullif([role 2], '') + ', ', '') + 
    isnull(nullif([role 3], ''), '') as Roles
from UserData

And then for your grid view code:

SqlConnection conn = new SqlConnection(YourConnectionStringHere);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = 
    "select " +
    "    username, " +
    "    isnull(nullif([role 1], '') + ', ', '') + " +
    "    isnull(nullif([role 2], '') + ', ', '') + " +
    "    isnull(nullif([role 3], ''), '') as Roles " +
    "from UserData";

SqlDataAdapter sda = new SqlDataAdapter(cmd);
Datatable YourData = new DataTable();

try
{
    sda.Fill(YourData);

    GridView1.DataSource = YourData;
    GridView1.Bind();
}
catch
{
    sda.Dispose();
    // handle your error
}

edited Jan 6, 2012 at 3:55

answered Jan 6, 2012 at 1:59

user596075

4 Comments

gymcode Over a year ago

hi, I have implemented the method. However is there a way to remove , if the value is empty? I am having some trouble with the display of data whenever Role 1,2 or 3 is empty, , are displayed as well

user596075 Over a year ago

@RUiHAO That's exactly what that query will do. It Checks for the combination of [role x] + ', '. If that is NULL (which it will be if [role x] is NULL), then it won't show the unnecessary comma.

user596075 Over a year ago

@RUiHAO When you say "empty", what do you mean? NULL or a blank string ('')?? Those are two very different things. The above solution is for NULL values, not an empty string ('').

user596075 Over a year ago

@RUiHAO See my edit. This will handle the situation where your [role x] field is NULL or an empty string. Let me know if that works out for you.

Collectives™ on Stack Overflow

Retrieve data from multiple columns and display within a single column

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related