Concatenating Multiple Rows Fields Into One Column In T-SQL

Jan 19, 2011

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

Similar Messages:

DataSource Controls :: Concatenating Rows From Another Two Tables

Apr 13, 2010

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.

View 3 Replies

DataSource Controls :: Summing A Column From Multiple Rows?

Jul 1, 2010

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?

View 7 Replies

Adding Multiple Rows, Displaying Session Rows And Then Doing One Insert?

Apr 14, 2010

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.

View 5 Replies

Forms Data Controls :: Display Text In 2 Rows And Then A Gridview Column - Third Row Is Displayed As A Row Under First Column

Jul 14, 2010

I have a 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.

View 11 Replies

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

Jan 30, 2009

I have a datatable as Shopping Cart and GV to display the Columns in the DT, also I have another Column Update, so every Row has an Update Cell that calculates the Subtotal by timesing The quantity Column to 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.

View 9 Replies

C# - Multiple Rows Have Multiple Submit Buttons, Should I Make A Form For Each Button?

Jan 28, 2010

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?)

View 8 Replies

Dynamic Rows And Fields Generation In Crystal Reports

Sep 8, 2010

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.

View 1 Replies

Forms Data Controls :: Calculated Fields For All Of The Rows Is Blank

Jun 22, 2010

I have a GridView bound to an ObjectDataSource. Most of the columns are directly databound, however one column is not - it's value is determined in code. The value is being calculated correctly at 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

View 2 Replies

Forms Data Controls :: Dynamically Add Fields / Rows To DetailsView

Aug 20, 2010

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

View 1 Replies

Forms Data Controls :: FormView Error "Cannot Insert The Value NULL Into Column" When 'not Null' Column Left Blank In Fields?

Sep 14, 2009

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:


View 2 Replies

Visual Studio 2005 - Extract Rows From DataBase - Replace Fields - Insert Into Another Table

Mar 8, 2010

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 extract rows 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.

View 1 Replies

VS 2008 Combining Two Fields For GridView Column?

Mar 30, 2010

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?

View 8 Replies

ADO.NET :: Convert Rows To Column?

Feb 1, 2011

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

View 6 Replies

Forms Data Controls :: Binding A GridView Column To Different Fields?

Dec 2, 2010

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"

View 1 Replies

C# - Concatenating String And A Resource Value?

Mar 14, 2011

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>:

View 2 Replies

How To Add Validation Which Checks The Sum Of Multiple Fields

Sep 15, 2010

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?

View 1 Replies

Sort List For Multiple Fields

Dec 5, 2008

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}

View 2 Replies

Edit All Rows For One Column In A GridView?

May 27, 2010

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?

View 3 Replies

ADO.NET :: LINQ To Entities - Getting The Value Of The Rows By Column?

Nov 17, 2010

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:


View 6 Replies

Column Value Of Multiselect Rows In Jqgrid?

Nov 22, 2010

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');

View 1 Replies

DataSource Controls :: Concatenating Columns With The IDs?

Nov 19, 2009

I am getting result as below:

ID Colors
1 Red
1 Blue
1 Black
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

View 6 Replies

Trying To Bind A Control To Fields In Multiple Tables?

Mar 26, 2009

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?

View 2 Replies

JQuery AutoComplete Updating Multiple Fields.NET?

Oct 4, 2010

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].....

View 2 Replies

Web Forms :: Select Multiple Fields From A Dropdownlist?

Sep 6, 2010

How to select multiple fields from a dropdownlist..

View 6 Replies

Web Forms :: Multiple Fields In A Drop Down List?

Dec 21, 2010

I am re-writing Access applications to using VB. On one Access application I have a combo box that is capable 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 updates 3 separate fields on another table. This is an easy process to do in Access, but I cannot seem to find a way to do this in using the drop down list. Can a drop down list display multiple fields or an entire record or is the drop down list limited to a single field per row?

View 2 Replies

SQL Server :: Sql Syntax To Update One Column With Many Rows

Nov 21, 2010

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=?

View 7 Replies

ADO.NET :: Update A Column For All Rows In A Collection Using Linq?

Jul 28, 2010

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.

View 2 Replies

SQL Server :: Concatenating Ntext Datatype Columns?

Dec 6, 2010

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.

View 2 Replies

Web Forms :: Use Stringbuilder When Concatenating More Than 2 Strings - Performance?

Jul 2, 2010

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?

View 3 Replies

Copyrights 2005-15, All rights reserved