|
Good Day,
I wonder if it is possible to joins two columns from two tables (ie column 1 from table A and column 2 from table B) into one column.
Here are my tables (note that the columns from both the tables have the same names)
TableA
----------------------
|Column1 | Column2 |
----------------------
| aaaa | 12345 |
----------------------
| bbbp | 12245 |
----------------------
| ccc | 12245 |
----------------------
TableB
----------------------
|Column1 | Column2 |
----------------------
| asd | 12245 |
----------------------
| bbb | 12245 |
----------------------
| ddd | 12245 |
----------------------
The first part of my SQL statement combines the two columns of each table into one column in each table, thus
TableA
------------------
|Column3 |
------------------
| aaa : 12345 |
------------------
| bbbp : 12245 |
------------------
| ccc : 12245 |
------------------
TableB
------------------
|Column4 |
------------------
| asd : 12245 |
------------------
| bbb : 12245 |
------------------
| ddd : 12245 |
------------------
Now, what I want to do is to combine these two columns into one column.
The SQL statement below gives me the following result
Result 
------------------------------
| Column 1 | Column2 |
------------------------------
| bbbp : 12245 | asd : 12245 |
------------------------------
| bbbp : 12245 | bbb : 12245 |
------------------------------
| bbbp : 12245 | ddd : 12245 |
------------------------------
| ccc: 12245 | asd : 12245 |
------------------------------
| ccc: 12245 | bbb : 12245 |
------------------------------
| ccc: 12245 | ddd : 12245 |
------------------------------
What I want is this : 
----------------
| Column1 |
----------------
| bbbp : 12245 |
----------------
| ccc: 12245 |
----------------
| asd : 12245 |
----------------
| bbb : 12245 |
----------------
| ddd : 12245 |
----------------
Here is my SQL statement
SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3, TableB.Column1 + ' : ' + TableB.Column2 AS Column4
FROM TableA FULL OUTER JOIN
TableB ON TableA.Column1 = TableB.Column1
WHERE (TableA.Column1 IN
(SELECT Column1
FROM TableA
WHERE (Column2 = '12245'))) AND (TableB.Column1 IN
(SELECT Column1
FROM TableB
WHERE (Column2 = '12245')))
I've tried all kinds of joins; left outer, right outer, inner, cross. All with the same results.
I would really appreciate your help, as this is kinda urgent!
Thanx a lot!
Rossouw
P.S I am using SQL Server 2005
|
|
|
|
|
Well, maybe I'm missing something but I don't see how that query gives the result set that you say it does. You are joining tables together where A.Column1 = B.Column1, but in the result set that you have put here, the first row shows A.Column1 = bbbp and B.Column1 = asd.
Why can't you just use a UNION to get what you want?
|
|
|
|
|
As David said go with a union, if there are dupes in the result then use distinct.
SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3
FROM TableA
UNION
SELECT TableB.Column1 + ' : ' + TableB.Column1 AS Column3
FROM TableB
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Puurrrrrrfect! 
Thanks a lot! (Note, column1 should have been column2), but nevertheless, it works beautifully!
Enjoy the rest of the day
Rossouw
|
|
|
|
|
Normaly I am reluctant to supply code, I prefer to give hints only - as David did and encourage you to do some research but as you posted some code you were abviously willing to learn.
Look into UNION and UNION ALL as suggested by Jorgen.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: As David said go with a union, if there are dupes in the result then use distinct
Aren't you mixing up UNION with UNION ALL?
UNION is supposed to be distinct while UNION ALL gives you all rows I thought.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Jörgen Andersson wrote: Aren't you mixing up UNION with UNION ALL?
Uhm yes I am 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a select statement which joins various tables. But there are two tables which specify nearly same thing but values are different. Because of which when joining its giving more rows than expected. Now I want these two tables to be included in join.
like for example QCV_Values table it has Type column as "Oxygen", "NEB" and "Sleep". In another table (arusers) these values are as "HME" and "SLP" in the column "GroupName". "Oxygen" and "NEB" comes under samething "HME".
I need to add these tables in to join to avoid duplicacy in the rows. Can any body please help me in achiving this. Except that everything is working fine.
Can anybody please help me in that.
I am putting my query here
And I have to join the Type with the GroupName, but there is no specific table to relate these two.
SELECT InstanceKey, Category, v.QCDesc, oi.CustCenterName, au.MgrName, oi.InsName1, oi.InsName2,
oi.InsName3, oi.TherapyId, oi.PhysNum, oi.TherapyIdSlp, oi.PhysNumSlp, SalesPersonName
FROM dbo.QCV_Instances i
JOIN dbo.vw_OrderIntake oi ON i.InstanceKey=oi.CustNum
--JOIN dbo.Center c ON oi.CustCenterId=c.CenterID
JOIN dbo.arusers au ON oi.CustCenterId=au.CenterID
JOIN dbo.QCV_Values v ON v.QCID=i.QCValueId
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
You have a serious problem, it's called crappy data!
You either create a CASE in the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions. CASE needs a change to a procedure if there is a new mapping, map table needs maintenance.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I concur. But how many times have we all see stuff like this?
I like your mapping table solution, unless this is a one off exercise in which case a case statement will run faster and won't need to be maintained.
|
|
|
|
|
Hi,
Yes, I have done it. As my office is far from my house I couldnt inform the same. But your reply is very usefull. Thanks a lot buddy.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
hi everybody,
In sql server 2005
I have a parent Table
Person(PersonID,PersonName)
PersonID personName
1 abc
2 xyz.
and child Table
BankAccount(AccountId,PersonId,Bank)
AccountId PersonId Bank
1 1 a
2 1 b
3 1 c
4 2 d
5 2 e
I Want to display result this way.
personId PersonName Bank
1 abc a,b,c
2 xyz d,e
Means display child tables rows result in parent table column.
modified on Wednesday, June 30, 2010 4:19 PM
|
|
|
|
|
You fail to mention what database you're using.
In Oracle you can use:
SELECT personid
,personname
,LISTAGG(bank, ',') WITHIN GROUP (ORDER BY bank) AS bank
FROM parent p,child c
WHERE p.personid = c.personid
GROUP BY personid,personname;
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
If you want to search for the answer, look at my last reply to the question below.
If you want the answer look at my second reply to indian143 (20:38 28 Jun '10) four questions down.
|
|
|
|
|
Hi All,
Can anybody please help me in executing a query using For Xml Path. Because query with this if I put it in my SQL Server editor, and run it, its giving errors. Is there any specific way to use For Xml Path. Or should I have anything to use it in my SQL Server 2008.
Because any query from any browser is giving errors. What should i do to get rid of it.
One more thing I found in my SQL Server is when I open the "new query" window in my SQL Server and try to write the "FOR XML" I get the RAW and Auto in the intellisense and I dont find the Path mode coming in the intellisense.
Am I miising any dll or any installation process. Because if I write any query using "For Xml Path" not at all compiling.
Can anybody help me in getting this done. I need to use the "For Xml Path", please help me out. Any links or any kind of help is very very welcoming.
Thanks in advance.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Tuesday, June 29, 2010 9:13 PM
|
|
|
|
|
Could you give us the error detail, transact sql statement as well.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Is your database in 2000 compatibility mode? FOR XML PATH was introduced in 2005.
|
|
|
|
|
I have run this exec to change the compatitbility
"EXEC sp_dbcmptlevel Ppsapp, 90". But it is not accepting 90 the "SQL Server 2005" compatibility level. It is giving following error
"Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.".
Please give me your idea for changing the compatibility level. May be thats why the query is not working.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
80 is Sql Server 2000, 70 is Sql Server 7 etc. You are connecting to a Sql Server 2000 instance not 2008. You cannot use FOR XML PATH. I suggest that you search google for "sql aggregate string concatenation". You may also find this link helps http://www.projectdmx.com/tsql/rowconcatenate.aspx[^]
|
|
|
|
|
Hi All,
We are trying to carry out a mail merge in Word 2007, which should be straight forward only we are running into issues.
We have a SQL Anywhere 10 ODBC Data Source setup on the machine and we are trying to access this database to populate certain areas of a letter. We used the step-by-step mail merge wizard and at Step 3 (Select Recipients), with "Use an Existing List" selected we click on the Browse hyperlink, which opened the "Select Data Source" window, where "+Connect to New Data Source" is selected, which in turn opens the "Data Connection Wizard". The ODBC Data Source previously created is selected and the username and password entered, the test on the connection is successful but when clicking the Ok button we receive the following message:
"The Data Connection Wizard cannot obtain a list of databases from the specified data source"
Does anyone know the steps needed to get around this issue?
Thank you,
Mel
|
|
|
|
|
how the datetime is written in a particular order using sql function?datetime format which i used is
Dec 2 2010 9:40:45:000PM
Dec 02, 2010 11:59:59:000PM
Dec 3 2010 3:45:54:000AM
Dec 3 2010 1:24:23:000AM
Dec 3 2010 12:15:27:000AM
how these datetimes in a correct order?pls help me...
|
|
|
|
|
So you have made the most basic error in data storage, you have stored your dates as varchar. You will have trouble until you reformat your data to datetime. Do it NOW!
However this may work on those formats
Select *
From Table1
Order by convert(datetime,StupidVarcharDateField)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Wow. You got my five for figuring out what the OP wanted. I read the post several times and was left scratching my head. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Chris Meech wrote: was left scratching my head
Been there many times, thankfully between the lot of us someone generally gets the idea the OP is trying to convey.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
convert(datetime,Field) that's it.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
