Tracking Forums, Newsgroups, Maling Lists
Home Submit Tracker Forums
  Advanced Search


Concatenating Multiple Rows Fields Into One Column In T-SQL

I am writing an SQL query in which that I will need to perform a sub select on a table, which will usually return multiple rows. I need to be able to join together the results of a certain field from all the rows into one field to output. Is this possible, and how?

For example, if the SQL query returns

id | field
1 | test1
2 | test2
3 | test3

I need the outputted field to be "test1 test2 test3".

View 4 Replies (Posted: Jan 19 at 19:07)

Sponsored Links:

Related Forum Messages For ASP.NET category:
DataSource Controls :: Concatenating Rows From Another Two Tables
I have a problem where I would like to get a comma-separated list of items from a couple of tables as part of a set of results. All the searching I've done so far has produced results for dealing with one or two tables. I have four, and can't get my head around how to adapt the sql to deal with it.

I have the following layout:

+---- CastingID {PK}
| MouldID

| SerialNo

| CastRef

| MeltCode

| Signature



| *Table_CastCertificates* *Table_Certificates*

| CertificateID {PK} ----------- CertificateID {PK}

+---- CastingID {PK} Description

FileID -----------------+



*Table_CertificateFiles* |

FileID {PK} -------------+


The results I would like include the following columns:

CastingID, MouldID, SerialNo, CastRef, MeltCode, [Certificates],

