데이터베이스 질문과 답변

세로 데이터를 가로로 출력 0 4 3,173

by 밍밍∨ [SQLServer] [2013.12.03 10:30:01]

1.jpg (90,357Bytes)
2.jpg (36,727Bytes)
(글이 자꾸 없어지고 파일만 등록되네요 ㅠㅠ)

가로 출력은 매번 할때마다 헷깔리네요..
mssql 입니다.

세로로 뿌려진 데이터들을 가로로 출력하려 합니다.
row_number를 이용해도 code K가 color 가 없어서어 그런지
생각대로 잘 안되네요;;
by 강정식 [2013.12.03 11:04:41]
하이 민선씨. 아래 url 참고해 보세요

http://ukja.tistory.com/168

color 값이 많거나 가변적이라면 pl/sql로 처리해야 되여
고정이거나 값을 다 알면 one-sql로 가능하구여
by 우리집아찌 [2013.12.03 11:21:43]

MS-SQL 이면 임시테이블 쓰는 방법도 있지요..
by 우리집아찌 [2013.12.03 14:08:17] 
 
-- 답에만 맞췄어요..
-- K값이 왜 저래야하는지 잘모르겟네요..
WITH T AS (
SELECT 'SELF' TYPE , 'D' CODE , '' SUB_CODE , 'EXOTIC' COLOR , 1 RN , 249 QTY UNION ALL
SELECT 'SELF' TYPE , 'D' CODE , '' SUB_CODE , 'GREEN' COLOR , 2 RN , 292 QTY UNION ALL
SELECT 'SELF' TYPE , 'D' CODE , '' SUB_CODE , 'PINK' COLOR , 3 RN , 335 QTY UNION ALL
SELECT 'SELF' TYPE , 'D' CODE , '' SUB_CODE , 'PLUM' COLOR , 4 RN , 224 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '2' SUB_CODE , 'EXOTIC' COLOR , 1 RN , 239 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '2' SUB_CODE , 'GREEN' COLOR , 2 RN , 280 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '2' SUB_CODE , 'PINK' COLOR , 3 RN , 322 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '2' SUB_CODE , 'PLUM' COLOR , 4 RN , 215 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '3' SUB_CODE , 'EXOTIC' COLOR , 1 RN , 0 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '3' SUB_CODE , 'GREEN' COLOR , 2 RN , 0 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '3' SUB_CODE , 'PINK' COLOR , 3 RN , 0 QTY UNION ALL
SELECT 'SELF' TYPE , 'F' CODE , '3' SUB_CODE , 'PLUM' COLOR , 4 RN , 0 QTY UNION ALL
SELECT 'SELF' TYPE , 'K' CODE , '' SUB_CODE , '' COLOR , 1 RN , 1170 QTY UNION ALL
SELECT 'TRIM' TYPE , 'D' CODE , '' SUB_CODE , 'BLE' COLOR , 1 RN , 242 QTY UNION ALL
SELECT 'TRIM' TYPE , 'F' CODE , '2' SUB_CODE , 'BLE' COLOR , 1 RN , 240 QTY UNION ALL
SELECT 'TRIM' TYPE , 'K' CODE , '' SUB_CODE , '' COLOR , 1 RN , 257 QTY )

SELECT TYPE , COLOR , RN ,
	  (SELECT MAX(QTY) FROM T WHERE TYPE = a.TYPE ) K,
	  SUM(CASE WHEN CODE = 'D' THEN QTY END) D,
	  SUM(CASE WHEN CODE = 'F' AND SUB_CODE = '' THEN QTY END) F1,
	  SUM(CASE WHEN CODE = 'F' AND SUB_CODE = '2' THEN QTY END) F2,
	  SUM(CASE WHEN CODE = 'F' AND SUB_CODE = '3' THEN QTY END) F3 
FROM T a
WHERE COLOR != ''
GROUP BY TYPE ,COLOR , RN
ORDER BY TYPE , COLOR
by 밍밍∨ [2013.12.03 16:21:11]

WITH TAB AS
(
    SELECT 'SELF' TYPE1, 'DYE' CODE, 0 SUBCODE, 'EXOTIC ROYAL NEON' COLOR, 249 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'DYE' CODE, 0 SUBCODE, 'GREEN NEON' COLOR, 292 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'DYE' CODE, 0 SUBCODE, 'KNOCKOUT PINK' COLOR, 335 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'DYE' CODE, 0 SUBCODE, 'PLUM BURST' COLOR, 224 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 2 SUBCODE, 'EXOTIC ROYAL NEON' COLOR, 239 QTY UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 2 SUBCODE, 'GREEN NEON' COLOR, 280 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 2 SUBCODE, 'KNOCKOUT PINK' COLOR, 322 QTY UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 2 SUBCODE, 'PLUM BURST' COLOR, 215 QTY UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 3 SUBCODE, 'EXOTIC ROYAL NEON' COLOR, 0 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 3 SUBCODE, 'GREEN NEON' COLOR, 0 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 3 SUBCODE, 'KNOCKOUT PINK' COLOR, 0 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'FABSENDOUT' CODE, 3 SUBCODE, 'PLUM BURST' COLOR, 0 QTY  UNION ALL
    SELECT 'SELF' TYPE1, 'KNITTING' CODE, 0 SUBCODE, '' COLOR, 1170 QTY  UNION ALL
    SELECT 'CONTRAST' TYPE1, 'DYE' CODE, 0 SUBCODE, 'BLEACH' COLOR, 242 QTY UNION ALL
    SELECT 'CONTRAST' TYPE1, 'FABSENDOUT' CODE, 2 SUBCODE, 'BLEACH' COLOR, 240 QTY  UNION ALL
    SELECT 'CONTRAST' TYPE1, 'KNITTING' CODE, 0 SUBCODE, '' COLOR, 257 QTY
)

--select  CODE+SUBCODE from TAB


SELECT s.*
  FROM (
    SELECT TYPE1
, COLOR
, ROW_NUMBER() OVER(PARTITION BY TYPE1 ORDER BY COLOR) RN
, CASE WHEN KNITTING IS NULL THEN SUM(KNITTING) OVER(PARTITION BY TYPE1) ELSE KNITTING END KNITTING
, DYE
, FABSENDOUT_1
, FABSENDOUT_2
, FABSENDOUT_3
  FROM (
    SELECT TYPE1
, COLOR
, SUM(CASE CODE WHEN 'KNITTING' THEN QTY END) AS KNITTING
, SUM(CASE CODE WHEN 'DYE' THEN QTY END) AS DYE
, SUM(CASE CODE+cast(SUBCODE as varchar) WHEN 'FABSENDOUT1' THEN QTY END) AS FABSENDOUT_1
, SUM(CASE CODE+cast(SUBCODE as varchar) WHEN 'FABSENDOUT2' THEN QTY END) AS FABSENDOUT_2
, SUM(CASE CODE+cast(SUBCODE as varchar) WHEN 'FABSENDOUT3' THEN QTY END) AS FABSENDOUT_3
  FROM TAB
GROUP BY TYPE1
    , COLOR
   ) a
   ) s
 WHERE COLOR <> ''

저도 비슷하게 하긴 했어요 ㅋㅋ
감사합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입