將多結果組成一張大表並使用迴圈方式，產生多筆結果

USE PA
DECLARE @com_date1 varchar(8)
DECLARE @com_date2 varchar(8)
DECLARE @COP_NO varchar(4)
DECLARE @PROJM_NO varchar(6)
DECLARE @tstStr as varchar(2000)
DECLARE @tstField as varchar(10)
DECLARE @tstCnt as int

set @com_date1 ='20151001'
set @com_date2 ='20151031'
set @COP_NO = 'FH01'
--set @PROJM_NO ='13A003'
select @tstStr = '11A010,11A005,11A006,11A007,11A009,12A005,13A001,13A002,13A003,13A004,13A005,14A006' --將這些code寫到Store Procedure中 ,這可以是AP傳入的參數

while (@tstStr <> '')
begin
 SELECT @tstCnt = PATINDEX('%,%', @tstStr)
  if @tstCnt > 0
    begin
     select @tstField = left(@tstStr,@tstCnt-1)
    -- select @tstField --將資料抓出
     select @tstStr = right(@tstStr,len(@tstStr)-@tstCnt)
    end
  else
    begin
     select @tstField = @tstStr
   --  select @tstField --將資料抓出
     select @tstStr = ''
    end
   
    --select @tstField
    select @PROJM_NO = @tstField
    select @tstField = ''

SELECT  @PROJM_NO 專案代號 ,COUNT(*) as '資料總筆數',
(SELECT COUNT(*) '姓名+EMAIL'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      --and a1.BIRTHDAY is not null
      --and a1.E_MAIL is not null
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO
      and a1.SUBFI_NAME is not null and (a1.E_MAIL is not null and a1.E_MAIL like '%@%')) as 姓名_EMAIL,
(SELECT COUNT(*) '姓名+地址'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      --and a1.BIRTHDAY is not null
      --and a1.E_MAIL is not null
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO
      and a1.SUBFI_NAME is not null and len(a1.COM_ADDR) >= 12) as  姓名_地址,

(SELECT COUNT(*) '姓名+電話'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      --and a1.BIRTHDAY is not null
      --and a1.E_MAIL is not null
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO
      and a1.SUBFI_NAME is not null and LEFT(a1.TEL3,2)='09') as 姓名_電話,
     
(SELECT COUNT(*) '姓名+電話+生日'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      --and a1.BIRTHDAY is not null
      --and a1.E_MAIL is not null
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO
      and a1.SUBFI_NAME is not null and LEFT(a1.TEL3,2)='09' AND a1.BIRTHDAY is not null) as 姓名_電話_生日,
     
(SELECT COUNT(*) '姓名+地址+生日'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      --and a1.BIRTHDAY is not null
      --and a1.E_MAIL is not null
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO
      and a1.SUBFI_NAME is not null and (a1.E_MAIL is not null and a1.E_MAIL like '%@%') -- 姓名+EMAIL
      and a1.SUBFI_NAME is not null and len(a1.COM_ADDR) >= 12 and a1.BIRTHDAY is not null) as 姓名_地址_生日,

(SELECT COUNT(*) '姓名+地址+電話+生日+EMAIL'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      --and a1.BIRTHDAY is not null
      --and a1.E_MAIL is not null
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO
      and a1.SUBFI_NAME is not null and len(a1.COM_ADDR) >= 12 and a1.BIRTHDAY is not null and LEFT(a1.TEL3,2)='09' and (a1.E_MAIL is not null and a1.E_MAIL like '%@%')) 姓名_電話_地址_生日_E_MAIL

FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
      and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
      and a1.com_date between @com_date1 and @com_date2
      and a1.COP_NO=@COP_NO
      and a1.PROJM_NO=@PROJM_NO

end

產生結果：