SQL Puzzle | The Header and Footer Puzzle [Difficult Challenge]

This puzzle is bit difficult. The situation is that I have to have a Header before the group and after the group as indicated below…

Header_NJ
[data here]……
Trailer_NJ
Header_PA
[data here]……
Trailer_PA

Consider header as ‘P’ & footer as ‘Q’.

Please check out the sample input and the expected output below-

Sample Input

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--



CREATE TABLE Gr
(
	Val VARCHAR(50)
)
GO

INSERT INTO Gr VALUES
('1234-12-NJ'),
('1234-13-NJ'),
('1234-13-NJ'),
('1234-13-NJ'),
('5678-12-PA'),
('5678-13-PA'),
('5678-13-PA'),
('5678-13-PA')
GO

--

Solution 1

--


SELECT CASE WHEN t = MIN(t) OVER (PARTITION BY Val ORDER BY (SELECT NULL)) THEN 'P'
              WHEN t = MAX(t) OVER (PARTITION BY Val ORDER BY (SELECT NULL)) THEN 'Q'
              ELSE
              Val
         END DataWithHeaderFooter
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Val ORDER BY (SELECT NULL)) t FROM
    (
        SELECT *,ROW_NUMBER() OVER (PARTITION BY Val ORDER BY (SELECT NULL)) grp FROM Gr 
    )t
    GROUP BY GROUPING Sets
    (
        Val,(Val,grp),Val
    )   
)g
ORDER BY Val


--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com