database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6857 게시물 읽기
No. 6857
MSSQL 쿼리 질문드립니다.
작성자
학생
작성일
2014-10-20 11:15
조회수
7,330

안녕하세요!

DB공부중인 학생인데요~ 궁금한게 있습니다.

select문을 사용하여 조회를 하였는데요

1번과 2번 칼럼은 AA테이블이고 여기에 3번칼럼값을 가지고 있는 BB테이블을 조인시켜주었습니다.

AA테이블의 1,2번 칼럼은 값이 같은데 BB테이블의 3번칼럼값이 달라 아래로 펼쳐집니다.

       1          2         3

(주)가      도매      2013

(주)가      도매      2014

(주)나      제조      2014

이렇게 나오는 것을

 

        1         2         3         4

(주)가       도매    2013    2014

(주)나       제조   2014

 

이런식으로 바꾸고 싶습니다.

조언 좀 부탁드립니다~!!

이 글에 대한 댓글이 총 7건 있습니다.

AA 에는 1,2 컬럼이 있고

BB 에는 어떤 컬럼이 있는건지요?

AA 테이블과 BB 테이블을 Join 할때

어떤 조건으로 조인을 하셨는지?

대략적인 테이블 레이아웃이나

정보가 있어야 다른 분들도 댓글달기 수월할거 같아요.

서울에서 김서방 찾는거 같다는 느낌이 들어요..

글만보고서는..;;

최한영(terry0515)님이 2014-10-20 12:15에 작성한 댓글입니다.

 아~

AA테이블은 1,2 즉 회사와, 업종 컬럼이 있구요

BB테이블에는 3의컬

럼 즉 회사 거래날짜가 들어있습니다.

테이블 조인시에는 AA테이블에 left join을 하여 BB테이블을 걸어주었구요

 

select AA.1 , AA.2 , BB.3

from AA as AA

left join BB as BB

이렇게 걸어주었습니다.

실행시켜보니 위에 

  1          2         3

(주)가      도매      2013

(주)가      도매      2014

(주)나     제조       2014

이런식으로 출력이 되고있습니다.

제가 출력 하고 싶은것은

    1         2         3         3

(주)가       도매    2013    2014

(주)나       제조    2014

이런식으로  출력이 되게 하고싶습니다.

즉, AA테이블의 1,2컬럼의 값이 같고 BB테이블의 3컬럼의 값이 다르다면

같은 1,2컬럼의 값은 하나만 출력되고 그 옆으로 3컬럼의 값이 출력되게 하고싶습니다!

김병수(kbbjsy)님이 2014-10-20 13:25에 작성한 댓글입니다.

 아 join 할 때 조건은 BB테이블에 적어놓진 않았는데 1컬럼과 같은 값을 가지고 있는 컬럼으로 조인시켜주었습니다.

left join BB on AA.1 = BB.1

김병수(kbbjsy)님이 2014-10-20 15:56에 작성한 댓글입니다.
WITH 조인된결과( [a], [b], [c] )
AS
(
SELECT '(주)가', '도매', '2013'
UNION ALL SELECT '(주)가', '도매', '2014'
UNION ALL SELECT '(주)나', '제조', '2014'
)
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER ( PARTITION BY [a], [b] ORDER BY [c] ) AS rn, *
FROM 조인된결과
) AS aa
PIVOT ( MAX([c]) FOR rn IN ([1],[2]) )AS pvt
;
우욱님이 2014-10-20 17:25에 작성한 댓글입니다. Edit

Drop Table #AA
Create Table #AA
(
 AA varchar(10)
   ,BB varchar(10)
)

DROP Table #AA_num
Create Table #AA_num
(
 AA varchar(10)
   ,seq decimal(18,0)
)

Drop Table #BB
Create Table #BB
(
 AA varchar(10)
   ,CC varchar(10)
)

Drop Table #BB_seq
Create Table #BB_seq
(
 AA varchar(10)
   ,CC varchar(4)
   ,seq decimal(18,0)
)

Drop Table #CC
Create Table #CC
(
    AA     varchar(10)
   ,year01 varchar(4)
   ,year02 varchar(4)
   ,year03 varchar(4)
   ,year04 varchar(4)
   ,year05 varchar(4)
   ,year06 varchar(4)
   ,year07 varchar(4)
   ,year08 varchar(4)
   ,year09 varchar(4)
   ,year10 varchar(4)
)

DROP TABLE #CC_seq
Create Table #CC_seq
(
    AA     varchar(10)
   ,CC     varchar(4)
   ,seq    Decimal(18,0)
)

Declare @ls_sql VARCHAR(MAX)

insert #AA
values
 ('(주)가','도매')
,('(주)나','소매')

insert #BB
values
 ('(주)가','2013')
,('(주)가','2014')
,('(주)나','2010')
,('(주)나','2011')
,('(주)나','2012')
,('(주)나','2013')
,('(주)나','2014')

