How to get result(output) column values based on g

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)

How to get result(output) column values based on g

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2011-12-21 : 13:57:08

Hello All,

please help me in this scenario, i tried a lot but somehow seems to be my view is wrong

Information:

Rownum MotherGrid ChildGrid PullarValue
2 1asAUSTRGIB 1asAUSTRaus MTHR
3 1asAUSTRGIB 1asAUSTRbal CHILD
4 1asAUSTRGIB 1asAUSTRhld MTHR
5 1auCLEMEGIB 1auCLEMEbal CHILD
6 1auCLEMEGIB 1auCLEMEbla MTHR
7 1auCLEMEGIB 1auCLEMEcgl MTHR
8 1auCLEMEGIB 1auCLEMEche MTHR
9 1BBDO_AFFIL 1auCLEME NULL
10 1BBDO_AFFIL 1coCOLOM NULL
11 7chChinabas 1chChinkinda MTHR
12 7chChinabas 1chChinBunda MTHR
13 7chChinabas 1chChinDamma MTHR
14 8AusDimmy 7chChinabas null
15 9abChandGIB 8AusDimmy null
16 9abChandGIB 8AusLitteur CHILD
17 9abChandGIB 8AusKroneuer MTHR

in the above roe_num is just for explanation purpose

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

Rownum MotherGrid ChildGrid PullarValue ResultCol_Output
2 1asAUSTRGIB 1asAUSTRaus MTHR 1asAUSTRbal
3 1asAUSTRGIB 1asAUSTRbal CHILD 1asAUSTRbal
4 1asAUSTRGIB 1asAUSTRhld MTHR 1asAUSTRbal
5 1auCLEMEGIB 1auCLEMEbal CHILD 1auCLEMEbal
6 1auCLEMEGIB 1auCLEMEbla MTHR 1auCLEMEbal
7 1auCLEMEGIB 1auCLEMEcgl MTHR 1auCLEMEbal
8 1auCLEMEGIB 1auCLEMEche MTHR 1auCLEMEbal
9 1BBDO_AFFIL 1auCLEME NULL NULL
10 1BBDO_AFFIL 1coCOLOM NULL NULL
11 7chChinabas 1chChinkinda MTHR 8AusLitteur
12 7chChinabas 1chChinBunda MTHR 8AusLitteur
13 7chChinabas 1chChinDamma MTHR 8AusLitteur
14 8AusDimmy 7chChinabas null 8AusLitteur
15 9abChandGIB 8AusDimmy null 8AusLitteur
16 9abChandGIB 8AusLitteur CHILD 8AusLitteur
17 9abChandGIB 8AusKroneuer MTHR 8AusLitteur

---> 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 06:20:16

something like


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')


UPDATE t
SET t.ResultCol_Output= t1.ResultCol_Output
FROM MyParTable t
CROSS APPLY (SELECT TOP 1 ChildGrid AS  ResultCol_Output
             FROM MyParTable
             WHERE MotherGrid=t.MotherGrid
             AND PullarValue='CHILD'
             ) t1
             
;WITH CTE
AS
(
SELECT MotherGrid,ChildGrid,
CAST( MotherGrid AS varchar(max)) AS path,
1 AS Level,t.PullarValue
FROM MyParTable t

UNION ALL
SELECT t.MotherGrid,t.ChildGrid, c.path+ '/' + t.MotherGrid ,
Level +1 ,t.PullarValue
FROM CTE c
INNER JOIN MyParTable t
ON t.ChildGrid = c.MotherGrid
)       
UPDATE t
SET t.ResultCol_Output = x.ChildGrid
FROM MyParTable t
CROSS APPLY(
             SELECT DISTINCT t1.ChildGrid FROM (SELECT LEFT([path],CHARINDEX('/',[path]+'/')-1) AS val,* FROM CTE) c
             INNER JOIN (SELECT LEFT([path],CHARINDEX('/',[path]+'/')-1) AS val,MAX([level]) AS lvl
                         FROM CTE 
                         GROUP BY LEFT([path],CHARINDEX('/',[path]+'/')-1))c2
                         ON c2.lvl=c.[Level]
                         AND c2.val= c.val
              INNER JOIN MyParTable t1
              ON t1.MotherGrid = c.MotherGrid
              AND t1.PullarValue='CHILD'
              WHERE c.Level>1
              AND t.MotherGrid=c.val
               
              )x
   WHERE     ResultCol_Output IS NULL      
             
              
              SELECT *
             FROM MyParTable 


output
-----------------------------------------------------
MotherGrid	ChildGrid	PullarValue	ResultCol_Output
1asAUSTRGIB	1asAUSTRaus	MTHR	1asAUSTRbal
1asAUSTRGIB	1asAUSTRbal	CHILD	1asAUSTRbal
1asAUSTRGIB	1asAUSTRhld	MTHR	1asAUSTRbal
1auCLEMEGIB	1auCLEMEbal	CHILD	1auCLEMEbal
1auCLEMEGIB	1auCLEMEbla	MTHR	1auCLEMEbal
1auCLEMEGIB	1auCLEMEcgl	MTHR	1auCLEMEbal
1auCLEMEGIB	1auCLEMEche	MTHR	1auCLEMEbal
1BBDO_AFFIL	1auCLEME	NULL	NULL
1BBDO_AFFIL	1coCOLOM	NULL	NULL
7chChinabas	1chChinkinda	MTHR	8AusLitteur
7chChinabas	1chChinBunda	MTHR	8AusLitteur
7chChinabas	1chChinDamma	MTHR	8AusLitteur
8AusDimmy	7chChinabas	NULL	8AusLitteur
9abChandGIB	8AusDimmy	NULL	8AusLitteur
9abChandGIB	8AusLitteur	CHILD	8AusLitteur
9abChandGIB	8AusKroneuer	MTHR	8AusLitteur

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources