This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to print START before group data and print END after group.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 |
Strs --------------- ABC ABC ABC PQR XYZ XYZ WER WER WER |
Expected Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Strings ----------------- ---START--- ABC ABC ABC ---END--- ---START--- PQR ---END--- ---START--- WER WER WER ---END--- ---START--- XYZ XYZ ---END--- |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE tbl_texts ( Strs VARCHAR(50) ) GO INSERT INTO tbl_texts VALUES ('ABC'),('ABC'),('ABC') ,('PQR'),('XYZ'),('XYZ') ,('WER'),('WER'),('WER') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT CASE WHEN rnk = MIN(rnk) OVER (PARTITION BY Strs ORDER BY (SELECT NULL)) THEN '---START---' WHEN rnk = MAX(rnk) OVER (PARTITION BY Strs ORDER BY (SELECT NULL)) THEN '---END---' ELSE Strs END Strings FROM ( SELECT Strs, ROW_NUMBER() OVER (PARTITION BY Strs ORDER BY (SELECT NULL)) rnk FROM ( SELECT Strs, ROW_NUMBER() OVER (PARTITION BY Strs ORDER BY (SELECT NULL)) rnk FROM tbl_texts )t GROUP BY GROUPING Sets ( Strs,(Strs,rnk),Strs ) )T ORDER BY Strs |
Please try the different solution for this puzzle and share it via comment...