Declare @ld_num Decimal(18,0)
Declare @ld_num2 Decimal(18,0)
Declare @ld_num3 Decimal(18,0)

 Select @ld_num = MAX(a.cnt)
   From
       (
     Select COUNT(CC) As cnt
     ,AA        As AA
    From #BB As BB
   Group By BB.AA
       ) As a
      
    Insert
      Into #AA_num
    Select
           AA
          ,ROW_NUMBER() over (order by AA asc) as num
      From #AA
  Group By AA
  Order By AA asc
 
--   Select * From #AA_num

       Select @ld_num2 = COUNT(AA)
         From #AA_num
        
--       Select @ld_num2
      
       Set @ld_num = 0                                                                                                                                                                                                                                                                                                                                                                  
       While @ld_num < @ld_num2
          Begin                                                                                                                                                                                                                                                                                                                                                                         
             Set @ld_num = @ld_num + 1                                                                                                                                                                                                                                                                                                                                                  
            
--             Select @ld_num
            
               Insert                                                                                                                                                                                                                                                                                                                                               
                 Into                                                                                                                                                                                                                                                                                                                                               
                      #CC_seq
               Select                                                                                                                                                                                                                                                                                                                                               
                      b.AA
                     ,b.CC
                     ,ROW_NUMBER() over (order by b.CC asc) as num
                 From #AA_num a
                                Inner Join
                                           #BB b
                                                 On a.AA = b.AA                                   
                Where a.seq = @ld_num
          End                                                                                                                                                                                                                                                                                                                                                                           
      
       Set @ls_sql = '             Insert                                                                                                                                                                                                                                                                                                                                               
                                     Into                                                                                                                                                                                                                                                                                                                                               
                                          #CC
                                   Select                                                                                                                                                                                                                                                                                                                                               
                                          AA
                     '                                                                                                                                                                                                                                                                                                                                                                  
       Set @ld_num = 0                                                                                                                                                                                                                                                                                                                                                                  
       Set @ld_num2 = 0                                                                                                                                                                                                                                                                                                                                                                 
       While @ld_num < 10                                                                                                                                                                                                                                                                                                                                                              
          Begin                                                                                                                                                                                                                                                                                                                                                                         
             Set @ld_num = @ld_num + 1                                                                                                                                                                                                                                                                                                                                                  
             Set @ls_sql = @ls_sql + '   ,year' + Convert(char(2),@ld_num) + ' = ( Case When seq = ' + Convert(char(2),@ld_num) + ' Then Convert(decimal(18,0),CC)  End ) '                                                                                                                                                                                            
          End                                                                                                                                                                                                                                                                                                                                                                           
       Set @ls_sql = @ls_sql + '    From #CC_seq '                                                                                                                                                                                                                                                                                                                                 
       Set @ls_sql = @ls_sql + ' Group By AA '                                                                                                                                                                                                                                                                                                                                   
       Set @ls_sql = @ls_sql + '         ,CC '                                                                                                                                                                                                                                                                                                                                   
       Set @ls_sql = @ls_sql + '         ,seq '                                                                                                                                                                                                                                                                                                                                   
       Set @ls_sql = @ls_sql + ' Order By AA ASC'                                                                                                                                                                                                                                                                                                                                   
       Set @ls_sql = @ls_sql + '         ,CC ASC'                                                                                                                                                                                                                                                                                                                                   
       Set @ls_sql = @ls_sql + '         ,seq ASC'                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                        
       Exec (@ls_sql)                   

  Select
         AA
        ,Max(year01)
        ,Max(year02)
        ,Max(year03)
        ,Max(year04)
        ,Max(year05)
        ,Max(year06)
        ,Max(year07)
        ,Max(year08)
        ,Max(year09)
        ,Max(year10)
    From #CC 
 Group By AA   

최한영(terry0515)님이 2014-10-20 18:06에 작성한 댓글입니다.

헉..댓글달고 우욱님꺼 보니..

SQL 공부 많이 해야겠다는 생각이 드네요 ㅜ.ㅜ

저렇게 짧은 구문을..

이렇게 길게 늘어뜨리는 것도

재능이겠죠?

-_-;;;

 

최한영(terry0515)님이 2014-10-20 18:08에 작성한 댓글입니다.

 답글 전부 감사드립니다..!!

 

알려주신 것들을 토대로 이것저것 다 해보니 해결 되었습니다!

감사드립니다~!!

김병수(kbbjsy)님이 2014-10-21 13:17에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
6860mssql 2008 설치문의드립니다. [3]
박세민
2014-10-21
6214
6859NULL 인식문제.. [2]
우암
2014-10-20
5890
6858디비 쿼리 질문드립니다.! [4]
이성주
2014-10-20
6270
6857MSSQL 쿼리 질문드립니다. [7]
학생
2014-10-20
7330
6856쿼리나 프로시저 작성 부탁합니다. [3]
정인수
2014-10-15
6106
6855mssql 에서 select 시 레코드 결과 수 지정 [2]
송영미
2014-10-14
5828
6853두개의 테이블을 조회후 조건쿼리 [3]
김우성
2014-09-29
6158
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다