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.
-- =================================================================== -- 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.