| Author |
 Topic  |
|
|
inbs
Aged Yak Warrior
811 Posts |
 Posted - 02/12/2013 : 08:24:53
|
i have table with 3 key:
agentkey, fatheragentkey, grandfatheragentkey
1 0 4
2 1 4
3 1 4
4 0 0
and another table that hold details of agent:
agentkey agent
0 a
1 b
2 c
3 d
4 e
i want to get a the first table and add the details of the agent (keys,names)
what is the best way to do it?
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/12/2013 : 08:33:09
|
I think you should join the details table 3 times but it would help if you could post the wanted result.
Too old to Rock'n'Roll too young to die. |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/12/2013 : 08:41:38
|
I think....
DECLARE @tab1 TABLE(agentkey int, fatheragentkey int, grandfatheragentkey int)
insert into @tab1
SELECT 1, 0, 4 union all
SELECT 2, 1, 4 union all
SELECT 3, 1, 4 union all
SELECT 4, 0, 0
--and another table that hold details of agent:
DECLARE @tab2 TABLE(agentkey int, agent char(1))
insert into @tab2
SELECT 0, 'a' union all
SELECT 1, 'b' union all
SELECT 2, 'c' union all
SELECT 3, 'd' union all
SELECT 4, 'e'
SELECT t2.agent, t3.agent, t4.agent
FROM @tab1 t1
LEFT JOIN @tab2 t2 ON t2.agentkey= t1.agentkey
LEFT JOIN @tab2 t3 ON t3.agentkey= t1.fatheragentkey
LEFT JOIN @tab2 t4 ON t4.agentkey= t1.grandfatheragentkey
--
Chandu |
|
|
|
inbs
Aged Yak Warrior
811 Posts |
Posted - 02/12/2013 : 13:00:45
|
| but if i have 3 or 4 ...n level , so do i need to join as the number of the level? |
|
|
|
James K
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 02/12/2013 : 13:11:38
|
Does the table with the agentkey, fatheragentkey and grandfatheragentkey (table @tab1 in Chandu's example) have only those 3 columns? If so it does not matter how many levels there are - you don't need to make any changes to the code.
If there are additional columns, or if the table is organized differently, you would of course need to make changes. Can you post the actual table structure with some sample data (like the sample table and data that Chandu created)? |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/12/2013 : 14:03:14
|
Again...but it would help if you could post the wanted result.
Too old to Rock'n'Roll too young to die. |
|
|
|
inbs
Aged Yak Warrior
811 Posts |
Posted - 02/12/2013 : 14:05:00
|
thanks james k
i get the point, i have just 3 columns ,
i look for some more sophisticated, that if i would another level in my table(another column), so i would need to change in the code.
so thanks |
|
|
|
James K
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 02/12/2013 : 15:49:25
|
I don't know if it is any more sophisticated, it is probably less readable, but here is another way using Chandu's test data. If you have more columns then add them to the PIVOT and UNPIVOT columns.DECLARE @tab1 TABLE(agentkey int, fatheragentkey int, grandfatheragentkey int)
insert into @tab1
SELECT 1, 0, 4 union all
SELECT 2, 1, 4 union all
SELECT 3, 1, 4 union all
SELECT 4, 0, 0
--and another table that hold details of agent:
DECLARE @tab2 TABLE(agentkey int, agent char(1))
insert into @tab2
SELECT 0, 'a' union all
SELECT 1, 'b' union all
SELECT 2, 'c' union all
SELECT 3, 'd' union all
SELECT 4, 'e'
SELECT * FROM
(
SELECT
RN,col, agent
FROM
(
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY agentkey) RN
FROM @tab1
)s
UNPIVOT (x FOR col IN (agentkey,fatheragentkey,grandfatheragentkey))U
) t1
INNER JOIN @tab2 t2 ON t1.x = t2.agentkey
) s2 PIVOT (MAX (agent) FOR col IN (agentkey,fatheragentkey,grandfatheragentkey))P If you want to store hierarchical data such as this, there are other ways. Using hierarchyid is one way. http://technet.microsoft.com/en-us/library/bb677290.aspx |
Edited by - James K on 02/12/2013 18:00:28 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/13/2013 : 01:30:16
|
If there is PrimaryKey/UniqueKey column (like ID), then follow below approach for so many levels
DECLARE @tab1 TABLE(ID int,agentkey int, fatheragentkey int, grandfatheragentkey int)
insert into @tab1
SELECT 1, 1, 0, 4 union all
SELECT 2, 2, 1, 4 union all
SELECT 3, 3, 1, 4 union all
SELECT 4, 4, 0, 0
--and another table that hold details of agent:
DECLARE @tab2 TABLE(agentkey int, agent char(1))
insert into @tab2
SELECT 0, 'a' union all
SELECT 1, 'b' union all
SELECT 2, 'c' union all
SELECT 3, 'd' union all
SELECT 4, 'e'
SELECT ID,[agentkey], [fatheragentkey], [grandfatheragentkey]
FROM
(
SELECT ID,m.Cat, t2.agent
FROM
(
SELECT ID,Cat,Val
FROM @tab1
UNPIVOT (Val FOR Cat IN ([agentkey], [fatheragentkey], [grandfatheragentkey]))u
)m
INNER JOIN @tab2 t2
ON t2.agentkey = m.Val
)p
PIVOT(MAX(agent) FOR Cat IN ([agentkey], [fatheragentkey], [grandfatheragentkey]))q
--
Chandu |
|
|
| |
Topic |
|
|
|
join
