help merge same column data

okboy · July 23rd, 2007, 01:44 AM

example :my table( fruits) the column are oren and apple

[u]oren</u>
a
b
c
d
e

[u]apple</u>
b
c
a
l
k

and i want to display the table like below

oren apple
a a
b b
c c

so what is the query??
------------------------------------------------------------

i try to entry this query

select * from fruit where oren in (select apple from fruit)
but the result is not my expected..
oren apple
a b
b c
c a

happygv · July 23rd, 2007, 08:25 AM

That is because, the row with Oren having "a", there is "b" with Apple and so on...

_________________________
- Vijay G
Strive for Perfection

happygv · July 23rd, 2007, 08:41 AM

I can't think of other way of doing it. ;)

Code:

select oren into #oren from table1
select apple into #apple from table1

select * from #oren, #apple where #oren.oren = #apple.apple
drop table #oren
drop table #apple

Hope that helps.
Cheers

_________________________
- Vijay G
Strive for Perfection

Jeff Mason · July 23rd, 2007, 09:04 AM

How about a self join of the fruits table:

SELECT F1.oren, F2.apple
FROM Fruits F1
INNER JOIN Fruits F2 ON F1.oren = F2.apple

But why would you have a table structured this way? I'm having trouble understanding the relationship between the two columns you are trying to express.

Jeff Mason
[email protected]

okboy · July 23rd, 2007, 08:47 PM

Quote:

quote:Originally posted by Jeff Mason
How about a self join of the fruits table:

SELECT F1.oren, F2.apple
FROM Fruits F1
INNER JOIN Fruits F2 ON F1.oren = F2.apple

But why would you have a table structured this way? I'm having trouble understanding the relationship between the two columns you are trying to express.

Jeff Mason
[email protected]

okboy · July 23rd, 2007, 09:09 PM

Quote:

quote:Originally posted by Jeff Mason

Quote:

How about a self join of the fruits table:

SELECT F1.oren, F2.apple
FROM Fruits F1
INNER JOIN Fruits F2 ON F1.oren = F2.apple

But why would you have a table structured this way? I'm having trouble understanding the relationship between the two columns you are trying to express.

Jeff Mason
[email protected]

thank you can work....:)
actually my table still have 1 column for ID and the ID is based on oren column, so because of this i need to find out the ID for the same data...

Peso · July 24th, 2007, 01:36 AM

Code:

-- Prepare sample data
DECLARE    @Sample TABLE (oren CHAR, Apple CHAR)

INSERT    @Sample
SELECT    'a', 'b' UNION ALL
SELECT    'b', 'c' UNION ALL
SELECT    'c', 'a' UNION ALL
SELECT    'd', 'l' UNION ALL
SELECT    'e', 'k'

-- Peso
SELECT        theValue AS oren,
        theValue AS Apple
FROM        (
            SELECT    oren AS theValue
            FROM    @Sample

            UNION ALL

            SELECT    Apple
            FROM    @Sample
        ) AS d
GROUP BY    theValue    
HAVING        COUNT(*) > 1

-- Mason
SELECT        s1.oren,
        s2.apple
FROM        @Sample AS s1
INNER JOIN    @Sample AS s2 ON s2.oren = s1.apple