Where Certificates is a comma separated list of Description + " " + FullLocation records. (What I'll actually do is add http:// and other html codes to make FullLocation a hyperlink, but I can do that.)

Some SQL I've already got is this:


SELECT @str = COALESCE(@str + ', ','') + '<a href="http://' + Table_CertificateFiles.FullLocation + '">' + Table_Certificates.Description + '</a>'

FROM Table_CastCertificates INNER JOIN

Table_Certificates ON Table_CastCertificates.CertificateID = Table_Certificates.CertificateID INNER JOIN

Table_CertificateFiles ON Table_CastCertificates.FileID = Table_CertificateFiles.FileID

WHERE (CastingID = 45)

SELECT output = @str

This actually gives all records in the for the specific casting, but I keep getting an error at the "=" in @str = COALESCE when I try joining it to another query to get each casting.

Posted: Apr 13, 2010 11:44 AM

View 3 Replies!   View Related
DataSource Controls :: Summing A Column From Multiple Rows?
I currently have a datasource that displays several rows on an editable gridview where the user can update several rows at once. Each row contains an ActivityTotal column per row. I'd like to sum all the ActivityTotal rows displayed and have one totalValue.

for example

row 1 activityTotal = 4
row 2 activityTotal = 3
row 3 activityTotal = 1
totalValue = 8

How can I incorporate this into the code?

Posted: Jul 01, 2010 10:32 AM

View 7 Replies!   View Related
Adding Multiple Rows, Displaying Session Rows And Then Doing One Insert?
I need to produce a from where I can add multiple parts. Each time a user enters a part number, the description should be populated. The user can then click add and this part and description will be shown below. They have the option to add as many parts as they require. Once all parts are added the user fills in some general info eg description and then once a submit button is clicked all part info will be inserted into a table and general info will be inserted into another table with one unique ID.

Posted: Apr 14 10

View 5 Replies!   View Related
Forms Data Controls :: Display Text In 2 Rows And Then A Gridview Column - Third Row Is Displayed As A Row Under First Column
I havea gridview with 3 columns. In second column I want to display some text in 2 rows and then a gridview.
Here is my code:


The problem is my third row is displayed as a row under first column. How do I make it a separate column in my girdview. Not sure what is going wrong.

Posted: Jul 14, 2010 04:33 PM

View 11 Replies!   View Related
How To Replace With Having Only One Button In Footer Of The GridView That Can Update All The Rows In The DtTable By Timesing Price Column To Quantity Column
I have a datatableas Shopping Cart and GV to display theColumns in the DT, also I have another Column Update, so every Row has an Update Cell that calculates the Subtotal by timesing The quantity Columnto Price Column.How can I replace with having only one button in Footer of the GridView that can update all the Rows in the DtTable by timesing Price column to Quantity Column. something like Looping through all the rows.

Posted: Jan 30, 2009 02:33 PM

View 9 Replies!   View Related
C# - Multiple Rows Have Multiple Submit Buttons, Should I Make A Form For Each Button?
I am listing about 20 rows, each row represents an Order.Each row needs to have 3 buttons, each button click will perform a different action.I have 3 actions to handle each button post request, I am just unsure how to setup the Html forms for each button.

<form method="post" action="/orders/do1"><input type=button ... /></form>
<form method="post" action="/orders/do2"><input type=button ... /></form> [code]....

Should I create 3 forms for each button, per row in my listing?(that would mean 20 rows x 3 forms = 60 forms on a page)Is that 'ok' to do? (i.e. or is there a better way to do this?)

Posted: Jan 28 10 at 22:12

View 8 Replies!   View Related
Dynamic Rows And Fields Generation In Crystal Reports
I am working on Crystal reports.My requirement is that i need to generate dynamic fields according to the count ofFamily_Members column in sql server database,for example if i have '4' in 'Family_Members' column i need to generate 4(Family members details)dynamically.

Posted: Sep 08, 2010 05:09 AM

View 1 Replies!   View Related
Forms Data Controls :: Calculated Fields For All Of The Rows Is Blank
I have a GridView boundto an ObjectDataSource. Most of the columns are directly databound, however one column is not - it'svalue is determinedin code. The valueis being calculated correctlyat the RowDataBound event, however when there is more than one data row in the GridView, the calculated fields for all of the rows is blank, but the top row displays the bottom row's calculated field

Posted: Jun 22, 2010 11:02 PM

View 2 Replies!   View Related
Forms Data Controls :: Dynamically Add Fields / Rows To DetailsView
I have created DetailsView in designer page with SqlDataSource, Details view is with autoGenerateRows="false" i.e. I have declared my own fields. it is fine there, but what if I want to add these fields dynamically from codebehind with select & edit commands. I want to to use Details View in designer page and want to add fields from codebehind

Posted: Aug 20, 2010 01:24 PM

View 1 Replies!   View Related
Forms Data Controls :: FormView Error "Cannot Insert The Value NULL Into Column" When 'not Null' Column Left Blank In Fields?
My FormView was working fine, then I set a couple columns in my base table to 'not null.' and regenerated the SQLDataSource. Shouldn't the update paramaters(confirmed as type String) be set to zero length strings"" instead of null for 'not null' fields??As in, if the texbox is blank and the UpdateParameter is a string, is it not a bug to pass a null?

I am using VS 2008 SP1 and SQL 2005.SqlDataSource definition:


Posted: Sep 14, 2009 07:59 PM

View 2 Replies!   View Related
Visual Studio 2005 - Extract Rows From DataBase - Replace Fields - Insert Into Another Table
I work with Visual studio 2005 and Sql server data base. I extract 1 million rows from a database and I put the rows in a Sql table. With a simple direct extraction, in a few minutes I complete the operation:

Dim cmd As New SqlCommand(query, cnSql)

But, if I must select and modify some fields between extraction and introduction, it takes a lot of time!
I work in this way. I extractrows from Dbase, Table1, and I put all into a Collection using dataReader:

Dim cmd As New SqlCommand(query, cnSql)
Dim coll As New Collection
Dim dr As SqlDataReader = cmd.ExecuteReader()
I replace some fields with the string "YES".
Dim toRepl As String = ""
If dr.HasRows Then
Do While dr.Read
toRepl = dr(5).ToString
If Len(toRepl) <= 3 Then ToRepl = "YES"
.................. ..............
collCount = collCount + 1
End If
Now, I insert all into a SQL Table2:
Dim a As String = "", b As String = "", c As String = "" etc.
For i = 1 To collPrel.Count Step 6
a = collPrel.Item(i).ToString
b = collPrel.Item(i + 1).ToString
.............. ...............
f = collPrel.Item(i + 5).ToString
queryIns = "INSERT INTO Tab (name, adress, etc)
VALUES ('" & a & "', '" & b & "', '" & c & "', etc. )
Dim cmdIns As New SqlCommand(queryIns, cnSql)
cmdIns.CommandTimeout = 600

This way to work goes right but it is necessary a lot of time for completing the operation.

Posted: Mar 08, 2010 10:15 AM

View 1 Replies!   View Related
VS 2008 Combining Two Fields For GridView Column?
I have a GridView with a SQLDatasource. All of the columns in the gridview are bound to a field. Now they want a column that is not a field returned but would be two of the fields combined together. How can I do this exactly? Can someone show a line of code to display this in a column?

Posted: Mar 30th, 2010, 10:32 AM

View 8 Replies!   View Related
ADO.NET :: Convert Rows To Column?
I have a datatable which contains rows for empid this is how the data looks empid year status category

1 2010 y 1
1 2010 y 2
2 2011 n 1
3 2010 y 2

i would like to make the data in the following format

1 2010 y 1 2
2 2011 n 1 0
3 2010 y 0 2

Posted: Feb 01, 2011 03:10 AM

View 6 Replies!   View Related
Forms Data Controls :: Binding A GridView Column To Different Fields?
I have a web page with an object data source that is connect to an object access layer which retrirevs an Id, Name_En and Name_Local from some table, the object data source is connected to a GirdView, In the girdview i have a template control with a label inside it, i want to change the binding expression for the label depending on the current states of localization so for example if the page is arabic the label should bind to "Name_Local", if it's english then it should bind to "Name_En"

Posted: Dec 02, 2010 09:02 PM

View 1 Replies!   View Related
C# - Concatenating String And A Resource Value?
I'm having a strange behavior in my .aspx page. I'm internationalizing some pages, but as I want to keep only entities or value object in my resource I want to have a key-value like:

CITY - Cidade
STATE - Estado

But when I list the entities, I want to put the " : " at end. Like:

<asp:Label ID="LabelCity" runat="server" Text="<%$ Resources:Localizacao, CITY %>:"></asp:Label>

But, if I put the " : " after the resource in text property, the page only shows " : ".

My simple solution is put after all definition of label, but I think this too wrong:

<asp:Label ID="LabelCity" runat="server" Text="<%$ Resources:Localizacao, CITY %>"></asp:Label>:

Posted: Mar 14 at 12:49

View 2 Replies!   View Related
How To Add Validation Which Checks The Sum Of Multiple Fields
I'm trying to use jQuery validation for a dynamic form I'm setting up.

In some cases this form contains a set of input boxes which are suppose to total 100.

An example might be:

indicate what percentage of students are in each grade?

Grade 9: TextBox1
Grade 10: TextBox2
Grade 11: TextBox3
Grade 12: TextBox4

I want to verify that TextBox1 + TextBox2 + TextBox3 + TextBox4 = 100%.

How do I go about this?

Posted: Sep 15 10 at 3:00

View 1 Replies!   View Related
Sort List For Multiple Fields
how can I sort this class with multiple columns?Request to give me code with syntex ,Here is my class and method to call it, can you tell me how can I retrieve result sorted by FamilyName, GroupName, subGroupName?

Method to get it as list : List<MyClass
> lstPriceBook = new
List<MyClass>(); lstPriceBook.Add(new
MyClass());TODO: Need to sort here ( by FamilyName, GroupName, SubGroupName ) after getting as a list : My

Class : Here is my class and method to call it, can you tell me how
can I retrieve result sorted by FamilyName, GroupName, subGroupName? Method to get it as list : List<MyClass

> lstPriceBook = new
MyClass());TODO: Need to sort here ( by FamilyName, GroupName, SubGroupName ) after getting as a list : My
Class :public
class MyClass
#region Private Fields
private int?
private string
private string
private string
private int
//Private Fields
public MyClass()
_PriceListID = -1;
#region Public Properties
public string FamilyName
get { return _FamilyName; }
set { _FamilyName =
value; }
public string GroupName
get { return _GroupName; }
set { _GroupName =
value; }
public string SubGroupName
get { return _SubGroupName; }
set { _SubGroupName =
value; }
public int ItemDefaultQty
get { return _ItemDefaultQty; }
set { _ItemDefaultQty =
value; }
//Public Properties}

