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…

[data here]……
Trailer_NJ
[data here]……
Trailer_PA

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

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

Sample Input

Val
1234-12-NJ
1234-13-NJ
1234-13-NJ
1234-13-NJ
5678-12-PA
5678-13-PA
5678-13-PA
5678-13-PA

Expected Output

P
1234-12-NJ
Q
P
1234-13-NJ
1234-13-NJ
1234-13-NJ
Q
P
5678-12-PA
Q
P
5678-13-PA
5678-13-PA
5678-13-PA
Q

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

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