테이블 데이티가
sku openmarket sprice nprice openmarketCode
100000 A 438900 176400 167346205
100000 B 438900 176400 13398320
100000 C 418000 168000 1042606820
100000 D 438900 176400 A599497495
100000 E 438900 176400 100000
102847 A 435700 261400 102847
102847 B 435700 261400 677509578
102847 C 439900 263900 36279541
102847 D 415000 249000 1597216478
102847 E 435700 261400 A709883629
이렇게 들어가 있습니다.
이 데이타를 쿼리해서
sku A-code B-code C-code E-code
100000 167346205 13398320 1042606820 A599497495
102847 102847 677509578 36279541 1597216478
이렇게 한번에 뽑고 싶은데 가능할까요?
Comment 4
-
항해자™
2013.03.13 10:08
게시판에서 pivot 으로 검색하면 다양한 자료를 만나 보실 수 있습니다,,, -
sigmak
2013.03.13 13:34
-- 이렇게 하면 맞는건지 모르겠지만 결과는 대충 그렇게 나오네요..
select sku, max(Acode) as 'A-code', max(Bcode) as 'B-code', max(Ccode) as 'C-code',max(Dcode) as 'D-code' , max(Ecode) as 'E-code'
from (
select sku, openmarketCode as Acode, '' as Bcode, '' as Ccode, '' as Dcode, '' as Ecode from zdemo2
where openmarket='A'
union all
select sku, '' as Acode, openmarketCode as Bcode, '' as Ccode, '' as Dcode, '' as Ecode from zdemo2
where openmarket='B'
union all
select sku, '' as Acode, '' as Bcode, openmarketCode as Ccode, '' as Dcode, '' as Ecode from zdemo2
where openmarket='C'
union all
select sku, '' as Acode, '' as Bcode, '' as Ccode, openmarketCode as Dcode, '' as Ecode from zdemo2
where openmarket='D'
union all
select sku, '' as Acode, '' as Bcode, '' as Ccode, '' as Dcode, openmarketCode as Ecode from zdemo2
where openmarket='E'
)x
group by x.sku-------------------------------------------------------------
sku A-code B-code C-code D-code E-code
100000 167346205 13398320 1042606820 A599497495 100000
102847 102847 677509578 36279541 1597216478 A709883629 -
카즈야마(이정우)
2013.03.13 14:11
안녕하세요.
관련해서 sigmak 님말씀대로 각 항목별로 테이블을 읽어서 처리하는 방법도 있겠지만
가급적 테이블은 한번 읽은 상태에서 데이터 표현해주는것이 좋겠다 생각하여
해당 링크 글 보시면 CASE 문을 이용하여 pivot 하는 경우도 있습니다.
감사합니다.
-
한태
2016.02.26 18:47
create table skuu(sku int,openmarket varchar(1),sprice int,nprice int,openmarketcode varchar(15),constraint [skuu_omcode] primary key(openmarketcode))insert into skuu values(100000,'A',438900,176400,'167346205'),(100000,'B',438900,176400,'13398320'),(100000,'C',418000,168000,'1042606820'),(100000,'D',438900,176400,'A599497495'),(100000,'E',438900,176400,'100000'),(102847,'A',435700,261400,'102847'),(102847,'B',435700,261400,'677509578'),(102847,'C',439900,263900,'36279541'),(102847,'D',415000,249000,'1597216478'),(102847,'E',435700,261400,'A709883629')select sku,MAX(case openmarket when 'A' then openmarketcode end) as "A-code",MAX(case openmarket when 'B' then openmarketcode end) as "B-code",MAX(case openmarket when 'C' then openmarketcode end) as "C-code",MAX(case openmarket when 'D' then openmarketcode end) as "D-code",MAX(case openmarket when 'E' then openmarketcode end) as "E-code"from skuugroup by skupivot으로 해보려다 openmarketcode 컬럼이 문자값이어서 case문으로 구성해보았습니다.
