Hi,
I have table which has the following values :
ID SEQ Text
1 1 A
2 1 B
3 2 C
4 2 D
5 2 E
6 2 F
7 3 G
The result should be :
1 AB
2 CDEF
3 G
Could somebody help me with this? I could use an cursor but the table
could be large and i want a fast solution.
Thanx in advance...
Hennie
I find myself between rock and hard place.
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 have a table detail where i need to merge the information on two columns. The difficult thing is, the 2nd column must have a different font size when i render the report. Also the merging of the two columns should not generate a space between the contents of both columns. I have done concatenation using the '&', the only problem is the data in the second column inherits the font size of the first column. I have used textboxes, but it generates spaces if the data in the first column contains lesser characters.
Does anyone have a solution?
AJ0520
hi,
please check this query and reply back with the appropriate solution.
len(ltrim(rtrim(exec('select' ' ' + 'pay' +convert(substring(@y1,3,2), varchar 2)))))<>0
here the concept is concatenating two string then that result is used as column and retreiveing data.but this is considering it as string instead of column.
can anyone give an appropriate solution.
Regards,
Uma.
I have a table called Signatures
It contains the fields
Parentobject and [View As]
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
[Code] .....
Ok, I have three tables worth of data. They basically hold data that is keyed into a system for potential clients and include stuff like names, weights, classes of weight, addresses etc.
The main table that holds data on a record by record basis is set up like this:
Tbl_prospect
Prospect_id (PK, unique)
Prospect_batch_id(unique)
Prospect_record_num
Client_code
This table contains the batches (or collections of the data above). Basically a collection of bills is called a batch, so if a batch has 18 bills in it, those 18 bills are keyed and assigned the prospect_batch_id above.
Tbl_prospect_batch
Prospect_batch_id (PK, unique)
Prospect_batch_num
This is the table that contains the various weights and classes for the individual records in the first table (tbl_prospect):
Tbl_prospect_clwt
Prospect_clwt_id (pk, unique)
Prospect_id(unique)
Class
weight
Generally without worrying about the multiple classes and weights it's easy to just join all three tables and pull the fields I need, but now that I have to get multiple classes and weights I have no clue how to actually grab them and delimit them with a comma.
I think I'm only going to be working on tbl_prospect and tbl_prospect_clwt, and if I put a filter in place for the prospect_batch_id I can see the individual record in tbl_prospect as well as the multiple classes and weights in the clwt table, as seen here:
So record 18 of that batch is comprised of two shipments, one that is class 100/weight 1623 and one that is class 70/weight 438, just not sure how to actually grab both of those for each record and delimit them.
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:
"John Smith, Jane Doe, Matthew Jones"
I've been drawing a blank. Anyone have any ideas?
Levi
Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.
current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015
Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015
I'm using the SQL indicated below:
SELECT
DISTINCT C.Field1 As 'Group',
A.Field2 As 'Security Object'
E.Field3 As 'User'
FROM TableA AS A
[code]...
However I'm having difficulties concatenating the user field in the horizontal form indicated.
I have a need to concatenate all Descriptions from a select statementSELECTt_ReviewSection.PeerRevSectionDescriptionFROMt_ReviewSectionWHEREt_ReviewSection.PeerRevID = @lngRevIDORDER BYt_ReviewSection.PeerRevSectionOrderI want to return a single string "section1, section2, section3, section4"based on the multiple rows returned.Any ideas
View 5 Replies View RelatedI have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
Hello
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,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?
View 3 Replies View RelatedHi,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...
View 1 Replies View RelatedHi 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 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 have two columns of type integer that I want to concatenate. I also want to put a "/ " in between the columns.
I have tried doing it in the same way as I would for varchar columns but that doesn't seem to work for integers.
Thanks
Rupal
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.
Thanks,
Jeff
I have a table with 1-4 20 character description fields which need to be concatenated into one value. The data is stored like below
patientid allergy1 allergy2 allergy3 allergy4
100
200 tylenol
300 tylenol bees
The way the data is stored there would never be a allergy3
with an allergy2 being null (the blanks are nulls)
I tried below but it doesn't like the case and concatenation. ANy better ideas
select patientid,allergy_cmt1 +
allergy_cmt2 =
case
when allergy_cmt2 = null then null
else allergy_cmt2 + ','
end
from cms_vw_ptdrugallergies2
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.
Thanks,
Jeff
i have a the following table:
CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO
for each wordID i have many paraID and for each wordID,paraID i have many wordPos
i will use the following convention:
paraID=p
wordID=w
wordPos=wp
i want to concatenate the columns to get the following format:
row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...
where NB1 is the number of wp having w1 and p1
Note: the length of a row may exceed 8000 chars
Hi Guys,
I am converting Access queries to SQL. One query has a string of IIF statements that can concatenate several strings into one field. For instance:
IIf([Hot Dogs]=-1,"* Wants a Hot Dog ","") &
IIf([Hamburger]=-1,"*Wants a Hamburger ","") &
IIf([mustard]=-1,"*add mustard ","") &
IIf([fries]=-1,"*Include Fries ","") &
IIf([Chips]=-1,"*Include Chips ","")
Consequently the output could be:
*Hamburger *add mustard *Include Chips
I know I can use the ‘Case When Then’ statement but how do I concatenate?
Thanx
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
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.
Example:
table x:
column:attritbute
id:123
fname:xyz
lname:zmith
seq:1
address: 123 something
id:123
fname:abc
lname:zmith
address: 123 something
seq:2
Query:
select *
from x
where id = 123
would return 2 records:
ID | Concat | address | fname | lname
123 | abc / xyz | 123 something | xyz | zmith
...
I would like it to return:
ID Concat address
123 | abc / xyz | 123 something ....
Thanks!
Hi All,
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
result
abcdefghijklmnop
as a result?
TIA
Ketan
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
Help would be appreciated. Thank you.
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.
View 2 Replies View RelatedHi,
How do we concate a normal text with an underlined text?
eg: I would like to concate :
textbox1 = my email address:
and
textbox2 = xxx@hotmail.com
into a new textbox3.
expected result: textbox3 : my email address:xxx@hotmail.com
Really appreciate your help.
Thanks
Hi All,
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.
Thanks
Friends,
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
Returns the sum of vehFacID & vehID. Doh!
J.H.
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?.
Example:
A query may currently return:
Title1 - Subtitle1 - Author 1a - etc
Title1 - Subtitle1 - Author 1b - etc
Title1 - Subtitle1 - Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc
When I would like
Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc
My actual SQL code, if you are interested, is:
SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number,
dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb,
dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors
FROM dbo.edition INNER JOIN
dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN
dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN
dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN
dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN
dbo.party ON dbo.role.party_id = dbo.party.party_id