包含动态列的SQL数据透视- result

Question

我有两个表:

表1)JOB_129_FEED_ITEMS

SHORT_TITLE麦格雷戈的鹿皮软鞋Bose音箱

我们可以尝试在ROW_NUMBER():

的帮助下使用pivot查询。

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;

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');

在pivot的子查询中先使用Join、Row_number和concat，再使用Pivot，如下所示:

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