select * FROM( SELECT [date],[CCID],[CCName],[CenterCode],[1st Show],'9' as [Deposit #] FROM
(SELECT vw_Activity.[date],vw_Activity.[CCID],vw_Activity.[CCName],
vw_Activity.CenterCode,vw_Activity.[Action] FROM vw_Activity
WHERE [Date]>='2010-07-01' AND [Date]<='2010-07-31' AND CCID='100007') as A
PIVOT (COUNT ([Action]) FOR [Action] in ([1st Show])) as T) as firstshow
union all
select * FROM( SELECT [PayDate] as [Date],'NULL' as [CCID],'NULL' as [CCName],'NULL' as [CenterCode],'NULL' as [1st Show],[900000] as [Deposit #] FROM
(SELECT [paydate],[CreateBy],[CCID] FROM Deposit
WHERE [PayDate]>='2010-11-01' AND [PayDate]<='2010-11-30' AND CCID='900000' ) as A
PIVOT
(COUNT ([CCID]) FOR [CCID] in ([900000])) as T) as secondshowfirstshow表内容如下:date CCID CCName CenterCode 1st Show Deposit #
2010-07-14 00:00:00 100007 Maggie Liu BJ4 9 NULL
2010-07-15 00:00:00 100007 Maggie Liu BJ4 4 NULL
2010-07-16 00:00:00 100007 Maggie Liu BJ4 11 NULL
2010-07-17 00:00:00 100007 Maggie Liu BJ4 8 NULL
2010-07-19 00:00:00 100007 Maggie Liu BJ4 3 NULL
2010-07-20 00:00:00 100007 Maggie Liu BJ4 2 NULL
2010-07-21 00:00:00 100007 Maggie Liu BJ4 1 NULL
2010-07-22 00:00:00 100007 Maggie Liu BJ4 1 NULL
2010-07-23 00:00:00 100007 Maggie Liu BJ4 2 NULL
2010-07-25 00:00:00 100007 Maggie Liu BJ4 1 NULL
secondshow表内容如下:
Date CCID CCName CenterCode 1st Show Deposit #
2010-11-24 00:00:00.000 NULL NULL NULL NULL 1
2010-11-09 00:00:00.000 NULL NULL NULL NULL 2
2010-11-10 00:00:00.000 NULL NULL NULL NULL 1
2010-11-11 00:00:00.000 NULL NULL NULL NULL 1
2010-11-17 00:00:00.000 NULL NULL NULL NULL 2
2010-11-18 00:00:00.000 NULL NULL NULL NULL 1
2010-11-20 00:00:00.000 NULL NULL NULL NULL 1
2010-11-23 00:00:00.000 NULL NULL NULL NULL 1
2010-11-29 00:00:00.000 NULL NULL NULL NULL 1UNION ALL时报如下错
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'NULL' to data type int.
请达人求解啊 最好帮我写一下啊~~谢谢啦~~
(SELECT vw_Activity.[date],vw_Activity.[CCID],vw_Activity.[CCName],
vw_Activity.CenterCode,vw_Activity.[Action] FROM vw_Activity
WHERE [Date]>='2010-07-01' AND [Date]<='2010-07-31' AND CCID='100007') as A
PIVOT (COUNT ([Action]) FOR [Action] in ([1st Show])) as T) as firstshow
union all
select * FROM( SELECT [PayDate] as [Date],'NULL' as [CCID],'NULL' as [CCName],'NULL' as [CenterCode],'NULL' as [1st Show],[900000] as [Deposit #] FROM
(SELECT [paydate],[CreateBy],[CCID] FROM Deposit
WHERE [PayDate]>='2010-11-01' AND [PayDate]<='2010-11-30' AND CCID='900000' ) as A
PIVOT
(COUNT ([CCID]) FOR [CCID] in ([900000])) as T) as secondshowfirstshow表内容如下:date CCID CCName CenterCode 1st Show Deposit #
2010-07-14 00:00:00 100007 Maggie Liu BJ4 9 NULL
2010-07-15 00:00:00 100007 Maggie Liu BJ4 4 NULL
2010-07-16 00:00:00 100007 Maggie Liu BJ4 11 NULL
2010-07-17 00:00:00 100007 Maggie Liu BJ4 8 NULL
2010-07-19 00:00:00 100007 Maggie Liu BJ4 3 NULL
2010-07-20 00:00:00 100007 Maggie Liu BJ4 2 NULL
2010-07-21 00:00:00 100007 Maggie Liu BJ4 1 NULL
2010-07-22 00:00:00 100007 Maggie Liu BJ4 1 NULL
2010-07-23 00:00:00 100007 Maggie Liu BJ4 2 NULL
2010-07-25 00:00:00 100007 Maggie Liu BJ4 1 NULL
secondshow表内容如下:
Date CCID CCName CenterCode 1st Show Deposit #
2010-11-24 00:00:00.000 NULL NULL NULL NULL 1
2010-11-09 00:00:00.000 NULL NULL NULL NULL 2
2010-11-10 00:00:00.000 NULL NULL NULL NULL 1
2010-11-11 00:00:00.000 NULL NULL NULL NULL 1
2010-11-17 00:00:00.000 NULL NULL NULL NULL 2
2010-11-18 00:00:00.000 NULL NULL NULL NULL 1
2010-11-20 00:00:00.000 NULL NULL NULL NULL 1
2010-11-23 00:00:00.000 NULL NULL NULL NULL 1
2010-11-29 00:00:00.000 NULL NULL NULL NULL 1UNION ALL时报如下错
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'NULL' to data type int.
请达人求解啊 最好帮我写一下啊~~谢谢啦~~
解决方案 »
- 如何动态生成表名
- 求解答!!!
- sqlserver里有没有把秒转成 形如 00:00:00 格式的函数?
- 求一sql如何写?
- 一个存储过程可以有多少结果集?
- navicat和navicat9的问题
- 【【【【【【用INSERT INTO 插入一条记录后,怎么样取得该条记录的ID号(表中有一字段(ID)为自增列)??】】】】】】
- 为什么access数据库导入到sql server数据库后数据就不能写入数据库呢?
- 今天发现的问题,给遇到这类情况的一个提示
- Ado调用Sql server7.0存储过程
- MS 2008跨域链接MS 2000
- Analysis Services 2005升级到Analysis Services 2008得问题
union all
select 1 as int
转换字符串类型的null值为int类型时失败。