MSSQL 기존 조회쿼리를 수정할려고 합니다. 기존조회결과+추가테이블컬럼값(세로열을 가로열로 변형) 0 2 1,587

by RedOri [SQL Query] MS-SQL MSSQL SQLServer [2018.01.16 18:05:30]


SELECT *
FROM(
SELECT 
		A.req_id, 
		A.chart_id,  
		(
SELECT minor_name
FROM SYS_INTERNAL_CODE
WHERE major_code = 5 AND minor_code = (
SELECT cust_class
FROM OCS_CUSTOMER
WHERE chart_id = A.chart_id)) AS cust_class, 
		A.shop_code, 
		A.doctor_id, 
		(
SELECT name
FROM BA_EMPLOYEE_MASTER
WHERE uId = A.doctor_id) AS doctor_name, 
		(
SELECT name
FROM BA_EMPLOYEE_MASTER
WHERE uId = (
SELECT codi_id
FROM OCS_CUSTOMER
WHERE chart_id = A.chart_id
			)
		) AS codi_name, 
		(
SELECT NAME
FROM BA_EMPLOYEE_MASTER
WHERE UID = A.REG_PERSON) AS manager_name, 
		(
SELECT rsv_diagnosis_name AS diagnosis_name
FROM BA_RESERVE_TREAT_CLASS
WHERE rsv_diagnosis_code = A.diagnosis_code) AS col1, 
		(
SELECT rsv_procedure_name AS procedure_name
FROM BA_RESERVE_TREAT
WHERE rsv_diagnosis_code = A.diagnosis_code AND rsv_procedure_code = A.procedure_code) AS procedure_name, 
		(
SELECT hair_class_name
FROM BA_HAIR_CLASS
WHERE hair_class_code = A.hair_class_code) AS col3,
		A.req_time, 
		A.treat_end_time, 
		A.cure_end_time, 
		A.care_end_time, 
		A.state, 
		B.income_id,
		B.treat_id, 
		C.visit_time, 
		B.amount, 
		B.treat_amount, CASE WHEN A.state = 3 THEN 1 WHEN A.state = 7 THEN 2 WHEN A.state = 10 THEN 3 WHEN A.state = 4 THEN 4 WHEN A.state = 8 THEN 5 WHEN A.state = 11 THEN 6 WHEN A.state = 99 THEN 8 ELSE 7 END AS sort
FROM OCS_RECEIPTION A
LEFT OUTER
JOIN (
	SELECT 
			A.income_id,
			B.req_id, 
			B.treat_id, 
			A.chart_id, ISNULL(A.cash_amount, 0) + ISNULL(A.card_amount, 0) + ISNULL(A.account_amount, 0) AS amount, 
			B.treat_amount
	FROM OCS_INCOME A
		RIGHT OUTER
			JOIN (
				SELECT A.req_id, A.treat_id, ISNULL(SUM(opr_other_price), 0) + ISNULL(SUM(prs_other_price), 0) + ISNULL(SUM(cos_other_price), 0) + ISNULL(SUM(amount), 0) AS treat_amount
					FROM OCS_TREAT A
						INNER JOIN (
							SELECT treat_id, SUM(amount) AS amount
								FROM (
									SELECT treat_id, 0 AS amount
										FROM OCS_TREAT_COUNSEL
										GROUP BY treat_id UNION ALL
									SELECT treat_id, SUM(ISNULL(amount, 0) - (ISNULL(amount, 0) * ISNULL(discount_rate, 0) * 0.01)) AS amount
										FROM OCS_TREAT_OPR
										GROUP BY treat_id UNION ALL
									SELECT treat_id, SUM(ISNULL(amount, 0) - (ISNULL(amount, 0) * ISNULL(discount_rate, 0) * 0.01)) AS amount
										FROM OCS_TREAT_PRS
										GROUP BY treat_id UNION ALL
									SELECT treat_id, SUM(ISNULL(amount, 0) - (ISNULL(amount, 0) * ISNULL(discount_rate, 0) * 0.01) + ISNULL(discount_price, 0)) AS amount
										FROM OCS_TREAT_COS
										GROUP BY treat_id
									) A
								GROUP BY 	treat_id
							) B ON A.treat_id = B.treat_id
					WHERE treat_date = '2018-01-16' AND shop_code = 'HOO'
				GROUP BY A.treat_id, A.req_id
			) B ON A.treat_id = B.treat_id	
		) B ON A.req_id = B.req_id 
	LEFT OUTER
		JOIN (
			SELECT rsv_id, visit_time
				FROM OCS_RESERVE
					WHERE reserve_date = '2018-01-16' AND rsv_shop = 'HOO'
				) C ON A.rsv_id = C.rsv_id
			WHERE A.req_date = '2018-01-16' AND A.shop_code = 'HOO'
	) AA
ORDER BY sort, AA.req_id DESC

이와 같은 쿼리조회결과의 treat_id값으로 BBBB라는 TABLE의 treat_id값과 매핑하여 file이라는 컬럼네임을 stuff,pivot기능을 이용하여 가로열로 출력하려고 하는데

어떻게 해야될까요... 도움부탁드립니다~

원하는 출력구조

컬럼명 aaaa || bbbb || ccccc || file

결과값 bbbb|| ccccc || dddd || 01,02,03,04

BBBB 테이블구조

컬럼명 treat_id || file

데이터 122334 || 01

데이터 122334 || 02

데이터 122334 || 03

데이터 122334 || 04

데이터 521322 || 05

by 마농 [2018.01.16 18:51:03]
SELECT a.*
  FROM (SELECT a.req_id
               -- 중략 --
             , STUFF((SELECT ',' + file
                        FROM bbbb
                       WHERE treat_id = b.treat_id
                       ORDER BY file
                         FOR XML PATH('')
                      ), 1, 1, '') AS file
          FROM -- 중략 --
        ) AA
 ORDER BY sort, AA.req_id DESC
;
-- http://www.gurubee.net/article/55512

 


by RedOri [2018.01.17 09:39:03]

마농님 감사합니다~T-T

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