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

Web Forms :: Display Multiple Column Fields In One Single Label Using DataReader?

Aug 5, 2013

how to display multiple records in a label from database through coding?

View 1 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

Data Controls :: Search Multiple Column Fields Of GridView On Single TextBox KeyPress

May 7, 2015

I have a gridview with textbox and that textbox is for searching. My question is do you know how to search with all fields in gridview.For example: I have a gridview contains, FIRST NAME, MIDDLE NAME, and LAST NAME, and if I type in the textbox the gridview will automatically get what I've inout in textbox.

View 1 Replies

Data Controls :: Display Multiple Data Column Fields In One BoundField Of GridView?

Sep 5, 2013

how to put three rows of one table data in gridview one coloumn

 how to add text1,text2,text3 rows data in upper as i write but the proper code

<asp:BoundField ItemStyle-Width = "150px" DataField = "LT_SHOWNAMETEXT1" HeaderText = "SHOW NAME"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "LT_TEXTTYPE" HeaderText = "TEXT TYPE"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "text1,text2,tex3 " HeaderText = "TEXT"/>


View 1 Replies

Data Controls :: Rotate GridView Rows Into Column With Image Column

Apr 27, 2016

I saw post [URL] .... which is converting rows into column item,quantity ,price. I have one other image field .how can bind image in this program?

View 1 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

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

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

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

Web Forms :: Mapping Column Fields In Code

Feb 8, 2012

In sql table I have fields which needs to be mapped with ALIAS field names. How to do the mapping when coding. Currently I have

select *,,data_type  from mappingtable,information_schema.columns where   where mainfield='City' or aliasfield ='City'

execute query using reader

This table does not contain the actual data

Its contains onky the mainfield and its alias field name

I get City from a variable

I have  a second table which contains mainfield ie the column names & the actual data then I use

if( reader["data_type"].ToString() == "numeric")
do something and so on
make label control visible true
make dropdown visible true

but there is lot of performance and speed issue. What would be the correct method so as to enhance the above...

View 1 Replies

Data Controls :: Display Multiple Columns In Multiple Lines In Single Column In GridView

Aug 18, 2015

I have a question in gridview, I want to combine my two or more records into a single cell with two rowsfor Example i have a ID, Name, Year Start, and Year End for Column name in database and i want call it into my gridview that the Year Start and Year End will combine to Year like:

In Database

ID Name Year Start Year End

1 Yourname 2010 2015

In Gridview:

ID Name Year

1 Yourname 2010 2015

in a year column i want it to two row..

View 1 Replies

Data Controls :: Display Multiple Columns In Multiple Lines In Single TemplateField Column In GridView?

Aug 18, 2015

I want to bind three column with one row in gridview in with c#,

i want like this, 

Name | Info
name | lastname

        | phone number

        | address

I get this by default

name | last name | phone number | address 

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

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

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

Concatenating Results From SQL Query And NULL Columns?

May 11, 2010

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?

View 6 Replies

Localization - Concatenating Text In .NET Localized Label?

Jun 2, 2010

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.

View 3 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

Copyrights 2005-15, All rights reserved