patternsqlMinor

SQL Server select records from header and body table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

headerbodysqlrecordsandserverselectfromtable

Problem

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.

Solution

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!

Code Snippets

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;

Context

StackExchange Database Administrators Q#198680, answer score: 3

Revisions (0)

No revisions yet.