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
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
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 :)
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
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
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 RelatedI 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?
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!
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
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?
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?
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 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
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.
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?
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
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.
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.
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!!!!!
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?
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
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
View 5 Replies View RelatedHere is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
Here is a sample of the two databases:
Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124
Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745
I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated.
Thanks
Im trying to combine 2 unrelated tables to create a new table with all the data. Both tables have the same number of rows. if i have 2 tables with 1 column each and 5 rows in each column, eg.
tb1
c1:
1
2
3
4
5
tb2
c2:
a
b
c
d
e
if i run the query
SELECT tb1.[c1], tb2.[c2] INTO tb3 FROM tb1, tb2
i get
tb3:
c1: c2:
1 a
1 b
1 c
1 d
1 e
2 a
2 b
2 c
........
how do i get
tb3:
c1: c2:
1 a
2 b
3 c
4 d
5 e
thanks,
I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.
My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?
Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6
New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
If i have to get the statuses from four different tables but want to get first table disabled , second table active and also have statuses field from the 3rd and 4th table how would i do that?
ALM,
PV,
AD,
ACE
select * from all tbls
where alm.id = pv.id
and alm status = 'disabled'
and pv status = 'active'
I want to include the status field from 3rd and 4th table.
THanks
I have 4 table
TBLA
id
1
2
3
----
TBLB
ID COUNT
1 2
2 2
3 2
----
TBLC
ID COUNT
1 2
2 2
----
TBLD
ID COUNT
1 3
2 3
NEED TO GET OUT PUT LIKE BELOW
ID aCOUNT bCOUNT cCOUNT dCOUNT
1 2
2 3
2 2
2 2
3 2
I have 2 tables. first table contains name and age and the second table contains class and roll num. I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.
View 8 Replies View RelatedHow to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
Thanks
how to
how to combine 2 tables but like this
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
table 2
table Employee on holiday
------------------------
empid start_date end_date shift
-----------------------------------------
12345678 11/04/2007 31/04/2007 10
98765432 01/04/2007 19/04/2007 10
------------------------------------------
how to create a view that show me and combine the 2 tables
all month from first day of the month until the end of the month like this
-----------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 10
12345678 12/04/2007 10
12345678 13/04/2007 10
12345678 14/04/2007 10
.................................
.................................... ...................add the missing date until the end of the month
12345678 31/04/2007 10
98765432 01/04/2007 10
.................................... ...................add the missing date from the start of the month
98765432 02/04/2007 10
................................
..............................
98765432 16/04/2007 10
12345678 17/04/2007 10
98765432 18/04/2007 1
98765432 18/04/2007 10
98765432 19/04/2007 10
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
TNX
I am looking for a way to combine the following two tables to get a result set that would look like this:
1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red
[Code]....
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?