Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Parent and Child Updation

Author  Topic 

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-11-02 : 22:10:11
Hi,
I got a table from SSIS Transformation stored in SQL Server. It has the combination of Name and its Ref Name with its ID's.
Need to split this Table into single row with Parent and Child ID updation.

TABLEA:
[ID] [NAME] [MOBILE] [REF_ID] [REF_NAME] [REF_MOBILE]
101 RAMU 9900040000 185 SURESH 9840078000
101 RAMU 9900040000 177 RAMESH 9850098000
185 SURESH 9840078000 101 RAMU 9900040000
185 SURESH 9840078000 177 RAMESH 9850098000
177 RAMESH 9850098000 185 SURESH 9840078000
177 RAMESH 9850098000 101 RAMU 9900040000

Can retain the 1st ROW as parent or else any other Row, And need to apply the ParentID from REF_ID. Output Table will look like.

[ID] [NAME] [MOBILE] [PARENTID]
101 RAMU 9900040000 0
185 SURESH 9840078000 101
177 RAMESH 9850098000 101


Please help me to fix this with SQL query...


Regards,
Kalai

GhostX1
Starting Member

6 Posts

Posted - 2013-11-04 : 11:04:13
I've assumed that the Ref_ID needs to be older (less than the ID).

Select ID, NAME, MOBILE, Case when Min(Ref_ID)<ID Then min(Ref_ID) Else 0 End ParentID
From TableA
Group by ID, NAME, MOBILE
Go to Top of Page

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-11-05 : 01:00:29
It wont work. We cant take MIN REFID for Parent and Child Update.
Its an example with some fields.
For reference I have given some sample Data. Some CIID will refer 4 or 5 ID's and Some ID will refer only one.

Regards,
Kalai
Go to Top of Page
   

- Advertisement -