How to query like below code [Resolved]

Posted by vishalneeraj-24503 under Sql Server on 7/25/2014 | Points: 10 | Views : 474 | Status : [Member] [MVP] | Replies : 1
PatientTable                           ExamTable

PatientGuid1 ExamGuid1
NULL ExamGuid2
NULL ExamGuid3
NULL ExamGuid4
NULL ExamGuid5
PatientGuid2 ExamGuid1
NULL ExamGuid2
NULL ExamGuid3
NULL ExamGuid4
NULL ExamGuid5
PatientGuid3 ExamGuid1
NULL ExamGuid2
NULL ExamGuid3
NULL ExamGuid4
NULL ExamGuid5





Responses

Posted by: vishalneeraj-24503 on: 7/25/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

Resolved
Hi,after a couple of trying myself,i have found solution like it can be done using row number with partition by clause as

We have to create a temp table and update the query as

IF OBJECT_ID('TEMPDB..#TEMP_PARENTGUID') IS NOT NULL
BEGIN
DROP TABLE #TEMP_PARENTGUID;
END

SELECT ROW_NUMBER() OVER(ORDER BY E.ParentGUID_Id) AS ROW_NUM,
ROW_NUMBER() OVER(PARTITION BY E.ParentGUID_Id ORDER BY E.ParentGUID_Id) AS PARTITIONED,
E.ParentGUID_Id,E.NAME
INTO #TEMP_PARENTGUID
FROM EXAM E
INNER JOIN ParentGUID P
ON P.Parent_GUid = E.ParentGUID_Id

UPDATE #TEMP_PARENTGUID SET ParentGUID_Id = NULL WHERE PARTITIONED >1

SELECT ParentGUID_Id,NAME FROM #TEMP_PARENTGUID


OUTPUT WOULD BE:-
1	ABC
NULL DEF
NULL PQR
2 MNP
NULL OBC
3 OPN
NULL VGF
4 PLM
NULL PQR


vishalneeraj-24503, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response