Solved

Left join of the same table but take 2 different columns .

Posted on 2014-01-24
5
184 Views
Last Modified: 2014-01-31
i have a table A , with columns A.A,A.B,A.C,A.D . For each matching row in the table my query should give 2 rows one row with A.A as original and one row with A.B as original , how can i accomplish this .
Resultset :
1. A.A as original,A.C,A.D
2. A.B as original,A.C,A.D
0
Comment
Question by:FranklinRaj22
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39806940
I think we need to see some mockup data of what you're trying to pull off here, as the term 'original' isn't real intuitive.
0
 

Author Comment

by:FranklinRaj22
ID: 39806962
Table A

ColA           ColB   ColC     ColD

America     USA     1          2
Russia        USSR    5         6

Result should be
Col1        Col2   Col3
America   1        2
USA          1        2
Russia      5       6
USSR         5       6

I am able to accomplish this with but want to know if there are any better ways to do it ...
(select ColA as col1,ColC as col2,ColD as col3 from A) UNION (select ColB as col1,ColC as col2,ColD as col3 from A)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39807207
that is not a "left join", but a UNION ALL of the same table ..
select a.cola, a.colc, a.cold from tableA a
UNION ALL
select a.colb, a.colc, a.cold from tableA a

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39807214
note: UNION should be avoided whenever you can, and use UNION ALL instead.
UNION is performance an implicit DISTINCT over the resulting outcome, which usually is not required, and hence just wasting resources.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39807599
No need to scan the table twice; instead, use a CROSS JOIN.  You could also use a CROSS APPLY.:

SELECT
    CASE WHEN whichCol = 'A' THEN A.ColA ELSE A.ColB END AS Col1,
    A.ColC,
    A.ColD
FROM dbo.tablename A
CROSS JOIN (
    SELECT 'A' AS whichCol UNION ALL
    SELECT 'B'
) AS whichCols
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now