display the record with parent child relationship

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

Transact-SQL (2005)

display the record with parent child relationship

Author

Topic

sona_Sql
Starting Member

2 Posts

Posted - 2009-08-27 : 07:29:40

Hi All

I am using Sql Server 2005.
WOID AC ACC AL ALO
1 2 3 5 11
2 5 11
3 2 3 5 11
4 2 5

My structure looks like above.
I need the result as:

1 2
1 3
1 5
1 11
2 5
2 11
3 2
3 3
3 5
3 11
4 2
4 5

Consider the first column as a parent and the rest are the child.The
result is the combination of parent with the child.Please can anybody help me out how to do it? Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-27 : 08:19:21

One of the solutions

select WOID, AC from table
union all
select WOID,ACCfrom table
union all
select WOID, AL from table
union all
select WOID, ALO from table
order by WOID

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-27 : 13:43:27

[code]DECLARE @Tempfun TABLE(WOID int, AC int, ACC int, AL int, ALO int)
INSERT INTO @Tempfun
SELECT
1, 2, 3, 5, 11 UNION ALL SELECT
2, 5, 11,null,null UNION ALL SELECT
3, 2, 3, 5, 11 UNION ALL SELECT
4, 2, 5,null,null

SELECT WOID, colx
FROM @Tempfun
UNPIVOT
(
colx FOR cols IN (AC, ACC, AL, ALO)
) AS unpvt
[/code]

Hope can help...but advise to wait pros with confirmation...

sona_Sql
Starting Member

2 Posts

Posted - 2009-08-30 : 07:13:06

Hi

Thank u so much for your replies.Madhivanan's solution worked well.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 01:37:38