Dataset:
1 Haemogram Report 1 Routine
Haemogram
1 Haemogram Report 5 Diff. Count
(Mature Cells)
1 Haemogram Report 10 Special
Investigations
2 Haemogram Report 1 Diff. Count
(Mature Cells)
3 C-Reactive Protine 1
4 Urine Analysis 1 Physical
Examination
4 Urine Analysis 8 Chemical
Examination
4 Urine Analysis 10 Microscopic
Examination of Centrifugalised Deposit
5 Haemogram Report 1 E.S.R.
Bhavbhutis attempt:
WITH cteTestGroups AS (SELECT MIN(A.iSrNo) AS iGroupSrNo
, B.cTitle
, MIN(C.iSrNo) AS iSubGroupSrNo
, C.cSubGroup
FROM sReqSlipTestGroup A
JOIN mTestGroups B
ON B.iID = A.iTestGroupID
JOIN lTestGroupsTest C
ON C.iPID = B.iID
JOIN mTests D
ON D.iID = C.iTestID
WHERE A.iPID = 1
GROUP BY B.lNewPage
, A.iSrNo
, B.cTitle
, C.cSubGroup
ORDER BY 1, 2, 3, 4)
SELECT cteTG.*
FROM cteTestGroups cteTG
>My final output I want something like this:
>1 Haemogram Report 1 Routine
>Haemogram
>1 Haemogram Report 5 Diff. Count
>(Mature Cells)
>1 Haemogram Report 10 Special
>Investigations
>5 Haemogram Report 1 E.S.R.
>3 C-Reactive Protine 1
>4 Urine Analysis 1 Physical
>Examination
>4 Urine Analysis 8 Chemical
>Examination
>4 Urine Analysis 10 Microscopic
>Examination of Centrifugalised Deposit
>
>ie. The Row 4 is merged with row 2 and row 9 becomes row 4
>
>The idea is that all the cTitle are clubbed together but in the order they
>were originally selected, thus MIN(A.iSrNo).
>The second grouping is cSubGroup, together but in the order they were defined,
>thus MIN(C.iSrNo)
I think an additional CTE may be what you want:
WITH cteTestGroups (iGroupSrNo, cTitle, iSubGroupSrNo, cSubGroup AS
(SELECT MIN(A.iSrNo), B.cTitle, MIN(C.iSrNo), C.cSubGroup
FROM sReqSlipTestGroup A
JOIN mTestGroups B ON B.iID = A.iTestGroupID
JOIN lTestGroupsTest C ON C.iPID = B.iID
JOIN mTests D ON D.iID = C.iTestID
WHERE A.iPID = 1
GROUP BY B.lNewPage, A.iSrNo, B.cTitle, C.cSubGroup),
cteOrderGroups(cTitle, cOrder) AS
(select cTitle, min(iGroupSrNo)
from cteTestGroups
group by 1)
SELECT cteTG.*
FROM cteTestGroups cteTG
JOIN cteOrderGroups cteOG on cteTG.cTitle = cteOG.cTitle
ORDER BY cteOG.cOrder, cteTG.iSubGroupSrNo
Maybe things could be simplified further, but this at least ought to work.
Set
