Help Need in combining Result in Column level

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)

Help Need in combining Result in Column level

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-14 : 15:08:01

Hi,

Below is my sample table and data

With Item as(
Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all
Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all
Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all
Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all
Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all
Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all
Select 7 as ItemId,'ItemName7' as ItemName,700 as position),

Mapping as (
Select 1 as Parent, 2 as child union all
Select 1 as Parent, 3 as child union all
Select 1 as Parent, 4 as child union all
Select 5 as Parent, 6 as child union all
Select 5 as Parent, 7 as child  )

Expected Result:

ParentItemId ParentItemName Parentposition ChildItemId ChildItemName Childposition
1 ItemName1 100 2 ItemName2 200
1 ItemName1 100 3 ItemName3 300
1 ItemName1 100 4 ItemName4 400
5 ItemName5 500 6 ItemName6 600
5 ItemName5 500 7 ItemName7 700

I was thinking to achieve using union all but if i use union all it will combine the result in rows level. but i need in column level. Any help please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-14 : 15:56:18

This is how i implemented. is there any other way to achieve this.

SELECT p.ItemId
    ,p.ItemName
    ,p.position
    ,c.ItemId
    ,c.ItemName
    ,c.position
FROM Item p
JOIN Mapping m ON p.ItemId = m.Parent
JOIN Item c ON m.child = c.ItemId;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources