Ask query to make a list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ask query to make a list

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-02-26 : 14:44:34

Hi,
I'm trying to create a procedure to create a list from Table1. In the finished list, each row containing only 2 columns, these 2 columns are the matches of each field in one record to other field of the same record. For example, the first row in Table1 contains the following data:

Aaron Aron Aren Ahren

That would need to be changed to:

Aaron Aron
Aaron Aren
Aaron Ahren
Aron Aaron
Aron Aren
Aron Ahren
Aren Aaron
Aren Aron
Aren Ahren
Ahren Aaron
Ahren Aron
Ahren Aren

I'll run the procedure in SQL Query Analyzer and just create the list, don't need to put the result in a table.

Please show me any help.

Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-26 : 15:16:07

CREATE TABLE #temp
(col1 VARCHAR(20),
col2 VARCHAR(20),
col3 VARCHAR(20),
col4 VARCHAR(20))

INSERT INTO #temp
SELECT 'Aaron','Aron','Aren','Ahren'

SELECT * FROM (SELECT col1 FROM #temp
UNION
SELECT col2 FROM #temp
UNION
SELECT col3 FROM #temp
UNION
SELECT col4 FROM #temp) t
LEFT JOIN (SELECT col1 FROM #temp
UNION
SELECT col2 FROM #temp
UNION
SELECT col3 FROM #temp
UNION
SELECT col4 FROM #temp) t2 ON t.col1 <> t2.col1

DROP TABLE #temp

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-26 : 15:16:28

[code]DECLARE @Table1 TABLE (Col1 CHAR(5), Col2 CHAR(5), Col3 CHAR(5), Col4 CHAR(5))
INSERT @Table1 (Col1, Col2, Col3, Col4)
SELECT 'Aaron', 'Aron', 'Aren', 'Ahren'

SELECT *
FROM ( SELECT Col1 AS Col
FROM @Table1
UNION
SELECT Col2
FROM @Table1
UNION
SELECT Col3
FROM @Table1
UNION
SELECT Col4
FROM @Table1
) a
CROSS JOIN ( SELECT Col1 AS Col
FROM @Table1
UNION
SELECT Col2
FROM @Table1
UNION
SELECT Col3
FROM @Table1
UNION
SELECT Col4
FROM @Table1
) b
WHERE a.Col <> b.Col[/code]

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-02-26 : 15:50:37

Thank you jackv and jdaman.
What I'm processing is a table with 1000s records in there.
Aaron Aron Aren Ahren
is just one record, can we use some kind of loop to process each record and make those matches within each record.
Ex. Aaron Aron Aren Ahren
Ben Benj Benjm
.....

Will make a list like:
Aaron Aron
Aaron Aren
Aaron Ahren
Aron Aaron
Aron Aren
Aron Ahren
Aren Aaron
Aren Aron
Aren Ahren
Ahren Aaron
Ahren Aron
Ahren Aren
Ben Benj
Ben Benjm
Benj Ben
Benj Benjm
Benjm Ben
Benjm Benj
....

Thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-26 : 16:47:28

You will need to create an id column to tie the rows together. In 2000 you can do this by inserting your original record set into a new table containing an IDENTITY column like so:

DECLARE @Table1 TABLE (Col1 CHAR(5), Col2 CHAR(5), Col3 CHAR(5), Col4 CHAR(5))
INSERT @Table1 (Col1, Col2, Col3, Col4)
SELECT 'Aaron', 'Aron', 'Aren', 'Ahren' UNION
SELECT 'Jake', 'Jack', 'Jak', 'Jax' UNION
SELECT 'Math', 'Mat', 'Matt', 'Maht'

DECLARE @Table2 TABLE ( id INT IDENTITY(1, 1), Col1 CHAR(5), Col2 CHAR(5), Col3 CHAR(5), Col4 CHAR(5) )
INSERT @Table2 (Col1, Col2, Col3, Col4)
SELECT Col1, Col2, Col3, Col4 FROM @Table1

SELECT  a.Col,
        b.Col
FROM    ( SELECT    id, Col1 AS Col
          FROM      @Table2
          UNION
          SELECT    id, Col2
          FROM      @Table2
          UNION
          SELECT    id, Col3
          FROM      @Table2
          UNION
          SELECT    id, Col4
          FROM      @Table2
        ) a
        JOIN ( SELECT   id, Col1 AS Col
               FROM     @Table2
               UNION
               SELECT   id, Col2
               FROM     @Table2
               UNION
               SELECT   id, Col3
               FROM     @Table2
               UNION
               SELECT   id, Col4
               FROM     @Table2
             ) b ON a.Col <> b.Col
                    AND a.id = b.id

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-02-26 : 17:40:12

Thank you again, jdaman.
It works, I added another identity field "RecordID" in the table, then used your JOIN on a.Col<>b.Col and a.RecordID=b.RecordID
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources