Combine 2 Columns From Different Table
Apr 29, 2015
I want to combine 2 columns from different table.
let said my table1:
id: A1 customername: WesternDigital
id: A2 customername: Sony
id: A3 customername: Samsung
my table2 :
id: A1 customername: Rose
id: A3 customername: John
My output is like that:
customername
WesternDigital, Rose
Sony
Samsung, John
my sql as below:
select table1.customername + table2.customername as customername
from table1
inner join table2 on table1.id = table2.id
how to make the table1 sony appear also even it does not exist in table2?
View 1 Replies
ADVERTISEMENT
Jan 18, 2004
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.
View 4 Replies
View Related
Sep 11, 2014
DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)
SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'
SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________
This is part of a bigger project but I am stuck on this part. I get back 2 result sets
Data Data
101 2013
102 2014
103
104
105
I want to insert the results in a new table 2 columns and get the results below.
New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014
View 2 Replies
View Related
Feb 20, 2007
Hi all,
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.
Thanks
View 6 Replies
View Related
Dec 7, 2005
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 :)
View 2 Replies
View Related
Jun 12, 2006
Hi,
i have a huge db and i wanna combine "date" fields with "time" fields.
eg. date time
03/06/1979 1758
03/09/1979 1759
i wanna datetime
03/06/1979 17:58:00
03/09/1979 17:59:00
View 2 Replies
View Related
Nov 9, 2007
Hi,
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.
Thanks
Garry
View 3 Replies
View Related
Mar 8, 2007
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.
View 20 Replies
View Related
Mar 4, 2007
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?
View 3 Replies
View Related
Feb 28, 2008
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!
View 1 Replies
View Related
May 30, 2008
Hi Guys,
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
View 6 Replies
View Related
Apr 12, 2006
Hey guys,
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)
Code:
id ctp_code mod_code billing_amt amount_owed
1 1 1 100.00 100.00
2 1 1 100.00 100.00
My results should be this:
Code:
id ctp_code mod_code billing_amt amount_owed
1 1 1 200.00 200.00
Any help, thoughts?
View 10 Replies
View Related
May 23, 2008
Hi,
I have 2 tables called Table A, Table B,
In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1
Now want a output like
Data1,
Data2,
Data3
Please help me to get this....
Thank you,
Senthil
View 4 Replies
View Related
Jun 9, 2006
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'
View 5 Replies
View Related
Aug 20, 2007
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?
Thank you
View 2 Replies
View Related
Mar 3, 2008
Hi,
I have the following query :
select uname, count(ID) from tbh_Axis
group by uname
which works fine and displays
Admin3
User18
How can i display the result as :
Admin(3)
User1(8)
When I do this:
select uname + '(' + count(ID) + ')' from tbh_Axis
group by uname
It doesnt work.
View 3 Replies
View Related
Jun 2, 2008
This might be a question with an extremely easy answer.. I don't know but here I go.
I want a report with lets say
|A | B | C |
----------------
I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them?
so lets say I have
select cola as A from table1 where ....
select colb as B from table2...
They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. )
How would I do this? Am I missing something?
View 5 Replies
View Related
Aug 28, 2007
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39
I would like to combine adate with atime to get adatetime
View 5 Replies
View Related
Sep 17, 2007
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).
Any help is appreciated.
View 13 Replies
View Related
Aug 31, 2007
When quering a table with given criteria, For ex:
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?
Please HELP!!!!!
View 4 Replies
View Related
Oct 8, 2007
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?
View 1 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Feb 15, 2012
I have 2 tables First is Student_detail and another is Employee_detail. Student_detail have 14 fields like (stud_Firstname,stud_Lastname...) and Employee_detail have 17 fields like(emp_Firstname,emp_Lastname...).there is no relationship between these two table and also not in a relationship with any other table in my database.This is a structure of my db. but i want to get the records from these two table whose first name is same for both the tables.as well as the result of this query will first show me Student_detail record first and then Employee_detail record.but not in a one row.it should be display in one by one.
Like this way:
HTML Code:
Student_detail :-
stud_First_name stud_Last_name std_city ........
Shrikant Joshi Jalgaon ........
Yogesh Trivedi Malkapur ........
Employee_detail:-
emp_First_name emp_Last_name emp_city ..........
Tushar Patil Mumbai ..........
Shrikant Rane Nasik ..........
Result of a query:-
First_name Last_name City ..........
Shrikant Joshi Jalgaon .........
Shrikant Rane Nasik .........
View 14 Replies
View Related
Apr 5, 2012
I have write two query but its only work one at a time need your expertise what i am doing wrong.
[Category].Category AS project_type,
[SalesManager].SalesManager As Manager,
FROM [Category] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [Category].ID = Projects.Category
FROM [SalesManager] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [SalesManager].ID = Projects.SalesManagerID
View 2 Replies
View Related
Feb 16, 2005
Could you write the simple SQL statement from 'Combine two column in one table '?
I try to use 'Union' which combine two column in two table . thx
View 2 Replies
View Related
Jan 9, 2014
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date
Can they be combined into one query to produce one result table??
View 3 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
View 3 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
View 1 Replies
View Related
Aug 13, 2014
Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.
View 9 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
View 5 Replies
View Related
Sep 17, 2012
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
View 11 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
Table D
View 7 Replies
View Related
Sep 19, 2007
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
View 2 Replies
View Related
