How to combine 2 columns into 1 big column?

Forum Moderators: open

Message Too Old, No Replies

How to combine 2 columns into 1 big column?

With sql server

NooK

12:55 pm on Sep 12, 2008 (gmt 0)

I have 2 columns in a table which contain the same type of data (A userID). The problem is I need to gett all distinct userids from those 2 columns.

Is there any way I can make a query to retrieve both columns as 1 big column, containing the records from column 1 followed by the records of column 2 (Thus this column would contain twice the amount of rows in the table) and then run a DISTINCT on that column to remove duplicates so that I can loop through the userIDs easily?

I am not sure how to go on about doing that.

ZydoSEO

1:36 pm on Sep 12, 2008 (gmt 0)

Not sure which DB you are using. But typically you would use some type of UNION operator. In MS SQL*Server I would do it as follows:

CREATE TABLE tUser1 (
UserID VARCHAR(32) NOT NULL
)

CREATE TABLE tUser2 (
Username VARCHAR(32) NOT NULL
)

INSERT INTO tUser1 (UserID) VALUES ('Jim')
INSERT INTO tUser1 (UserID) VALUES ('Harry')
INSERT INTO tUser1 (UserID) VALUES ('Susan')
INSERT INTO tUser1 (UserID) VALUES ('Mary')

INSERT INTO tUser2 (Username) VALUES ('Jim')
INSERT INTO tUser2 (Username) VALUES ('Tom')
INSERT INTO tUser2 (Username) VALUES ('Rob')
INSERT INTO tUser2 (Username) VALUES ('Joel')
INSERT INTO tUser2 (Username) VALUES ('Susan')
INSERT INTO tUser2 (Username) VALUES ('Melissa')

SELECT UserID AS Username
FROM tUser1
UNION
SELECT Username AS Username
FROM tUser2

The query yields the following result set:

Username
--------------------------------
Harry
Jim
Joel
Mary
Melissa
Rob
Susan
Tom

(8 row(s) affected)

Notice 'Jim' and 'Susan' are not duplicated even though they were in both tables.