I have a table like this there could be multiple products with varying path
| Product | StartPoint | EndPoint |
|---|---|---|
| P1 | A | B |
| P1 | B | C |
| P1 | C | D |
| P1 | D | E |
| P1 | E | F |
| P2 | P | Q |
| P2 | Q | R |
| P2 | R | S |
| P2 | S | T |
I want output like this Path Points could vary for different products
| Product | PP1 | PP2 | PP3 | PP4 | PP5 | PP6 | PP7 |
|---|---|---|---|---|---|---|---|
| P1 | A | B | C | D | E | F | null |
| P2 | P | Q | R | S | T | null | null |
I find the similar question but couldn't solve this one In Sql Server how to Pivot for multiple columns
You will need to use recursive cte to find the path. And use PIVOT to present the information horizontally
with rcte as
(
-- Anchor Member. Find the Starting point of the path
select Product, t.StartPoint, t.EndPoint, path = 1
from tbl t
where not exists
(
select *
from tbl x
where x.Product = t.Product
and x.EndPoint = t.StartPoint
)
union all
-- Recursive Member
select t.Product, t.StartPoint, t.EndPoint, path = path + 1
from rcte r
inner join tbl t on t.Product = r.Product
and t.StartPoint = r.EndPoint
)
select Product,
[1] as [PP1], [2] as [PP2], [3] as [PP3], [4] as [PP4],
[5] as [PP5], [6] as [PP6], [7] as [PP7]
from
(
select Product, Point = StartPoint, path
from rcte
union all
-- include the last point
select Product, Point = EndPoint, path + 1
from rcte r
where not exists
(
select *
from tbl x
where x.Product = r.Product
and x.StartPoint = r.EndPoint
)
) d
pivot
(
max(Point)
for [path] in ([1], [2], [3], [4], [5], [6], [7])
) p
[EDIT : dynamic sql version]
-- Find the max no of paths
declare @col_count int;
select @col_count = max(cnt) + 1
from
(
select Product, cnt = count(*)
from tbl
group by Product
) t;
-- Form the columns string
declare @sql nvarchar(max),
@col_1 nvarchar(1000),
@col_2 nvarchar(1000);
with cols as
(
select n = 1
union all
select n = n + 1
from cols
where n < @col_count
)
select @col_1 = string_agg(quotename(n) + ' AS ' + stuff(quotename(n), 2, 0, 'PP'), ','),
@col_2 = string_agg(quotename(n), ',')
from cols;
print @col_1;
print @col_2;
select @sql =
N'with rcte as
(
select Product, t.StartPoint, t.EndPoint, path = 1
from tbl t
where not exists
(
select *
from tbl x
where x.Product = t.Product
and x.EndPoint = t.StartPoint
)
union all
select t.Product, t.StartPoint, t.EndPoint, path = path + 1
from rcte r
inner join tbl t on t.Product = r.Product
and t.StartPoint = r.EndPoint
)
select Product, ' + @col_1 + '
from
(
select Product, Point = StartPoint, path
from rcte
union all
select Product, Point = EndPoint, path + 1
from rcte r
where not exists
(
select *
from tbl x
where x.Product = r.Product
and x.StartPoint = r.EndPoint
)
) d
pivot
(
max(Point)
for [path] in (' + @col_2 + ')
) p'
-- Print out for verification
print @sql
-- Execute the query
exec sp_executesql @sql
