How to query like below code [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts