Sql-server – SQL Server select records from header and body table

sql serverunion

I have 2 tables, Header and Body, with the same columns and multiple groups of records. For each group, I want one record of header and the rest of the body records.

Header

Id Group Name Value
1  1     n1   v1
2  2     n2   v2

Body

Id Group Name Value
1  1     nb1   vb1
2  1     nb2   vb2
3  2     nb3   vb3

Expected Result

Id Group Name Value
1  1     n1    v1
2  1     nb1   vb1
3  1     nb2   vb2
4  2     n2    v2
5  2     nb3   vb3

If it was just one group I could have just done a union but the number of groups isn't fixed.

The Id column in the result is just a row number. The other three columns Group, Name, and Value are more important to me.

The header row is always the first row for that group.

Best Answer

I don't see why this won't work:

IF OBJECT_ID(N'dbo.Header', N'U') IS NOT NULL
DROP TABLE dbo.Header;
IF OBJECT_ID(N'dbo.Body', N'U') IS NOT NULL
DROP TABLE dbo.Body;
GO
CREATE TABLE dbo.Header
(
    iGroup int
    , sName varchar(50)
    , iValue int
);
CREATE TABLE Body
(
    iGroup int
    , sName varchar(50)
    , iValue int
);

INSERT INTO dbo.Header (iGroup, sName, iValue)
VALUES (1, 'test1', 10)
    , (2, 'test2', 20);

INSERT INTO dbo.Body (iGroup, sName, iValue)
VALUES (1, 'test3', 30)
    , (2, 'test4', 40);


;WITH src AS 
(
    SELECT iGroup
        , sName
        , iValue
        , 0 as sort_by
    FROM Header
    UNION ALL 
    SELECT iGroup
        , sName
        , iValue
        , 1 as sort_by
    FROM Body
)
SELECT Id = ROW_NUMBER() OVER (ORDER BY sort_by, iGroup, sName)
    , src.*
FROM src
ORDER BY sort_by, iGroup, sName;

Results:

╔════╦════════╦═══════╦════════╗
║ Id ║ iGroup ║ sName ║ iValue ║
╠════╬════════╬═══════╬════════╣
║  1 ║      1 ║ test1 ║     10 ║
║  2 ║      1 ║ test3 ║     30 ║
║  3 ║      2 ║ test2 ║     20 ║
║  4 ║      2 ║ test4 ║     40 ║
╚════╩════════╩═══════╩════════╝

Also, don't use reserved keywords as column names, please!

Related Solutions

Sql-server – Select multiple rows from one row based on column values

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
       Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Sql-server – Most efficient way to generate a diff

Here is another approach:

SELECT
  di.name,
  di.date,
  x.field,
  x.oldValue,
  x.newValue
FROM
  @diffInput AS di
  LEFT JOIN dbo.myTable AS mt ON
    mt.version = @version
    AND mt.name = di.name
    AND mt.date = di.date
  CROSS APPLY
  (
    SELECT
      'fieldA',
      mt.fieldA,
      di.fieldA
    WHERE
      NOT EXISTS (SELECT mt.fieldA INTERSECT SELECT di.fieldA)

    UNION ALL

    SELECT
      'fieldB',
      mt.fieldB,
      di.fieldB
    WHERE
      NOT EXISTS (SELECT mt.fieldB INTERSECT SELECT di.fieldB)

    UNION ALL

    SELECT
      'fieldC',
      mt.fieldC,
      di.fieldC
    WHERE
      NOT EXISTS (SELECT mt.fieldC INTERSECT SELECT di.fieldC)

    UNION ALL

    ...
  ) AS x (field, oldValue, newValue)
;

This is how it works:

The two tables are joined using an outer join, @diffInput being on the outer side to match your right join.
The result of the join is conditionally unpivoted using CROSS APPLY, where "conditionally" means that each pair of columns is tested individually and returned only if the columns differ.
The pattern of each test condition
```
NOT EXISTS (SELECT oldValue INTERSECT SELECT newValue)
```
is equivalent to your
```
oldValue != newValue
OR (oldValue IS NULL AND newValue IS NOT NULL)
OR (oldValue IS NOT NULL AND newValue IS NULL)
```
only more concise. You can read more about this use of INTERSECT in detail in Paul White's article Undocumented Query Plans: Equality Comparisons.

On a different note, since you are saying,

Both the input table and existing table are expected to be quite large over time

you may want to consider replacing the table variable you are using for the input table with a temporary table. There is a very comprehensive answer by Martin Smith that explores differences between the two:

What's the difference between a temp table and table variable in SQL Server?

In short, certain properties of table variables, like e.g. absence of column statistics, may make them less query optimiser-friendly for your scenario than temporary tables.