CODEWITHSUNDEEP
sqlsql-servert-sql
sourabhgk
sourabhgk

Reputation: 99

SQL Server - Adding header and footer fields in each group

I have data in following format from SQL table

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

I need to add Header at the beginning of each group and Footer at the end of each group.

I need to get following output using SQL query.

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

Awaiting for your reply. Thanks.

Upvotes: 2

Views: 3389

Answers (4)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can also use CROSS APPLY

DECLARE @TempTable TABLE (Code VARCHAR(20),      Name VARCHAR(20))
INSERT INTO @TempTable VALUES
('Finance', 'Bank Charges'),
('Finance', 'Interest Charges'),
('Finance', 'Other Charges'),
('Insurance', 'Premium Charges '),
('Insurance', 'Other Charges')

SELECT X.* FROM 
    ( SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY Code ORDER BY (SELECT NULL)) AS RN 
        FROM @TempTable ) T
    CROSS APPLY (
        SELECT Seq, Code, Code + ' '+ Title AS Name  FROM ( VALUES(1,'Header'),(3, 'Footer')) AS HF (Seq, Title)
        WHERE T.RN = 1
        UNION ALL
        SELECT 2 Seq, T.Code, T.Name
    ) X
ORDER BY Code, Seq, RN

Result:

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

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

Perhaps a simple UNION ALL

Example

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

Returns

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

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

I don't think this is possible via SQL but here is a way you may find it helpful

WITH K AS (
SELECT *, ROW_NUMBER () OVER (PARTITION BY Code ORDER BY Code) RN
FROM T
)
SELECT Code, 
       Name, 
       CASE WHEN RN = 1 
                THEN Code + ' Header'
            WHEN RN = (SELECT MAX(RN) FROM K WHERE K.Code = KK.Code GROUP BY Code)  
                THEN Code + ' Footer'
            ELSE
            '' END AS Title
FROM K KK;

Results:

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need rollup :

ORIGINAL QUESTION ANSWER :

select code, sum(name)
from table t
group by rollup (code);

EDIT : Assuming you need individual rows also if so, then you can do :

select code, name
from (select distinct 1 as sort, Code, concat(code, ' header') as name
      from table t
      union all
      select 2, Code, cast(name as varchar(255)) -- This assumes name has numeric value so, casting is needed.
      from table t
      union all
      select distinct 3, Code, concat(code, ' footer')
      from table t
     ) t
order by code, sort;

Upvotes: 2

Related Questions