I'm looking for ideas / options for concatenating several fields into one based on a sequence number. The sequence could go as high as 90 but I'm only interested in concatenating the first 3 sequences. I'd like to compare when seq is the same as the id, it's concatenated and placed in a virtual column.
Hi,there's a method to concatenate fields in a WHERE clause?I've a parameter which represents a name and surname of a person; in the table I've two fields representing one the name and the other the surname. I'd like to do a "LIKE" comparison concatenating Name and Surname field and confronting with my parameter... Is it possible?
Hi,I've a table with two fields representing one Name and the other teh Surname of a persona. I've a to create a Stored Procedure with one input parameter that is a string containing Name and Surname (I don't know in waht order...)What I'd like to do is to concatenate teh fields Name and Surname and confronting with "LIKE" in the "WHERE" clause... something like this:Select Name, Surname FROM XXX where (Name + ' ' + Surname LIKE @parameter OR Surname + ' '+ Name LIKE @parameter).The problem is that I don't know neither if it is possible neither the correct syntax...
I am using SQL server 2000 and trying to do a concatenation of firstname,middle-initial and lastname. SELECT first_name+('mid_ini'= casewhen middle_init is null then ''when middle_init ='' then ''else middle_initend)+nsl_last_nameFROM tbl_names How is it possible to use the CASE condition along with concatenating the fields. Is this possible? I would like my result to be "Bob J Sam". and if there is no middle initial it should be blank or when its null it should be blank. I know you can separate and get the results with the CASE statement,but is this feasible when you want to concatenate? Thank you
I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.
I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.
Example:- ID TxtLn Text
R262965 1 Text 1
R262965 2 Text 2
R262965 3 Text 3
What i'm aiming for is:- ID ConcatText R262965 Text1 Text2 Text3
Can anyone please help me or guide me towards a starting point.
I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.
SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles
This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.
Here is my current SELECT statement:
SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name, m.JobTitle, m.Company, m.Department, m.Address, m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ, m.WorkPhone, m.FAX, m.Email, c.Chapter, m.Active, s.Sector, i.Industry FROM tblMembers m LEFT OUTER JOIN tblChapters c ON m.ChapterID = c.ChapterID LEFT OUTER JOIN tblSectors s ON m.SectorID = s.SectorID LEFT OUTER JOIN tblIndustries i ON m.IndustryID = i.IndustryID WHERE m.DRGInclude = 1
My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.
The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.
Can someone tell me what I am missing? Is there a function that I can use for this?
I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS.
In other words I have a table as such:
1 John Smith 2 Jane Doe 3 Matthew Jones
And I'd like to create one textbox that contains the following:
I have the following problem that I can use some help with :
I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement
I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?
My query and my desired results are below:
select item, test_date from inventory where cat = 'TELE' and itemised_status > 15
I'm trying to get the text of all my SPs saved into text (*.sql) files. My first thought was to use sp_helptext and bcp the table to a text file but this isn't working (see my other post) so thought I'd try another method.
I can get the code from syscomment.text and concatenate the varchar(8000) field together in a text field. Unfortunately this isn't as easy as just text = text + newtext, how is this done?
Or am I doing it all comletely the wrong way? BTW, I have over 150 SPs so I can't save them individually.
First post here and am looking for some help with a issue I have with concatenation.
I am looking to concatenate two columns together into one new column...
ins_type + currency
I have searched for help onthis but for some reason nothing I do works...
Any ideas or suggestions?
Thanks
Nuno
SELECT a.deal_tracking_num AS TemplateDealNum, a.tran_num AS TemplateTranNum, a.reference AS TemplateName, a.ins_type, a.currency, a.trade_date AS TemplateCreated, u.template_tran_num as Misc
FROM ab_tran a, USER_restricted_templates u WHERE a.tran_type = 0 AND a.asset_type = 2 AND a.current_flag = 1 AND a.toolset IN (33,36,15,16) AND a.tran_status = 15 AND a.tran_num *= u.template_tran_num
Hi how would i concatenate columns from my database into one column, these are the columns im trying to concatenate; SELECT Companies.companyName, Companies.companyAddressLine1 , Companies.companyAddressLine2 as Company, Companies.companyAddressLine3, Companies.companyTown, Companies.companyPostcode, AS COMPANY Thank you
I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to
SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table
If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.
The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.
I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to
SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table
If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.
The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.
Hi Guys, I am converting Access SQL to SQL Server and I have two problems: First of all I can’t get the city, state and zip to concatenate. It will not take the "As Location" and it will not allow me to concatenate the fields in the Group By area. Second I ‘m trying to replace a First() function with a Top() function but I’m not having much success. If it is not the first statement I get an error (Incorrect syntax near the keyword 'TOP'.)
Any ideas how I can accomplish what it is I want to do?
Thanx much, Trudye
BEGIN
SELECT Date AS DateEntered, LTRIM(RTrim([ResultsCustName])) AS CustName, Timestamp, LTRIM(RTrim([ResultsPH])) AS Phone, TOP (1) tblEscalationData.Explain_followup AS FirstOfExplain_followup, [ResultsAddr1] AS Address1, [ResultsAddr2] AS Address2, [ResultsCity] AS City, [ResultsST] AS [State], [ResultsZip] AS Zip, ERROR INTO zTmp_Training FROM tblEscalationData LEFT JOIN qry_IssuesPrint ON tblEData.ResultsPKey = qry_IssuesPrint.ResultsPKey LEFT JOIN LOOKUP_DATA ON tblEData.DSLOAN = LOOKUP_DATA.DSLOAN LEFT JOIN qry_IssuesPrintDef ON tblEData.ResultsPKey = qry_IssuesPrintDef.ResultsPKey INNER JOIN qry_CurrentStatus ON tblEData.ResultsPKey = qry_CurrentStatus.ResultsPKey LEFT JOIN tblErrors ON Error = SUBID WHERE tblEData.CSRReporting= 1 AND qry_CurrentStatus.Date Between @Start_Date And @End_Date AND Source = @CallCtr AND ERROR Is Not Null ORDER BY LTrim(RTrim([ResultsCustName]))
END --qry_CSRTraining (Group BY Clause) BEGIN SELECT DateEntered, LTrim(RTrim([CustName])), [Timestamp], LTrim(RTrim([Phone])), [Address1] + ' ' [Address2], [City] + ' ' + [State] + ' ' + [Zip] As Location, [Name], ERROR FROM zTmp_Training GROUP BY DateEntered, [CustName], [Timestamp], [Phone], [Address1], [Address2], Location, [Name], ERROR END
My requirement is to simply output a flat file from an OLE DB source containing three types of records. The output records should look something like this: Type 1 contains field1, field2, field3 Type 2 contains field1, field3, field6 Type3 contains field1, field4, field5
The source table contains all six fields.
If I simply map the input to output columns, I get all fields output in each record, which is not what I want.
So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.
The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.
Is there any way to string these fields together while maintaining the original field length?
I'd like the output to look like ABC FGH XYZ instead of ABCFGHXYZ
I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.
For eg:
column1 abc def hij klm nop
is it possible to write a select statement which would return
I'm wanting to concatenate something that doesnt exist to an already existing field.
What I need to do is add a generic email address to every record in a data view So, what it would do is take the column that does exist and add @domain.com to another column that I would call username So rather than it just appearing as 911234 it would be a derived column saying 911234@domain.com
i've tried using +''+ but I get a space after the number.
I have a table and a view. Let's call the Table dbo.EmpPers and the view dbo.codes
EmpPers holds personal information and Codes holds all the codes for the company, naturally.
There is a field in Emppers called 'EthnicID'. It a number say for example 1, 2, 3 ect...
'Codes' has a field called CodDesc. It's a varchar for example White, Hispanic, African American. ect...
What is the best way to match up the description with the code? Say for all Persons who have have the Hispanic Code '2', I want to add the description to it in the select statement.
Hey guys, I 'm coding my very first stored procedure as accessed by a .NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl
Hi,I have table which has the following values :ID SEQ Text1 1 A2 1 B3 2 C4 2 D5 2 E6 2 F7 3 GThe result should be :1 AB2 CDEF3 GCould somebody help me with this? I could use an cursor but the tablecould be large and i want a fast solution.Thanx in advance...Hennie
I am trying to create a job that, as one of its steps, will kick off aDTS package. As part of the command parameter, I need to concat asystem variable (@@SERVERNAME) to a constant string. I am receiving anerror about incorrect syntax near the +.Here is the code for the job step.-- Add the job stepsEXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep@job_id = @JobID,@step_id = 1,@step_name = N'Import OCC Series Data',@command = N'DTSRun /FD:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +@@SERVERNAME,@database_name = N'',@server = N'',@database_user_name = N'',@subsystem = N'CmdExec',@cmdexec_success_code = 0,@flags = 2,@retry_attempts = 0,@retry_interval = 1,@output_file_name = N'',@on_success_step_id = 0,@on_success_action = 3,@on_fail_step_id = 0,@on_fail_action = 3IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackIf I just try SELECT N'DTSRun /FD:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +@@SERVERNAME, everything works fine. I even tried declaring a localvariable named @command and setting it in the select statement, but nodice.
I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?.
I need to concatenate a field from certain number of rows. I created a function to return the concatenated value which will be a part of another view/procedure to be used for reporting purposes.
final woorkNoteAll will be a part of a query in another view which contains many other fields.
Here's the function. I'm passing an ID and based on that ID, the function returns a string. However, when I tested the function it's giving me a null.
/* --Calling syntax:: Select dbo.getIncidentNotes(187714) as 'Notes' --Function to get all the latest notes for an incident */ CREATE FUNCTION dbo.getIncidentNotes(@iIncidentID int) RETURNS varchar(8000) AS BEGIN DECLARE @allnotes varchar(8000) DECLARE @seqnotes varchar(255) DECLARE @seqnum int DECLARE @counter int SELECT @counter=1
SELECT @seqnum = max(iseqnum) from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID
WHILE (@COUNTER <=@seqnum) BEGIN SELECT @seqnotes = workNoteALL from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID and iSeqNum=@counter SELECT @allnotes = @allnotes + @seqnotes SELECT @COUNTER = @COUNTER + 1 END --While Begin RETURN @allnotes END
Can someone please tell me what's wrong with the code?
there can be more than one Parentobject so I want to concatenate them so I have them on one line
for example
901 Joe Dow 901 Jane Dow
I want one line - | 901 | Joe Dow, Jane Dow
I found something similar as below but I'm getting dups like
901 |Joe Dow , Joe Dow 901 | Jane Dow, Jane Dow
DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values SELECT DISTINCT ParentObject, (SELECT STUFF( (SELECT @Delimiter + s1.[View As] FROM Signatures s2