Hi I have a table that has two columns whose values need to be combined into one column and pipe delimed into the first one.
But I am not sure how to writed the query.
Here is my atmempt, which by the way does not work, but you might understand what I am trying to do.
update tabmodulesettings
set settingvalue =
(Select settingvalue from tabmodulesettings as t2 where t2.settingname='m2' and t.tabmoduleid=t2.tabmoduleid) + '|' +
(select settingvalue from tabmodulesettings as t3 where t3.settingname='m7' and t.tabmoduleid=t3.tabmoduleid)
from tabmodulesettings t
where settingname='m2'
I have 2 text data type columns that I would like to combine into a new column. I'd also like to add a newline character between each column value when I combine them. I've tried columnA + columnB but that didn't work. How could I do that?
I have a table with multiple rows with the same ID. a) How do I combine all columns into one row with the same ID? b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls? Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls. Here is the stored Proc: SELECT DATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME), COUNT(*) FROM CALL_LOG_MASTER WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL) GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) It returns a table: MONTH DATE TOTAL NUMBER OF CALLS======= ===== ===========1 1 10 1 2 15 My question is: is it possible to combine the Month and Date column into one column. e.g. Date Total Number of Calls==== ==============1/1 101/2 15 Please Help, Thanks in advance :)
I have a query that looks at the stock levels in one warehouse and returns the quantity, have been asked to create a new column that shows the total of the same stock that is available in our two other warehouses.
Have tried this: SELECT ItemCode, WhsCode, InStock FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.WhsCode = TABLE1.WhsCode WHERE WhsCode = '31' or WhsCode = '61' GROUP BY InStock, WhsCode,ItemCode
This returns the results in one column rather than in a seperate column for 31 & 61, I then need to add the two columns together so a total stock is shown.
I realise this may be a basic query but I'm batting my head against a wall at the moment.
I'm sure this has been brought up many times, but I will ask anyway.Let's say I have 2 tables related:Owner:---------o_ido_nameDog:---------d_idd_nameo_id - for Owner table.If the data is laid out aso_id o_name1 Johnd_id d_name o_id1 Skippy 12 Fido 1How can I make a query that will produce the following results:o_id o_name owned dog names1 John Skippy, FidoI think it has something to do with unions but I can't seem to get it. I'musing SQL Server Compact Edition.
Is there a way to combine 2 columns of different types for example varchar and decimal? column1 = varchar, column2 = decimal For example: SELECT column1 + ' - ' + column2 AS CombineColumn FROM TABLE1 Without getting "Error converting data type varchar to numeric." Thanks for any help!
I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID in Employee table. Can any one help? Thanks
I realize I've posted something like this before, but i'm confused how to do something new, what I am trying to do is combine rows of data when certain columns equal one another.
For example, I have to sets of data who have in common the columns ctp_code and a mod_code (they are the same) however they have two other columns called billing_amt and amount_owed that need to be added together (literally taking the data and doing the math, so 100.00 + 100.00 = 200.00)
I would appreciate any help with my following problem... lets say i have...
select A.firstname + '' + B.lastname as fullname, 'Their Home is ' + A.City + ' ' + (select top 1 C.State from States C where C.City = A.City) as Location from tableA A, TableB B Where A.id = b.id
This is not the actual statement but follows the same kinda logic... the problem that i get is that some of the rows in both my fullname column and in my location column show up as null... how would i fix it so for instance even if the state is missing it would still show: their home is LA or if just the last name is available it would show the lastname?
I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).
I have two statements that look like this:
SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
Each statement correctly aggregates the sums, but I need a record that shows me:
CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid
I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).
I have one table with text column(varchar50) and 2 value columns(INT)
it looks like this: c1, c2, c3 1, null, text1 2, null, text2 3, null, text3 4, 3, text4
What i want to do is to combine c3 on row 3 and 4 so the output would look like this: c1, c2, c3 1, null, text1 2, null, text2 3, null, text3 4, 3, text3 ext4
I have tried to use CASE with no luck.
edit: output like this is also fine c1, c2, c3 1, null, text1 2, null, text2 3, null, text3 4, 3, text4 5, null, text3 ext4
It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.
But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.
select col1 from table1 go select col2 from table2 go
Now I want to combine them so table3 is made of col1 and col2.
Is it possible to combine fields and text in a select statement? In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this: SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe FROM GE_Data WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets. If this is possible, or there's a better way to do it, I'd love some more info. Thanks! -Mathminded
select notes, jobid, caller from contact where status in (6) and jobid = 173 I am getting this:
This job will be posted to Monster for 2 weeks. 173 906 Waiting for full budget approval 173 906 TUrns out we're uppin 173 906
What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888
And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
Can you not add a text column to a full text index?? If I change it to a nvarchar it works fine but if I change it to a text column it wont index. Anyone know how to fix this?
I need to separate data within one column into two. For example I have a column in one table that contains first and lastnames separated by a space. I need to export that data in to another table (or create a view) that has a lastname and firstname column. I'm unable to specify character lengths within the substring statement because obviously the data varies so it was suggested I use the instring function within it. However instring doesn't seem to be a recognised function. How come something so simple within excel is causing me such a headache in SQL. I would really appreciate any suggestions.
I actually extracted a set of from an email to excel then to a SQL Server table I'm trying to update a column in a database to clean out everything except the name "George Jeffrey"
bodytext1 column :- " The following person is leaving the department, please update your records accordingly. Person Leaving: George Jeffrey/Users/DVC Division/Agency: People and Community Advocacy Date Leaving: Thursday 10, November 2005 "
I tried to use the replace method
update test.dbo.test set bodytext1 = REPLACE(bodytext1,'The following person is leaving the department','')
but this error came out
Server: Msg 8116, Level 16, State 1, Line 1 Argument data type text is invalid for argument 1 of replace function.
Easy so far. But I what I need is to find out whether the audited table contains text(or nText / image) columns or not. If it contains any text columns the statement should automatically be changed to
select a,b into #inserted from inserted
Remember that the trigger must be usable for any table in the database without manually entering the tables structure or the column names.
Best would be fetching the structure fropm the system views (INFORMATIONSCHEMES)
I'm at my wits end with sql server here, perhaps someone can explain to me why this is happening and hopefully a way around it or to fix it... I recently had this happen while working with a table and exporting the results of a query to an xml file, one field in the table is a text field, before being output to xml I must parse out some of the text usually near the beginning, during which the text is converted to varchar(max), I handled that just fine but when I examine the output file, The tail end of the field has several bytes that gets duplicated... Example...
empowering them to squeeze every drop of performance available. #x0D;.#x00;ble. #x0D;.#x00;</NOTES>
The first #x00; is the terminating character(which ultimatly i need to strip out to make the file comply by the normal xml rules but thats a different issue alltogether) here is another example that has even more data duplicated...
encompassing the production, post production, broadcasting and computer video markets.#x0D;.#x00;ing and computer video markets.#x0D;.#x00;#x00;</NOTES>
If anyone can shed some light on why this is happening or at least how I can prevent it, your help would be greatly appreciated...
A side note on this is that when I run the select statement in the sql manager, the results do not include the duplicated bytes...
Hi,I have a problem, I have a table with a text type column and anvarchar(2000) type column on my MS SQL 2000 Server.I know that the longest text in the text field is 1000 chars. I want tocopy the content the content of the text field into the nvarchar field.I tried convert and cast but after the update there are only 255 charsin the nvarchar field.Best regardsMarc
