This post discusses how you can take a table with a single column of data and add a header and trailer row to it along with a sequence of numbers that sequence every row except the header row.

Here is our input table. It is intentionally simple. Below that is the output table and below that is the code to make it happen. The data rows from the input table end up being sorted.

Below is the sql server code that creates the output. It is in the form of a stored procedure. The key to making this work is the as “abc” and order by “abc”.

-- ===================================================================
-- Author:		Mike
-- Create date: Oct. 17 2017
-- Description:	Use SQL UNION clause and also sort the output.
--              Here we need to take a table as input and add a header
--              row and a trailer row. We also need to add sequence
--              numbers at the front of every row except the header. 
--              Every row must have exactly 10 characters.
--              The results are inserted into another table.
--              With UNION and UNION ALL the order of the results
--              are not guaranteed. We need to enforce the sorting.
-- ===================================================================
CREATE PROCEDURE [dbo].[ProduceSortedOutput] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @RecordCount int = 0;
	select @RecordCount = Count(*) from [dbo].[UnionSort];
	set @RecordCount = @RecordCount + 2;
	print @RecordCount;
	-- Delete all records from our target table
	delete from [dbo].[UnionSortResult];
	-- Insert the resuts into another table
	insert into [dbo].[UnionSortResult]
	-- Header
	select cast('$Header1 ' as char(9)) + cast('Z' as char(1)) as "abc"
	union all
	-- Detail lines
	select
	   cast(right('000000' + cast((row_number() over (order by DataColumn)) + 1 as varchar(6)),6) as char(6)) 
	   + cast('DD' as char(2)) 
	   + [DataColumn] as "abc"
	   from [dbo].[UnionSort]
	union all 
	-- Trailer
	select --distinct 
	  cast(right('000000' + cast(@RecordCount as varchar(6)),6) as char(6)) 
	  + 'TRLR' as "abc"
	-- Order
	order by "abc";
	-- just show the results to the user by displaying the target table
	select * from [dbo].[UnionSortResult];

END
GO

It turns out that the dollar sign in the header sorts before the zeros. Had the first character been an “A” for example, we would get the following results, which we probably did not want.