日期:2014-05-16 浏览次数:20497 次
CREATE TABLE test(dptid varchar(20),dptname varchar(50),liushi1 int,
liushi2 int,liushi3 int,liushi4 int,liushi5 int,
liushi6 int,Outqty int,
[year] varchar(4),[month] varchar(2))
INSERT INTO test(dptid,dptname,liushi1,liushi2,liushi3,liushi4,liushi5,liushi6,Outqty,[year],[month])
SELECT
'0118612', '成都分理处', 0, 2, 0, 0, 0, 0, 2, 2014, 1
UNION ALL
SELECT
'0118612', '成都分理处', 0, 0, 0, 1, 0, 0, 1, 2014, 2
UNION ALL
SELECT
'0118612', '成都分理处', 1, 0, 0, 1, 0, 0, 2, 2014, 3
CREATE TABLE test(dptid varchar(20),dptname varchar(50),liushi1 int,
liushi2 int,liushi3 int,liushi4 int,liushi5 int,
liushi6 int,Outqty int,
[year] varchar(4),[month] varchar(2))
INSERT INTO test(dptid,dptname,liushi1,liushi2,liushi3,liushi4,liushi5,liushi6,Outqty,[year],[month])
SELECT
'0118612', '成都分理处', 0, 2, 0, 0, 0, 0, 2, 2014, 1
UNION ALL
SELECT
'0118612', '成都分理处', 0, 0, 0, 1, 0, 0, 1, 2014, 2
UNION ALL
SELECT
'0118612', '成都分理处', 1, 0, 0, 1, 0, 0, 2, 2014, 3
select isnull(a.dptid,'0118612') as dptid , isnull(a.dptname,'成都分理处') as dptname ,
isnull(a.liushi1,0) as liushi1 ,
isnull(a.liushi2,0) as liushi2 ,
isnull(a.liushi3,0) as liushi3 ,
isnull(a.liushi4,0) as liushi4 ,
isnull(a.liushi5,0) as liushi5 ,
isnull(a.liushi6,0) as liushi6 ,
isnull(a.Outqty,0) as Outqty ,
isnull(a.[year],'2014') as [year] ,
isnull(b.number,0) as [month]
from test a right join master..spt_values b on a.[month] = b.number
where b.[type]='p' and b.number between 1 and 12
/*
dptid dptname liushi1 liushi2 liushi3 liushi4 liushi5 liushi6 Outqty year month
-------------------- -------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---- -----------
0118612 成都分理处 &nb