I have a CTE where i'm getting the details as below.I want this result to be pivoted based on the position.But the column name need to be same(if not can be different).
Sample output is given below,Can anyone suggest or help on this?
PostgreSQL version 11
Table have nearly 2000 ID's and up-to 10 links. Sample input:
ID LINK Position
757 "https://www.shop.com/product/5/_/5_142_40.jpg" 4
757 "https://www.shop.com/product/3/_/3_347_44.jpg" 2
757 "https://www.shop.com/product/4/_/4_207_43.jpg" 3
757 "https://www.shop.com/product/2/_/2_416_44.jpg" 1
758 "https://www.shop.com/product/5/_/5_142_39.jpg" 4
758 "https://www.shop.com/product/4/_/4_207_42.jpg" 3
758 "https://www.shop.com/product/3/_/3_347_43.jpg" 2
758 "https://www.shop.com/product/2/_/2_416_43.jpg" 1
760 "https://www.shop.com/product/5/_/5_142_42.jpg" 4
Sample output:
ID link link link link
757 .../2/_/2_416_44.jpg ../3/_/3_347_44.jpg ../4/_/4_207_43.jpg ../5/_/5_142_40.jpg
758 .../2/_/2_416_43.jpg ../3/_/3_347_43.jpg ../4/_/4_207_42.jpg. ../5/_/5_142_39.jpg
760 ../5/_/5_142_42.jpg
You can use row_number() and conditional aggregation. The column names need to be different though:
with mycte as (...) -- your cte here
select
id,
max(link) filter(where rn = 1) link1,
max(link) filter(where rn = 2) link2,
max(link) filter(where rn = 3) link3,
max(link) filter(where rn = 4) link4
from (
select c.*, row_number() over(partition by id order by position) rn
from mycte c
) t
group by id