Posted: Dec 05, 2008 06:35 PM

View 2 Replies!   View Related
Edit All Rows For One Column In A GridView?
I would like to be able to bulk edit all the rows for one column using a GridView control. What is the best way to do this?

Posted: May 27 10 at 4:42

View 3 Replies!   View Related
ADO.NET :: LINQ To Entities - Getting The Value Of The Rows By Column?
All I am trying to do is query the database, then get the returned values, so that I can do some calculations. But, I don't understand how to get the values from the database row that is returned, so that I can assign it for usage.

I would like to do something where the logic goes: for the index of [1] (which is just column 1), multiply it by 2. For the index of [2] (which is just column 2), multiply it by 3.

Example below:

This does NOT work:


Posted: Nov 17, 2010 04:53 AM

View 6 Replies!   View Related
Column Value Of Multiselect Rows In Jqgrid?
Its like I have multiselect option in Jqgrid in which i want to pass the selected rows value to the server and based on the value i will delete the rows. I dont want to have the ids to do my work. For the Single row i get the cell value and delete using the same. But for multi select its not the case. In getGridParam('selarrrow'); I use this to fetch the selected rows bu the values are not getting populated.

When i use the following code as i saw in some sample question i can fetch the value for the single row selection but when i select multiple rows then i pass it says like "FALSE" or "UNDEFINED". What can be the issue. var grid = jQuery('#list'); var sel_id = grid.jqGrid('getGridParam', 'selarrrow'); var myCellData = grid.jqGrid('getCell', sel_id, 'CountryId');

