Mega Code Archive
UNION same type of columns from different tables
<![CDATA[
7> CREATE TABLE Employees (
8> EmployeeID int NOT NULL ,
9> LastName nvarchar (20) NOT NULL ,
10> FirstName nvarchar (10) NOT NULL ,
11> Title nvarchar (30) NULL ,
12> TitleOfCourtesy nvarchar (25) NULL ,
13> BirthDate datetime NULL ,
14> HireDate datetime NULL ,
15> Address nvarchar (60) NULL ,
16> City nvarchar (15) NULL ,
17> Region nvarchar (15) NULL ,
18> PostalCode nvarchar (10) NULL ,
19> Country nvarchar (15) NULL ,
20> HomePhone nvarchar (24) NULL ,
21> Extension nvarchar (4) NULL ,
22> Photo image NULL ,
23> Notes ntext NULL ,
24> ReportsTo int NULL ,
25> PhotoPath nvarchar (255) NULL
26>
27> )
28> GO
1>
2> CREATE TABLE Customers (
3> CustomerID nchar (5) NOT NULL ,
4> CompanyName nvarchar (40) NOT NULL ,
5> ContactName nvarchar (30) NULL ,
6> ContactTitle nvarchar (30) NULL ,
7> Address nvarchar (60) NULL ,
8> City nvarchar (15) NULL ,
9> Region nvarchar (15) NULL ,
10> PostalCode nvarchar (10) NULL ,
11> Country nvarchar (15) NULL ,
12> Phone nvarchar (24) NULL ,
13> Fax nvarchar (24) NULL
14> )
15> GO
1>
2> INSERT Customers VALUES('1','A','Maria', 'Sales', 'Str. 57', 'Berlin' ,NULL,'12209', 'Germany','111-1111111','111-1111111')
3> INSERT Customers VALUES('2','M','Joe', 'Owner', 'Ave. 231','Vancouver' ,NULL,'05023', 'Mexico', '(222) 222-3332',NULL)
4> INSERT Customers VALUES('3','H','Thomas', 'Sales', 'Sq. 111','London' ,NULL,'1D00P', 'UK', '(444) 444-4444','(444) 444-4444')
5> INSERT Customers VALUES('4','B','Berg', 'Order', 'Blv 8','Toronto' ,NULL,'00222', 'Sweden', '4444-55 55 65','5555-55 55 55')
6> INSERT Customers VALUES('5','S','Moos', 'Sales', 'Fort 57','New York' ,NULL,'68306', 'Germany','6666-66666','6666-77777')
7> INSERT Customers VALUES('6','F','Cite', 'Manager','24 ','Dalles' ,NULL,'67000', 'France', '88.60.15.31','88.60.15.32')
8> INSERT Customers VALUES('7','C','Sommer', 'Owner', 'Araq, 67','Paris' ,NULL,'28023', 'Spain', '(91) 555 22 82','(91) 555 91 99')
9> INSERT Customers VALUES('8','P','Leb', 'Owner', '12 ','Beijing' ,NULL,'13008', 'France', '91.24.45.40','91.24.45.41')
10> INSERT Customers VALUES('9','D','Elizabeth','Manager','23 Blvd.','Tsawassen','BC', 'T2F8M4','Canada', '(604) 555-4729','(604) 555-3745')
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Suppliers (
4> SupplierID int NOT NULL ,
5> CompanyName nvarchar (40) NOT NULL ,
6> ContactName nvarchar (30) NULL ,
7> ContactTitle nvarchar (30) NULL ,
8> Address nvarchar (60) NULL ,
9> City nvarchar (15) NULL ,
10> Region nvarchar (15) NULL ,
11> PostalCode nvarchar (10) NULL ,
12> Country nvarchar (15) NULL ,
13> Phone nvarchar (24) NULL ,
14> Fax nvarchar (24) NULL ,
15> HomePage ntext NULL
16> )
17> GO
1>
2>
3> INSERT Suppliers VALUES(1,'L','N','Manager','L 10','Van',NULL,'2800','Paris','1114108','43844115',NULL)
4> INSERT Suppliers VALUES(2,'Z','D','Manager','V 22','Zaa',NULL,'9999 ZZ','USA',' 1212','(12345) 1210',NULL)
5> INSERT Suppliers VALUES(3,'K','A','Manager','V 12','Lap',NULL,'53120','Finland',' 10956',NULL,NULL)
6> INSERT Suppliers VALUES(4,'G','W','Tester', '1 Hill','Sydney','NSW','2042','Australia','(02) 555-5914','(021) 555-2222',null)
7> INSERT Suppliers VALUES(5,'M','J','Manager','2 St.','Mon','BC','H1J 1C3','Canada','(514) 555-9022',NULL,NULL)
8> INSERT Suppliers VALUES(6,'P','G','Administrator','V 153','Sal',NULL,'84100','Italy','(089) 6547665','(089) 1111111',NULL)
9> INSERT Suppliers VALUES(7,'E','M','Sales','22 Str','Mont',NULL,'71300','France','85.57.00.07',NULL,NULL)
10> INSERT Suppliers VALUES(8,'G','E','Sales','B Ave','Ann',NULL,'74000','France','38.76.98.06','38.76.98.58',NULL)
11> INSERT Suppliers VALUES(9,'F','C','Manager','1 Str','Ste','Calgary','J2S 7S8','Canada','(514) 555-2955','(514) 555-2921',NULL)
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT CompanyName AS Name,
4> Address,
5> City,
6> Region,
7> PostalCode,
8> Country
9> FROM Customers
10> UNION
11> SELECT CompanyName,
12> Address,
13> City,
14> Region,
15> PostalCode,
16> Country
17> FROM Suppliers
18> UNION
19> SELECT FirstName + ' ' + LastName,
20> Address,
21> City,
22> Region,
23> PostalCode,
24> Country
25> FROM Employees
26> go
Name Address City Region PostalCode Country
---------------------------------------- ------------------------------------------------------------ --------------- --------------- ---------- ---------------
A Str. 57 Berlin NULL 12209 Germany
B Blv 8 Toronto NULL 00222 Sweden
C Araq, 67 Paris NULL 28023 Spain
D 23 Blvd. Tsawassen BC T2F8M4 Canada
E 22 Str Mont NULL 71300 France
F 1 Str Ste Calgary J2S 7S8 Canada
F 24 Dalles NULL 67000 France
G 1 Hill Sydney NSW 2042 Australia
G B Ave Ann NULL 74000 France
H Sq. 111 London NULL 1D00P UK
K V 12 Lap NULL 53120 Finland
L L 10 Van NULL 2800 Paris
M 2 St. Mon BC H1J 1C3 Canada
M Ave. 231 Vancouver NULL 05023 Mexico
P 12 Beijing NULL 13008 France
P V 153 Sal NULL 84100 Italy
S Fort 57 New York NULL 68306 Germany
Z V 22 Zaa NULL 9999 ZZ USA
(18 rows affected)
1>
2> drop table Customers;
3> drop table Employees;
4> drop table Suppliers;
5> GO
]]>