# 交叉分析表該怎麼作??

• ### 問題

• hi

how to get the table??

column1       column2                column3               column4

a                 b                                        cc                               dd

aa               bb                                        c                             d

a23               32b                             c23                         d 67

my  SELECT  CODE:

select (select distinct TYPE  from TUMB_MAIN where TYPE='D'),(select count(FRAME) from TUMB_MAIN where FRAME='2964C' AND  TYPE='D') ,(select  count(holder)  from  TUMB_MAIN  where  FRAME='2964C' AND  TYPE='D') ,(select sum (BUILD_YEAR) from TUMB_MAIN where FRAME='2964C' AND  TYPE='D')
UNION
select (select distinct TYPE  from TUMB_MAIN where TYPE='H'),(select count(FRAME) from TUMB_MAIN where FRAME='2964C' AND  TYPE='H') ,(select  count(holder)  from  TUMB_MAIN  where  FRAME='2964C' AND  TYPE='H') ,(select sum (BUILD_YEAR) from TUMB_MAIN where FRAME='2964C' AND  TYPE='H')

UNION
select (select distinct TYPE  from TUMB_MAIN where TYPE='G'),(select count(FRAME) from TUMB_MAIN where FRAME='2964C' AND TYPE='G') ,(select  count(holder)  from  TUMB_MAIN  where  FRAME='2964C' AND TYPE='G') ,(select sum (BUILD_YEAR) from TUMB_MAIN where FRAME='2964C' AND TYPE='G')

i got

D                     1                              1                            77

G                      0                               0                         NULL

H                      2                                2                       160

if add follows (z not in type)

UNION
select (select distinct TYPE  from TUMB_MAIN where TYPE='z'),(select count(FRAME) from TUMB_MAIN where FRAME='2964C' AND TYPE='z') ,(select  count(holder)  from  TUMB_MAIN  where  FRAME='2964C' AND TYPE='z') ,(select sum (BUILD_YEAR) from TUMB_MAIN where FRAME='2964C' AND TYPE='z')

the line added at first row:    NULL 0 0 NULL

why??

2006年10月14日 上午 07:48

### 所有回覆

• try pivot statement in SQL 2005
2006年11月1日 下午 02:16