查了很多資料發(fā)現(xiàn)網(wǎng)上很多文章都是轉(zhuǎn)發(fā)和抄襲，有些問(wèn)題。這里分享一個(gè)自己項(xiàng)目中使用的行轉(zhuǎn)列例子，供大家參考。代碼如下：文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-832204.html
SELECT
my_id,
nm_cd_map['A'] AS my_cd_a,
nm_cd_map['B'] AS my_cd_b,
nm_cd_map['C'] AS my_cd_c,
nm_num_map['A'] AS my_num_a,
nm_num_map['B'] AS my_num_b,
nm_num_map['C'] AS my_num_c
FROM
(
SELECT
t.my_id,
STR_TO_MAP(my_nm_cds,';',':') AS nm_cd_map,
STR_TO_MAP(my_nm_nums,';',':') AS nm_num_map
FROM
(
SELECT
my_id,
CONCAT_WS(';',COLLECT_LIST(CONCAT(my_nm,':',my_cd))) AS my_nm_cds,
CONCAT_WS(';',COLLECT_LIST(CONCAT(my_nm,':',my_num))) AS my_nm_nums
FROM
(
SELECT '1' AS my_id,'A' AS my_nm,'D01' AS my_cd,19 AS my_num
UNION ALL
SELECT '1' AS my_id,'B' AS my_nm,'D04' AS my_cd,18 AS my_num
UNION ALL
SELECT '1' AS my_id,'C' AS my_nm,'D02' AS my_cd,17 AS my_num
UNION ALL
SELECT '2' AS my_id,'A' AS my_nm,'D03' AS my_cd,16 AS my_num
UNION ALL
SELECT '2' AS my_id,'B' AS my_nm,'D05' AS my_cd,15 AS my_num
UNION ALL
SELECT '2' AS my_id,'C' AS my_nm,'D06' AS my_cd,14 AS my_num
)
GROUP BY my_id
) t
) t
WHERE 1=1;
如果是在SparkSQL或Presto平臺(tái)，或者阿里云的MaxCompute平臺(tái)，還可使用如下方式：文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-832204.html
-- 其實(shí)也可使用CONCAT然后STR_TO_MAP的方式，或者用MAP_FROM_ARRAYS，再或者用數(shù)組排序后ARRAY[n] AS的方式
SELECT
my_id,
nm_cd_map['A'] AS my_cd_a,
nm_cd_map['B'] AS my_cd_b,
nm_cd_map['C'] AS my_cd_c,
nm_num_map['A'] AS my_num_a,
nm_num_map['B'] AS my_num_b,
nm_num_map['C'] AS my_num_c
FROM
(
SELECT
t.my_id,
MAP_FROM_ENTRIES(COLLECT_LIST(nm_cd)) AS nm_cd_map,
MAP_FROM_ENTRIES(COLLECT_LIST(nm_num)) AS nm_num_map
FROM
(
SELECT
my_id,
my_nm,
my_cd,
my_num,
STRUCT(my_nm,my_cd) AS nm_cd,
STRUCT(my_nm,my_num) AS nm_num
FROM
(
SELECT '1' AS my_id,'A' AS my_nm,'D01' AS my_cd,19 AS my_num
UNION ALL
SELECT '1' AS my_id,'B' AS my_nm,'D04' AS my_cd,18 AS my_num
UNION ALL
SELECT '1' AS my_id,'C' AS my_nm,'D02' AS my_cd,17 AS my_num
UNION ALL
SELECT '2' AS my_id,'A' AS my_nm,'D03' AS my_cd,16 AS my_num
UNION ALL
SELECT '2' AS my_id,'B' AS my_nm,'D05' AS my_cd,15 AS my_num
UNION ALL
SELECT '2' AS my_id,'C' AS my_nm,'D06' AS my_cd,14 AS my_num
)
) t
GROUP BY my_id
) t
WHERE 1=1;
到了這里，關(guān)于Hive數(shù)據(jù)倉(cāng)庫(kù)行轉(zhuǎn)列的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
