December 21, 2011 at 12:01 pm
Hello All,
please help me in this scenario, i tried a lot but somehow seems to be my view is wrong
Actual Values
RownumMotherGridChildGridPullarValueResultCol_Output
21asAUSTRGIB1asAUSTRausMTHR
31asAUSTRGIB1asAUSTRbalCHILD
41asAUSTRGIB1asAUSTRhldMTHR
51auCLEMEGIB1auCLEMEbalCHILD
61auCLEMEGIB1auCLEMEblaMTHR
71auCLEMEGIB1auCLEMEcglMTHR
81auCLEMEGIB1auCLEMEcheMTHR
91BBDO_AFFIL1auCLEMENULL
101BBDO_AFFIL1coCOLOMNULL
117chChinabas1chChinkindaMTHR
127chChinabas1chChinBundaMTHR
137chChinabas1chChinDammaMTHR
148AusDimmy7chChinabasnull
159abChandGIB8AusDimmynull
169abChandGIB8AusLitteurCHILD
179abChandGIB8AusKroneuerMTHR
Information:
1)
MotherGrid will have group of values, in each group it may have one Pullarvalue=Child, if this is the case then outputcolumn(ResultCol_Output) will be
CgildGrid column value for the entire group.
(for example 1st set & 2nd set above excel)
2)
if the given group MotherGrid value is not ends with GIB then you may have leveldown parent
so 7chchinabas has all pullarvalues to be "mthr" thereis no "child" so check any exist child is equal to parent
(ie., 11,12,13 parent equal to 14 child, since 14 parent not ends with GIB then next 14 parent exist in 15 child also 15th parent ends with GIB so it all these rows will have childgrid of child value which is 8auslitteur).
3) if no pullar value given & there no parent = child (i mean level down like we did in step 2) then just replace with what ever child grid value is or simply null also fine
please assit me,
expected outcome will be
Expected OutCome
RownumMotherGridChildGridPullarValueResultCol_Output
21asAUSTRGIB1asAUSTRausMTHR1asAUSTRbal
31asAUSTRGIB1asAUSTRbalCHILD1asAUSTRbal
41asAUSTRGIB1asAUSTRhldMTHR1asAUSTRbal
51auCLEMEGIB1auCLEMEbalCHILD1auCLEMEbal
61auCLEMEGIB1auCLEMEblaMTHR1auCLEMEbal
71auCLEMEGIB1auCLEMEcglMTHR1auCLEMEbal
81auCLEMEGIB1auCLEMEcheMTHR1auCLEMEbal
91BBDO_AFFIL1auCLEMENULLNULL
101BBDO_AFFIL1coCOLOMNULLNULL
117chChinabas1chChinkindaMTHR8AusLitteur
127chChinabas1chChinBundaMTHR8AusLitteur
137chChinabas1chChinDammaMTHR8AusLitteur
148AusDimmy7chChinabasnull8AusLitteur
159abChandGIB8AusDimmynull8AusLitteur
169abChandGIB8AusLitteurCHILD8AusLitteur
179abChandGIB8AusKroneuerMTHR8AusLitteur
---> sql code script Starts Here
create table MyParTable
(
MotherGrid varchar(40),
ChildGrid varchar(40),
PullarValue varchar(40),
ResultCol_Output varchar(40)
)
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1asAUSTRGIB', '1asAUSTRaus', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1asAUSTRGIB', '1asAUSTRbal', 'CHILD')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1asAUSTRGIB', '1asAUSTRhld', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1auCLEMEGIB', '1auCLEMEbal', 'CHILD')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1auCLEMEGIB', '1auCLEMEbla', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1auCLEMEGIB', '1auCLEMEcgl', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1auCLEMEGIB', '1auCLEMEche', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1BBDO_AFFIL', '1auCLEME', NULL)
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('1BBDO_AFFIL', '1coCOLOM', NULL)
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('7chChinabas', '1chChinkinda', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('7chChinabas', '1chChinBunda', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('7chChinabas', '1chChinDamma', 'MTHR')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('8AusDimmy', '7chChinabas', null)
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('9abChandGIB', '8AusDimmy', null)
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('9abChandGIB', '8AusLitteur', 'CHILD')
insert into MyParTable(MotherGrid ,ChildGrid ,PullarValue) values ('9abChandGIB', '8AusKroneuer', 'MTHR')
---> sql code script Ends Here
Thanks in advance
Thanks a ton in advance
i tried a lot, i need another view
please ALLL
asita
December 21, 2011 at 12:26 pm
Something like this?
IF OBJECT_ID(N'tempdb..#MyParTable') IS NOT NULL
DROP TABLE #MyParTable ;
CREATE TABLE #MyParTable
(MotherGrid VARCHAR(40),
ChildGrid VARCHAR(40),
PullarValue VARCHAR(40),
ResultCol_Output VARCHAR(40)) ;
INSERT INTO #MyParTable
(MotherGrid, ChildGrid, PullarValue)
VALUES ('1asAUSTRGIB', '1asAUSTRaus', 'MTHR'),
('1asAUSTRGIB', '1asAUSTRbal', 'CHILD'),
('1asAUSTRGIB', '1asAUSTRhld', 'MTHR'),
('1auCLEMEGIB', '1auCLEMEbal', 'CHILD'),
('1auCLEMEGIB', '1auCLEMEbla', 'MTHR'),
('1auCLEMEGIB', '1auCLEMEcgl', 'MTHR'),
('1auCLEMEGIB', '1auCLEMEche', 'MTHR'),
('1BBDO_AFFIL', '1auCLEME', NULL),
('1BBDO_AFFIL', '1coCOLOM', NULL),
('7chChinabas', '1chChinkinda', 'MTHR'),
('7chChinabas', '1chChinBunda', 'MTHR'),
('7chChinabas', '1chChinDamma', 'MTHR'),
('8AusDimmy', '7chChinabas', NULL),
('9abChandGIB', '8AusDimmy', NULL),
('9abChandGIB', '8AusLitteur', 'CHILD'),
('9abChandGIB', '8AusKroneuer', 'MTHR') ;
SELECT #MyParTable.MotherGrid,
#MyParTable.ChildGrid,
#MyParTable.PullarValue,
COALESCE(#MyParTable.ResultCol_Output, Child.ChildGrid,
Child2.ChildGrid2) AS ResultCol_Output
FROM #MyParTable
OUTER APPLY (SELECT ChildGrid
FROM #MyParTable AS Child
WHERE Child.MotherGrid = #MyParTable.MotherGrid
AND Child.PullarValue = 'CHILD') AS Child
OUTER APPLY (SELECT ChildGrid AS ChildGrid2
FROM #MyParTable AS Child2
WHERE Child2.ChildGrid = #MyParTable.MotherGrid
AND Child2.MotherGrid NOT LIKE '%GIB') AS Child2 ;
Does that do what you're looking for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 1:02 pm
Hi GSquared,
Thanks alot for your such an immediate repsonse,
i greatful to you,
may be my question is confusing you,
in your response it accomplished 1 & 3, but 2 is need litttle more
Here is my explanation this applies for last 7 rows (Rows from 10 onwards)
10,11,12 parent is 7chChinabas (it doesn't have GIB in it) , so it check it may be sub child to another parent (in our example, yes it is
for rownumber 13 8AUSDimmy is parent for 7chChinabas) still 8AUsDimmy is dont have 'GIB' in it so check to see it may be sub child for another level
so here we found 14 row which parent is 9adChandGib it has Gib in it, so check this group has pullar value = 'child' then apply this child value to the all sub level childrens which we gine through so i.e., 10,11,12,13,14,15,16 will have Result column as same value since they all are under parent-child (of multi livel)
so expected out come will be
RownumMotherGridChildGridPullarValueResultCol_Output
11asAUSTRGIB1asAUSTRausMTHR1asAUSTRbal
21asAUSTRGIB1asAUSTRbalCHILD1asAUSTRbal
31asAUSTRGIB1asAUSTRhldMTHR1asAUSTRbal
41auCLEMEGIB1auCLEMEbalCHILD1auCLEMEbal
51auCLEMEGIB1auCLEMEblaMTHR1auCLEMEbal
61auCLEMEGIB1auCLEMEcglMTHR1auCLEMEbal
71auCLEMEGIB1auCLEMEcheMTHR1auCLEMEbal
81BBDO_AFFIL1auCLEMENULLNULL
91BBDO_AFFIL1coCOLOMNULLNULL
107chChinabas1chChinkindaMTHR8AusLitteur
117chChinabas1chChinBundaMTHR8AusLitteur
127chChinabas1chChinDammaMTHR8AusLitteur
138AusDimmy7chChinabasnull8AusLitteur
149abChandGIB8AusDimmynull8AusLitteur
159abChandGIB8AusLitteurCHILD8AusLitteur
169abChandGIB8AusKroneuerMTHR8AusLitteur
please helpme here squared
thanks again to you
December 21, 2011 at 1:05 pm
Can this cascade more levels than that? Like a hierarchy? If so, you'll need to query it with a recursive function. Take a look at "Common Table Expressions" on http://www.MSDN.com and see if that helps you resolve the hierarchy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 1:08 pm
Yes it is kind of hierarchy, but may limited to 3 levels as in my example,
even if it is more that that ignore remain
just enough for that in example GSquared
GSquared,
December 21, 2011 at 1:11 pm
If it's truly limited to three levels, just take what I wrote, and add another Outer Apply to get to the data for the third level, and add that to the Coalesce statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 1:41 pm
Sorry to ask you,
could you please assistme regarding to this query with colease.
please squared
asitti
December 21, 2011 at 4:30 pm
please GSquared
help me
thank you
asitti
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply