Help Need in combining Result in Column level

Question

Help Need in combining Result in Column level

KGJ-Dev

SSCertifiable

Points: 5780
More actions
May 14, 2014 at 12:48 pm

#396754

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:
ParentItemIdParentItemNameParentpositionChildItemIdChildItemNameChildposition
1ItemName11002ItemName2200
1ItemName11003ItemName3300
1ItemName11004ItemName4400
5ItemName55006ItemName6600
5ItemName55007ItemName7700
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I would drop the mapping table and add another column to the Item table named ParentItemID. Then this is a simple recursive cte.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You don't need UNION ALL. UNION will add rows and you need to add columns. To add columns, we use JOIN.

In this case, you need some kind of self join with an intermediate table. Unless that you have several parents, you could use a single table for your hierarchy.

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 )

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;

--Single table alternative

With Item as(

Select 1 as ItemId,'ItemName1' as ItemName,100 as position, 0 AS parentID union all

Select 2 as ItemId,'ItemName2' as ItemName,200 as position, 1 AS parentID union all

Select 3 as ItemId,'ItemName3' as ItemName,300 as position, 1 AS parentID union all

Select 4 as ItemId,'ItemName4' as ItemName,400 as position, 1 AS parentID union all

Select 5 as ItemId,'ItemName5' as ItemName,500 as position, 0 AS parentID union all

Select 6 as ItemId,'ItemName6' as ItemName,600 as position, 5 AS parentID union all

Select 7 as ItemId,'ItemName7' as ItemName,700 as position, 5 AS parentID )

SELECT p.ItemId

,p.ItemName

,p.position

,c.ItemId

,c.ItemName

,c.position

FROM Item p

JOIN Item c ON p.ItemId = c.parentID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Sean Lange (5/14/2014)
I would drop the mapping table and add another column to the Item table named ParentItemID. Then this is a simple recursive cte.

I saw the single parent-child relationship and thought of a single join, but with an unknown number of levels the recursive cte would be a better option.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Luis Cazares (5/14/2014)
Sean Lange (5/14/2014)
I would drop the mapping table and add another column to the Item table named ParentItemID. Then this is a simple recursive cte.
I saw the single parent-child relationship and thought of a single join, but with an unknown number of levels the recursive cte would be a better option.

I suppose that depends. I too jumped to a conclusion, mine was that represents a hierarchy. It could very well describe just a one level relationship. I guess we will have to leave it up to the OP to determine that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

Hi Sean and Luis ,

thanks for your assistance. Unfortunately the design couldn't be changed at this moment. I also thought about that. Appreciate your time on this.