How to Display Vertical result to Horizontal-CodePudding

Does anyone know here how to display vertical result to horizontal?

Current query result:

ITEM	DESCRIPTION	ALTERNATE
489	TJ CLASSIC PE 1KG 630 234	635
489	TJ CLASSIC PE 1KG 630 234	859
490	CAKE FLOUR 250G	251
490	CAKE FLOUR 250G	278
490	CAKE FLOUR 250G	703
490	CAKE FLOUR 250G	925

Expected result

ITEM	DESCRIPTION	ALTERNATE	ALTERNATE1	ALTERNATE2	ALTERNATE3
489	TJ CLASSIC PE 1KG 630 234	635	859
490	CAKE FLOUR 250G	251	278	703	925

Vertical result for each item, some has more than 20 result.

CodePudding user response：

We can use ROW_NUMBER here along with pivoting logic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ITEM ORDER BY ALTERNATE) rn
    FROM yourTable
)

SELECT
    ITEM,
    DESCRIPTION,
    MAX(CASE WHEN rn = 1 THEN ALTERNATE END) AS ALTERNATE,
    MAX(CASE WHEN rn = 2 THEN ALTERNATE END) AS ALTERNATE1,
    MAX(CASE WHEN rn = 3 THEN ALTERNATE END) AS ALTERNATE2,
    MAX(CASE WHEN rn = 4 THEN ALTERNATE END) AS ALTERNATE3
FROM cte
GROUP BY
    ITEM,
    DESCRIPTION;