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 have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!
View 6 Replies View RelatedHey 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 RelatedEach patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.
I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:
declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'
Here is the SQL code I'm using:
exec xp_cmdshell 'copy "C:DevelopmentParticipant Limits ReportParticipant Limits Report template.xls" "C:DevelopmentParticipant Limits ReportParticipant Limits Report ' + @currentdate + '.xls"'
GO
The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.
Hello,
I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.
--code within cursor
SELECT '
DECLARE @Result INT
EXEC @Result = DELETE_DOCUMENT
@DocumentID = ' + STR(DocumentID) + ',
@TimeStamp =' + CAST([Timestamp] as varchar) + ',
-- CHECK RESULT AND STATUS
-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
FROM Document
The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values
T
T€‘
T
xnÞ
T!
T"
T#
T$
T%
T&
T'
T(
T)
T*
T+
T,
instead of
0x0000000013540F1C
0x0000000013540F1E
0x0000000013540F1F
0x0000000013786EDE
0x0000000013540F21
0x0000000013540F22
0x0000000013540F23
0x0000000013540F24
0x0000000013540F25
0x0000000013540F26
0x0000000013540F27
0x0000000013540F28
0x0000000013540F29
0x0000000013540F2A
0x0000000013540F2B
0x0000000013540F2C
which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.
Thanks
Sam
Hi,
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.
Here's sample data:
iIncidentID iWorkNoteID iseqNum workNoteAll
1 1 1 notes1(1275 chars)
2 1 1 notes2(1275 chars)
2 1 2 notes3(1275 chars)
3 1 1 notes4(1275 chars)
3 1 2 notes5(1275 chars)
3 1 3 notes6(1275 chars)
Final output
iIncidentID workNoteALL
1 notes1(1275 chars)
2 notes2 notes3
3 notes4 notes5 notes6
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?
I really appreciate it.
Thanks in advance.
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have 2 database table3 as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2
2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls
Using the myGameRecencyAllBalls table ---
I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).
I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.
I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1
however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?
Thank you
lpbcorp
sqlCmd.CommandText = "DECLARE @colName nvarchar(max) " +
"DECLARE @lastRowCount int " +
"DECLARE lastrow_cursor CURSOR SCROLL FOR " +
"(SELECT [" + i.ToString() + "] FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_cursor INTO @lastRowCount " +
"SET @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) " +
"IF @lastRowCount <= 175 " +
"BEGIN UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET [@colName] = 1 WHERE Date = '" + Date + "' " +
"END " +
"ELSE " +
"UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET WeeksSinceDrOver175 = 1 WHERE Date = '" + Date + "' " +
"CLOSE lastrow_cursor " +
"DEALLOCATE lastrow_cursor";
sqlCmd.ExecuteScalar();
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression:
SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent
FROM Agents;
I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing.
Any help greatly appreciated. Please cc me privately so I receive your assistance at once!
TIA,
Arthur
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.
Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
I having a column formatted as char(12) being used for indexing. I am not getting the correct max value back from the statement:
"SELECT MAX(ID) FROM STAGING_TABLE"
i.e.
ID column contains 45,999,1726..The returned val for the query above is 999. Any ideas? TIA
Hi All,
I have a table which contains values like this :
Column 1 Column 2
A 1
A 2
A 3
B 1
B 2
In need the output in this format:
Col1 Col 2
A 1,2,3
B 1,2
Can anyone help me with this ????
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
hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls; protected void Button1_Click(object sender, EventArgs e) { SqlDataSource profilesinsert = new SqlDataSource(); profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString(); profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text; profilesinsert.InsertCommand = "INSERT INTO ProfileComments (Approved) VALUES (@Approved)"; profilesinsert.InsertParameters.Add("Approved", "yes"); profilesinsert.Insert(); } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :)
View 19 Replies View RelatedHello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?
Many thanks
Grant
I have a column of 5 comma-separated-value strings:
stringA, stringB, stringC, stringD, stringE
The strings are GUID's with the hyphen stripped and made all uppercase so they are completely random. I need to be able to remove any one of the strings including the comma, in a stored procedure and I am not sure how to accomplish this.
SELECT tickets
FROM users
WHERE CONTAINS (tickets, @ticket)
IF @@rowcount > 0
REMOVE STUFF HERE
SET @valid = 1
ELSE
SET @valid = 0
So if stringB gets passed in as @ticket then the new value in the column would be :
stringA, stringC, stringD, stringE
Any help is greatly appreciated.
Thank you
dave
I have access to a database's web front end and a limited amount of access to the server.
I am trying to find out what column a certain string is in.
I can see some text on the front end via the web that a user enters and then submits. This information must go to the back end of the database but I dont know where it goes...
I am able to provide a unique Sequence number that it would be linked with and obviously I have the string that im looking for its column name but other then that I dont know what I can do.
I don't have write access to the database so I am unable to make procedures. I am also not really aware how the front end works or if I would be able to find the script that deals with the text after the user hits "save"...
Good Morning,
I'm trying to write a scrip to compate two columns in two different tables to each other (billing_payee to Debtor_name)
The problem i have is that the info in the billing_payee column is CLIENT RE DEBTOR INITAL SURN and the infor in Debtor_name column is [i]DEBTOR TITLE NAME SURN[i]
What i need to be able to do is select all from Billing_Payee table where the Billing payee [i]DEBTOR INIAL SURN[i] is not the same as the info in Debtor_name.
I somehow need to split out the inial - first letter of debtor name and surname.
If i can't do it this way i need some other way of comaparing the two.
I am fairly new to t-SQL however have a pretty good grasp of it.
Please let me know if there is anything else i can provide to make this any easier any help is much appreciated!!
How to separate column FullName to three column LastName, FirstName, and MI? Sample of FullName - Smith, John P.
View 4 Replies View RelatedI want to concaternate all rows that has the same ID and update another table.
Table1:
ID: COL1
1 txt1
1 txt2
1 txt3
2 txt7
3 txt5
3 txt6
OUTPUT:
ID: COL1:
1 txt1, txt2, txt3
2 txt7
3 txt5, txt6
How do I manage this, have tried self join but with no luck.
I have the column of type string in the database
Following is the data in that column
1
2
11
12
21
abc
If i sort the table with the help of this column then the output come in the following manner
1
11
12
2
21
abc
can i improve the order actually i want the output like 1 2 11 12 21 abc
Hi,
I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.
=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')
=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')
Would you please tell me what's wrong? Your suggestions would be appreciated.
Thanks alot.
I am using Derived Column Transformation Editor. I have 3 string values which I would like to combine them into one string and have a newline character inbetween the 3 strings. I cannot see a Char() function similar to TSQL to use for this purpose. I thought about creating a Variable but even to that I don't know how I can assign a newline character.
Any ideas?
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