3

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.

0

2 Answers 2

3

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!

0
1

A UNION ALL plus ROW_NUMBER() do the job.

create table header ([Id] int, [Group] int, [Name] varchar(10), [Value] varchar(10));
insert into header values
(1, 1, 'n1', 'v1'),
(2, 2, 'n2', 'v2');

create table body ([Id] int, [Group] int, [Name] varchar(10), [Value] varchar(10));
insert into body values
(1, 1, 'nb1', 'vb1'),
(2, 1, 'nb2', 'vb2'),
(3, 2, 'nb3', 'vb3');
GO
select row_number() over (order by [Id], [Group]) rn,
       [Group], [Name], [Value]
from
     (
      select [Id], [Group], [Name], [Value]
      from   header
      union all
      select [Id], [Group], [Name], [Value]
      from   body
     ) x
order by [Id], [Group]
GO
rn | Group | Name | Value
:- | ----: | :--- | :----
1  |     1 | n1   | v1   
2  |     1 | nb1  | vb1  
3  |     1 | nb2  | vb2  
4  |     2 | n2   | v2   
5  |     2 | nb3  | vb3  

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.