Concatenate Two Fields In A View?

Feb 14, 2006

Hello,

I'm wondering if there is a way to concatenate two fields or a field and a
string value in a single field in a view?

Where in Access I might write;

[field1] & " (m3)" as TotalVolume

is there a way to do this in an SQL Server View?

Thanks!

View 2 Replies


ADVERTISEMENT

Concatenate 2 Fields

Feb 13, 2008

Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks

View 7 Replies View Related

How To Concatenate Several Fields Together With Commas

Nov 17, 2014

I have a hierarchy of product categories and I want to string them together to show the complete path (breadcrumbs) to the item. Each category/subcat must be separated buy a comma. The catch is that not all items have the same number of cats/subcats. Here is what I am currently doing, but as you can see, this results in extra commas where the subcats are null.

Code:
create table #ItemCat(
itemNo int,
Cat varchar(50),
SubCat1 varchar(50),
SubCat2 varchar(50)

[Code] ....

Is there some way to concatenate these, separated by commas, but ignoring the NULL fields? For example, ItemNo 1 should show "Kitchen, Appliances"

View 3 Replies View Related

Concatenate Two Text Fields

Mar 29, 2006

New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:

Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....

I've created the following query that displays the columns:

CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE

FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID

WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE

Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?

Please advise - thanks!

View 8 Replies View Related

Concatenate Text Fields In Query

Oct 11, 2005

I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.

For example


Code:

SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'

FROM Table1



However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.

View 2 Replies View Related

Concatenate Fields In Stored Procedure

Feb 22, 2008

Does anyone have code example on how to concatenate fields in a SQL Server stored procedure?
I would like to create a stored procedure to concat (off_name ' ' off_fname ' ' off_prior) to a field to bound to a combobox.
Thanks,
Ron

Table below.


ALTER PROCEDURE CreateOfficersTable

AS

CREATE TABLE [officers](

[off_int_id] [int] Identity Primary Key,

[off_badge] [int] NULL,

[off_lname] [nvarchar] (20) NOT NULL,

[off_mname] [nvarchar] (20) NOT NULL,

[off_fname] [nvarchar] (20) NOT NULL,

[off_radio_num] [int] NULL,

[off_handle] [nvarchar] (10) NULL,

[off_unit] [nvarchar] (5) NULL,

[off_prior] [int] NULL,

[off_shift] [int] null,

[dateadded] [datetime] NULL,

[userchange] [nvarchar](10) NULL,

[changedate] [datetime] NULL

)

View 5 Replies View Related

Concatenate Text And Varchar Fields

May 29, 2008

I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:


UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '

' WHERE Column_TEXT = 'Some text'

I also tried

UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'

But I keep getting the error

The data types text and varchar are incompatible in the equal to operator.

Help!!

Thanks in advance
Mangala

View 3 Replies View Related

Transact SQL :: Concatenate Multiple Fields Into One

Dec 5, 2015

I have a table where I need to concatenate all values into one field separated by a comma.  If the field is null display a blank value.  This is my table structure and example output

Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)

[code]...

View 5 Replies View Related

How To Concatenate Two Fields In A Textbox One Integer And Other Charecter Field

Jul 2, 2007

I have the folliwing two fields, want tp concatenate:



=Fields!sequenceno.Value & =Fields!LogType.Value



Thank you very much for the information.

View 3 Replies View Related

Alter View And Concatenate In One Row (was: Is It Possible?!)

Aug 28, 2006

Hey Folks,

I have a View that I'm using to power a Crystal Report. I want to include an extra column that will be a string containing all the values from a single column of another table, seperated by comma.

So for example:

Project | Staff
----------------------
TestProject1 | "Jonathan, Greg, Joe"

Is this possible? Where do I do it? From the SQL Server end or in the report?

View 2 Replies View Related

SQL 2012 :: Concatenate Fields And Insert Comma Between - How To Handle Nulls

Sep 5, 2014

I thought it's easy but somehow got lost, what is the good way to insert that Comma intelligently, depending if any of var value is NULL.

-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'

SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )

View 3 Replies View Related

Problem With Updatetext In SQL 2000 Trying To Concatenate Multiple Text Fields

Jan 2, 2007

Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan

View 4 Replies View Related

SQL Server 2012 :: View To Concatenate File / Path Names Back To Root Directory?

Sep 25, 2014

We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root

Below is a sample:

Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4

iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.

I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.

Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)

The end result of my view on the above data should return:

Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)

View 5 Replies View Related

Update Certain Fields From A Grid View ?

Sep 25, 2006