Posted: Nov 22 10 at 14:55

View 1 Replies!   View Related
DataSource Controls :: Concatenating Columns With The IDs?
I am getting result as below:

ID Colors
1 Red
1 Blue
2 Red
2 Black
3 White
3 Orange

But I need the result as below:

ID Colors
1 Red,Blue,Black
2 Red,Black
3 White,Orange

Posted: Nov 19, 2009 05:09 AM

View 6 Replies!   View Related
Trying To Bind A Control To Fields In Multiple Tables?
I'm trying to bind a control to fields in multiple tables, but all the examples I can find use the LinqDataSource, and I can't find a way to configure a LinqDataSource to retrieve data from more than one table. Can anyone help me figure out how to do this?

Posted: Mar 26, 2009 03:28 AM

View 2 Replies!   View Related
JQuery AutoComplete Updating Multiple Fields.NET?
I'm trying to use the AutoComplete feature of JQuery to update multiple fields on an ASP.NET web page. The individual textbox is functioning perfectly, but I cannot figure out how to update multiple textboxes on the page once the user selects.

I've read many suggestions here that state I need to implement a result handler, but my intellisense is not showing that as an option, and IE I get a JS error saying that the object doesn't support this method.

I am linking to jquery-1.4.2.min.js and jquery-ui-1.8.5.custom.min.js .Here's my code:

$(function () {


source: function (request, response) {[code].....

Posted: Oct 4 10 at 23:34

View 2 Replies!   View Related
Web Forms :: Select Multiple Fields From A Dropdownlist?
How to select multiple fields from a dropdownlist..

Posted: Sep 06, 2010 09:35 AM

View 6 Replies!   View Related
Web Forms :: Multiple Fields In A Drop Down List?
I amre-writingAccess applications to using VB. On one Access application I have a combo box that iscapable of displaying an entire record (3 fields) from a table on each row of the combo box.When that record on the combo box is selected, each of these 3 fields updates3 separate fields on another table.This is an easy process to do in Access, but I cannotseem to find a way to do thisin using the drop down list.Can a drop down list display multiple fields oran entire recordor is the drop down listlimited to a single field per row?

Posted: Dec 21, 2010 03:44 PM

View 2 Replies!   View Related
SQL Server :: Sql Syntax To Update One Column With Many Rows
I have a table with 2905 rows. I want to update one column with 2905 values. The values are all different, and they are just stacked one to a line with a line break. One on top of the other like so:



How would I write this query? Do they have to all be separated by commas? What about NULLS?

update tblAdvanceLinkBanned set ReciprocalURL=?

Posted: Nov 21, 2010 12:05 AM

View 7 Replies!   View Related
ADO.NET :: Update A Column For All Rows In A Collection Using Linq?
is there a way to update a column for all rows in a collection using linq like sql does it

eg. UPDATE myTable SET myColumn='newValue'

Also weighing options as to whether there is better performance using linq to sql for this or straight sql to DB via table adapters etc.

Posted: Jul 28, 2010 12:47 PM

View 2 Replies!   View Related
SQL Server :: Concatenating Ntext Datatype Columns?
i have the below query where MessageText column is of ntext datatype..whe n i run the below query it is giving me the below error. Error :The data types ntext and varchar are incompatible in the add operator.

select li.LookUpPageInfoId,
(select messagetext from messages where messageid=pagemessageid and channelid=m.ChannelID)+'/'+
m.MessageText as TotalText,
LookUpPageInfo as li left outer join
Messages as m on m.MessageID=li.PathMessageID and li.IsActive=1

Can some one suggest me how to concatenate the ntext column....but the above works good if the column MessageText is of nvarchar(max) datatype.

Posted: Dec 06, 2010 01:08 AM

View 2 Replies!   View Related
Web Forms :: Use Stringbuilder When Concatenating More Than 2 Strings - Performance?
I have been led to believe that the performance benefits of using StringBuilder over normal concatenation means that you should always use stringbuilder when concatenating more than 2 strings. However when I test them this does not seem to be the case. Take these 2 methods

public void TestMethod5()
for (int i = 0; i < 1000; i++)
string t = string.Empty;
t = "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd" + "dddddddd"; ;
public void TestMethod6()
for (int i = 0; i < 1000; i++)
StringBuilder sb = new StringBuilder();
string t = string.Empty;
t = sb.ToString();

TestMethod5 takes 0.03 milliseconds so complete but TestMethod6 takes 1.43 milliseconds What is going on?

Posted: Jul 02, 2010 11:57 AM

View 3 Replies!   View Related
Concatenating Results From SQL Query And NULL Columns?
I need to concatenate several columns of a table into a single value, then show that value in an asp dropdownlist. The SQL code I'm issuing is as follows:

SELECT UserID, CustomerNum, UserName + ' - ' + UserAddress + ',' + UserCity + ' ' + UserState AS UserInfo
FROM Users
WHERE (CustomerNum = @CustomerNum)

I then set 'UserInfo' as the text field in the dropdownlist.This generally works, except occasionally one of the columns in the database is null (for example, UserState). When that happens, the entire concatenation is null, and I get an empty entry in the dropdownlist.

Is there something in SQLServer that will allow me to ignore those NULL results, or will I have to code something up in the DataBind event?

Posted: May 11 10 at 14:54

View 6 Replies!   View Related
Localization - Concatenating Text In .NET Localized Label?
Trying to localize text in ASP.NET labels... want to add a ':' after the localized text. I could just add the ':' to the localized text in the resource file, but that seems silly... there should be an easy way to do this.

<asp:Label id="RoleTypeLabel" runat="server" Text='<%$ Resources: GS3, RoleTypeLabel %>:' AssociatedControlID="RoleTypeDropDown"></asp:Label>

(note the ':' at the end of Text='...')

Of course, this doesn't work... and neither does anything I can think of to concatenate a ':' onto the end of the localized text.

Posted: Jun 2 10 at 20:38

View 3 Replies!   View Related
SQL Server :: Populate Drop Down List With Multiple Fields?
I'm trying to dynamically populate a drop down box with fields from a SQL Server Database, but I'm having some issues. I am able to get it to work well with only one field, but I need to get display two fields in the ddl. I've seen that you can do it in the command statement, but I have two different data types and I think that causes it to throw an error: Conversion failed when converting the varchar value 'This is a Title' to data type int.

here is my code:


Posted: Feb 03, 2011 05:22 PM

View 3 Replies!   View Related
How To Sort Gridview Rows By A Unbound Template Column
i want to sort my Gridview rows by a template column that is not bound to any database field. This template coulmn just has a label whose text i set in code depending on a value in a different column that is databound. So am stuck on how to set its sortExpression since its not linked to an column.

Posted: Mar 22 10 at 7:10

View 1 Replies!   View Related
DataSource Controls :: Can Get A Single Column Of Rows In A Table
I have a tblClientGuests tableand in it are theFrist and Last names of guests attending a meeting for each of our clients. I need to return in a select statementa single column with the first and last name of all guests for each client_id.

So I would have a single row for each client_ID, like client _ID (1) and in it would be a column named(Guest) in the guest column would beall the names of that clients guests.

Maybe something like:

SELECT Guest FROM tblClientGuests WHEREClient_ID=1
Jim Smith, Jane Smith, Jack Jones, Debbie Philps

But thisis all in the one cloumn Guests

How could I do something like this?

Posted: Dec 07, 2009 06:56 PM

View 6 Replies!   View Related
Forms Data Controls :: Take Column's Value For All Rows On A Control?
I am having a datatable. I am binding this to a GridView1. In this gridview there are bound fields and a template field; Button1.

In datatable there is a coloum UserName, So I want to make it like when this gridview

bound,in all rows of GridView1 having the UserName is qeuals to Session["uname"]

Button1 should enable else Button1 should disable.

I did this way on Grid Views RowDataBound Event

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
if (e.Row.RowType == DataControlRowType.DataRow)
DataRowView dr = ((System.Data.DataRowView)(e.Row.DataItem));


But tell me How can I get all UserName in only one Label1 lblUser

Posted: Sep 19, 2009 02:46 PM

View 20 Replies!   View Related
Adding Data From More Than Two Columns In Drop Down List (not Concatenating Them)?
adding data from more than one columns of a table in drop down list but not concatening them.

Like a table having columns Station_1 , Station_2 , Station_3. All these columns having place names.

Now I want to list the distinct station/place names from each columns Station_1, Station_2 and Station_3 in my drop down list.

How to do this I serched everywhere in but not found the solution..

Posted: Jun 22, 2010 05:59 AM

View 4 Replies!   View Related
Web Forms :: Concatenating Label Text At Design Time?
I am trying to find the best way to concatenate two stringsand set the result to the text property of a label during design time.Here is what is not working:

I have Text="<%&#36;Resources:labels, btnLinkNoText%>" and Text='<%# Bind("ProductID") %>'
I want Text="<%&#36;Resources:labels, btnLinkNoText%>" + Text='<%# Bind("ProductID") %>'

Posted: May 28, 2010 02:04 PM

View 3 Replies!   View Related
C# - Combining Multiple Fields In C# Dynamic Data Foreign Key Display?
I have two tables (Person and Location) In the Dynamic Data Site, when adding Location info, I am need to choose a person from the dropdown box which is populated via a FK. That dropdown box defaults to the field in "Person" that is titled "fname" which is first name, so it looks like "Jim" or "Steve".

I'm trying to have that dropdown box display the full name of the person it references which would be combining the fields fname and lname.

Would I do this inside a Field Template? Metadata? I'm kind of stuck on this.

If I look at the FieldTemplate for ForeignKey_Edit I see this:


and it seems like I should be able to make something similar to "PopulateListControl" and use that instead, but I have no idea where this method even resides.

Posted: Sep 30 10 at 18:43

View 1 Replies!   View Related
Adding Multiple Fields Dynamically By Clicking Button Or Link
my requirement is i want to add education details having three fields

1) name of school
2)name of city

some persons will studied only in one school . some in 2 . aome in 3. how to add dynamically .and after that i want to show the data to admin .if there is 2 means i want to display 2 school informa

Posted: Mar 23, 2011 12:52 PM

View 4 Replies!   View Related
Copyright 2005-08, All rights reserved