[sql] STRING_SPLIT 함수, 구분자로 자르기, 행열 변환

 

STRING_SPLIT 함수, 구분자로 자르기

 

select top 100 *
from
(
select purchase_id
,product_ids
from purchase_log
) as x
cross apply string_split (product_ids, ',') as y

--- 쉼표로 묶어진 컬럼을 하나로 value 형식으로 따내는 법



--- 결과값



purchase_id product_ids value
100001 A001,A002,A003 A001
100001 A001,A002,A003 A002
100001 A001,A002,A003 A003
100002 D001,D002 D001
100002 D001,D002 D002
100003 A001 A001

 

행열 변환하는 방법

 

/* 행 열 변환
SELECT *
FROM ( 피벗할 쿼리문 ) AS result
PIVOT ( 그룹합수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값] ... ) AS pivot_result

*/


DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
job nvarchar(MAX)
, deptno bigint
, sal_sum bigint
)

INSERT INTO emp
VALUES
('CLERK',10,1300)
,('MANAGER',10,2450)
,('PRESIDENT',10,5000)
,('ANALYST',20,6000)
,('CLERK',20,1900)
,('MANAGER',20,2975)
,('CLERK',30,950)
,('MANAGER',30,2850)
,('SALESMAN',30,5600)

;

*/

select *
from
(
select job
,deptno
,sal_sum
from emp
) as result
pivot(
sum(sal_sum) FOR deptno in ([10], [20], [30], [40])
) as pivot_result
order by
job

------ 결과값

job 10 20 30 40
ANALYST NULL 6000 NULL NULL
CLERK 1300 1900 950 NULL
MANAGER 2450 2975 2850 NULL
PRESIDENT 5000 NULL NULL NULL
SALESMAN NULL NULL 5600 NULL

 

 

행열 변환하는 방법

 

/* 행열 변환 */

DROP TABLE IF EXISTS quarterly_sales;
CREATE TABLE quarterly_sales (
year integer
, q1 integer
, q2 integer
, q3 integer
, q4 integer
);

INSERT INTO quarterly_sales
VALUES
(2015, 82000, 83000, 78000, 83000)
, (2016, 85000, 85000, 80000, 81000)
, (2017, 92000, 81000, NULL , NULL )
;

select q.year
,case
when p.idx = 1 then 'q1'
when p.idx = 2 then 'q2'
when p.idx = 3 then 'q3'
when p.idx = 4 then 'q4'
end as quarter
,case
when p.idx = 1 then q.q1
when p.idx = 2 then q.q2
when p.idx = 3 then q.q3
when p.idx = 4 then q.q4
end as sales
from quarterly_sales as q
cross join
(
select 1 as idx
union all select 2 as idx
union all select 3 as idx
union all select 4 as idx
) as p