Hi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting.  The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database  so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray.. 

View 1 Replies View Related

Manipulating Individual Fields Of A View

Nov 13, 2007

In my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)

At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.

What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.

The point of this is to then SELECT the connections identified by the UserID pairs.

I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?

View 6 Replies View Related

Merging Text Fields In View

Oct 25, 2004

I'm using a view where I do a few things, including merge two short text fields into one. Just wanted to know if there was an alternative way of doing this:

Example:
SELECT first_name + ' ' + last_name AS full_name
FROM names

It works - but it seems rather blah. Is there something that'd be more "proper"?

View 3 Replies View Related

How Do You Lock Table (or View) Fields From Editing???

Jul 20, 2005

Hello:I have an Access 2K form I built from a SQL Server 7.0 view. I want tolock certain fields in the database from users so they can see them onthe views and forms, but NOT be able to edit them.I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books andcould not find how to do this.Any advise will be greatly appreciated.Thanks,Richard

View 4 Replies View Related

Data Source View Missing Fields

May 9, 2008

Is there a limit on the number of fields that can be displayed in a table object in a data source view in SSAS 05?
One of the tables (the fact table) in my data source view is displaying only 50 fields. The table actually has many more than that. One of the fields that is not displaying is a foreign key that I need to link to a new dimension table. I have tried refreshing the view, but it doesn't bring in any additional fields.



View 10 Replies View Related

How To Separate The DateTime Field Into Two Fields In View

Jun 21, 2006

Hi ,

I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?

Best Regards,

View 4 Replies View Related

Reporting Services :: Power View - Text Wrap For One Of Fields

May 20, 2015

When I create a Power View on the SQL server after loading up my data model, how do I turn on Text wrap for one of the fields?

View 2 Replies View Related

View Field Content Update When Real Table Fields Change

Sep 1, 2005

Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas

View 4 Replies View Related

Retaining Fields For Multiple Models In Mining Model Prediction Tab Design View

Nov 29, 2006

I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view?

My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX.

I have consulted the following without finding an answer:
http://msdn2.microsoft.com/en-us/library/ms178445.aspx
http://msdn2.microsoft.com/en-us/library/ms175642.aspx
http://msdn2.microsoft.com/en-us/library/ms175678.aspx
http://msdn2.microsoft.com/en-us/library/ms175637.aspx

Thanks for your help,

Sam

View 3 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


I have just started using SQL Server reporting services and am stuck with creating subreports.

I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.

For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.

When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.

Am I missing something here? Any help is appreciated.

Thanks,
Sirisha

View 3 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Public Overridable ReadOnly Default Property Fields() As ADODB.Fields

Jan 26, 2008

sir

I have got this error message to establish connction with recordset vb .net, Can you please rectify this

Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'

my code like this


rs = New ADODB.Recordset

rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)


If txtUserName.Text = rs.Fields.Append(userid) Then


MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")

End If


thanks

View 1 Replies View Related

Update Fields With Searched First Date Record Fields

Jul 23, 2005

Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/

View 3 Replies View Related

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

Jul 2, 2015

I am using MS SQL 2012.  I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it.  Then I need a total of those added decimal fields. My data is like this:

I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:

ID, provider_name, uniq_id, total_spent, total_earned

Here is sample data:

1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00

I need the results to be just 3 lines:

Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00

But then I would need the totals for the Provider, so:

Harbor, 1800.00, 3800.00

View 3 Replies View Related

Search Multipe Fields, Compounding Fields, Like, Contains...?

Jul 20, 2005

I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M

View 1 Replies View Related

How To Concatenate

Feb 1, 2007

Need help. How to concat into 1 row from the example below:

Table Address
Block Flr Road
2 1 Green
3 - Maine
4 3 Bell
5 2 Prade

Result :
Block 2 Flr 1 Green Road
Block 3 Maine Road
Block 4 Flr 3 Bell Road
Block 5 Flr 2 Prade Road

Cheers!

View 7 Replies View Related

How To Concatenate Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all 

View 5 Replies View Related

Concatenate Int && Var Char - SQL

Oct 1, 2007

Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result  Thanks 

View 2 Replies View Related

Concatenate Variable

Feb 23, 2001

I'm trying to use a variable (@sqlfields1) for building a select statement.
Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":

If @showo1 = 1
select @sqlfields1 = 'o1,'

If @showo5 = 1
select @sqlfields1 = @sqlfields1 + 'o5'

I want @sqlfields1 to be = 'o1, o5'.

Regards,

Abrazame

View 2 Replies View Related

Concatenate String

Oct 25, 2004

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved