SQL pivot with dynamic columns - result-CodePudding

I have 2 tables:

Table 1) JOB_129_FEED_ITEMS

id	SHORT_TITLE
1	McGregor Moccasins
2	Bose speaker

Table 2) JOB_129_FEED_ITEMS_CLASSICTAX

id	items_id	CLASSICTAX
1	1	FASHION
2	1	CASUAL FOOTWEAR
3	1	FOOTWEAR OTHER
4	1	FOOTWEAR OTHER MEN
5	2	BROWN GOODS
6	2	HIFI
7	2	HIFI LOUDSPEAKERS
8	2	HIFI LOUDSPEAKER

They can be joined on JOB_129_FEED_ITEMS.ID = JOB_129_FEED_ITEMS_CLASSICTAX.ITEMS_ID

And i am looking for a result like this

id	short_title	CLASSICTAX1	CLASSICTAX2	CLASSICTAX3	CLASSICTAX4
1	McGregor Homie Moccasins	FASHION CASUAL	FOOTWEAR	FOOTWEAR OTHER	FOOTWEAR OTHER MEN
2	Auna Simpfy Speaker	BROWN GOODS	HIFI	HIFI LOUDSPEAKERS	HIFI LOUDSPEAKER

How to do this? SQL Pivot? thanks

CodePudding user response：

We can try using a pivot query with the help of ROW_NUMBER():

WITH cte AS (
    SELECT i.id, i.SHORT_TITLE, c.CLASSICTAX,
           ROW_NUMBER() OVER (PARTITION BY i.id ORDER BY c.CLASSICTAX) rn
    FROM JOB_129_FEED_ITEMS i
    INNER JOIN JOB_129_FEED_ITEMS_CLASSICTAX c
        ON c.items_id = i.id
)

SELECT
    id,
    SHORT_TITLE,
    MAX(CASE WHEN rn = 1 THEN CLASSICTAX END) AS CLASSICTAX1,
    MAX(CASE WHEN rn = 2 THEN CLASSICTAX END) AS CLASSICTAX2,
    MAX(CASE WHEN rn = 3 THEN CLASSICTAX END) AS CLASSICTAX3,
    MAX(CASE WHEN rn = 4 THEN CLASSICTAX END) AS CLASSICTAX4
FROM cte
GROUP BY
    id,
    SHORT_TITLE
ORDER BY
    id;

CodePudding user response：

data

CREATE TABLE  Job129FeedItem(
   id          INTEGER  NOT NULL 
  ,SHORT_TITLE VARCHAR(100) NOT NULL
);
INSERT INTO  Job129FeedItem
(id,SHORT_TITLE) VALUES 
(1,'McGregor Moccasins'),
(2,'Bose speaker');


CREATE TABLE Job129FeedItemsCLASSICTAX(
   id         INTEGER  NOT NULL
  ,items_id   INTEGER  NOT NULL
  ,CLASSICTAX VARCHAR(100) NOT NULL
);
INSERT INTO Job129FeedItemsCLASSICTAX
(id,items_id,CLASSICTAX) VALUES 
(1,1,'FASHION'),
(2,1,'CASUAL FOOTWEAR'),
(3,1,'FOOTWEAR OTHER'),
(4,1,'FOOTWEAR OTHER MEN'),
(5,2,'BROWN GOODS'),
(6,2,'HIFI'),
(7,2,'HIFI LOUDSPEAKERS'),
(8,2,'HIFI LOUDSPEAKER');

use Join,Row_number and concat in Subqurey of pivot and then Pivot as follows:

select *
from   (select j1.id,
               SHORT_TITLE,
               concat('CLASSICTAX', row_number()
                                      over(
                                        partition by items_id
                                        order by  CLASSICTAX asc))) PivotC,
               CLASSICTAX
        from   Job129FeedItem j1
               join Job129FeedItemsCLASSICTAX j2
                 on j1.id = j2.items_id) src
       pivot ( Max(CLASSICTAX)
             for PivotC in ([CLASSICTAX1],
                            [CLASSICTAX2],
                            [CLASSICTAX3],
                            [CLASSICTAX4]) ) piv
order  by id asc

dbfiddle