【SQL】sql 多行变一行，一行变多行，行转列，列转行，lateral view explode split，collect_set/collect_list

一行变多行，行转列

一行原数据：

orderid   clientname
123		  linda,lily,bob
345       apple,july,cici

一行拆成多行：

-- LATERAL VIEW explode 要放置于from之后，where之前

select distinct orderid,clientname
            from table
            LATERAL VIEW explode (split(clientname,',')) t -- t为新表别名
            where d ='${zdt.format("yyyy-MM-dd")}' 

拆成后：

orderid   clientname
123		  linda
123       lily
123       bob
345       apple
345       july
345       cici

再变回一行：

-- collect_set() 去重；collect_list() 不去重

select orderid,collect_set(clientname) clitentname
from t
group by orderid

聚合后：

orderid   clientname
123       ["linda","lily","bob"]
345       ["apple","july","cici"]

注意：此时多行聚合成一行后，clientname只是数组，要变成原始版本string格式，使用concat_ws()增加逗号

select orderid,concat_ws(',',collect_set(clientname)) clitentname
from t
group by orderid

最终还原为一行原数据

orderid   clientname
123		  linda,lily,bob
345       apple,july,cici