SQL Server - 在每个组中添加页眉和页脚字段

Question

我有来自 SQL 表的以下格式的数据

Code       Name
-----------------------------
Finance    Bank Charges
Finance    Interest Charges
Finance    Other Charges
Insurance  Premium Charges
Insurance  Other Charges
-----------------------------

我需要在每个组的开头添加页眉，在每个组的结尾添加页脚。

我需要使用 SQL 查询获得以下输出。

Code       Name
-----------------------------
Finance    Finance Header
Finance    Bank Charges
Finance    Interest Charges
Finance    Other Charges
Finance    Finance Footer
Insurance  Insurance Header
Insurance  Premium Charges
Insurance  Other Charges
Insurance  Insurance Footer
-----------------------------

等待您的回复。谢谢。

Answer 1

也许是一个简单的UNION ALL

示例

Declare @YourTable Table ([Code] varchar(50),[Name] varchar(50))
Insert Into @YourTable Values 
 ('Finance','Bank Charges')
,('Finance','Interest Charges')
,('Finance','Other Charges')
,('Insurance','Premium Charges')
,('Insurance','Other Charges')


Select Seq=2,* from @YourTable
Union All
Select Distinct 1,[Code],[Code]+' Header' From @YourTable
Union All
Select Distinct 3,[Code],[Code]+' Footer' From @YourTable
Order by [Code],Seq

返回

Seq Code        Name
1   Finance     Finance Header
2   Finance     Bank Charges
2   Finance     Interest Charges
2   Finance     Other Charges
3   Finance     Finance Footer
1   Insurance   Insurance Header
2   Insurance   Premium Charges
2   Insurance   Other Charges
3   Insurance   Insurance Footer