SQL Server – How to Display Data Separately and Combined from Two Tables

sql serversql server 2014view

I have two tables, let's call them t1 and t2. They're associated – column c1 in t2 matches the primary key column i1 in t1.

For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?

Table t1:   Table t2:
+----+----+  +----+----+----+
| i1 | x  |  | i2 | c1 | c2 |
+----+----+  +----+----+----+
| 1  | a  |  | 1  | 1  |    |
| 2  | b  |  | 2  | 2  | g  |
| 3  | c  |  | 3  | 3  | h  |
| 4  | d  |  | 4  | 3  | i  |
+----+----+  +----+----+----+

Result
+----+----+----+---+----+----+
| i  | i1 | i2 | x  |c1 | c2 |
+----+----+----+---+----+----+
| 1  |  1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2  |  1 | 1  | a | 1  |NULL|
| 3  |  2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4  |  2 | 2  | b | 2  | g  |
| 5  |  3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6  |  3 | 3  | c | 3  | h  |
| 7  |  3 | 4  | c | 3  | i  |
| 8  |  4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+

NOTE: column i in the output is simply a row number.

Best Answer

Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?

CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));

INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO

CREATE VIEW TestView 
AS
WITH cteInitialSet AS (
    SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
    UNION ALL
    SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2 
    FROM dbo.T1
        JOIN dbo.T2 ON T1.i1 = T2.c1
        )

SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
    i1 ,
    i2 ,
    x ,
    c1 ,
    c2
FROM cteInitialSet
GO

SELECT * FROM dbo.TestView;
Related Question