27,579
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#RecordsMoneys_Add') is null
drop table #RecordsMoneys_Add
Go
Create table #RecordsMoneys_Add
(id int identity(1,1),
moneyid varchar(10),
itemname nvarchar(50)
,moneys decimal(12,2))
Insert #RecordsMoneys_Add
select '243','应发奖金',243 union all
select '242','应发奖金',4514 union all
select '242','药比超标罚款',-111 union all
select '242','转院床目超标罚款',-44 union all
select '242','处方扣款',-333 union all
select '242','医保或药剂扣款',0 union all
select '240','应发奖金',0 union all
select '240','药比超标罚款',0 union all
select '240','转院床目超标罚款',0 union all
select '240','处方扣款',0 union all
select '240','医保或药剂扣款',0
Go
--如果这5列的列名固定
--先把行转列的结果生成一个结果集,然后再拿这个结果集代替整个拼接起来中的RecordsMoneys_Add表来进行处理。
with cte
as
(select moneyid,
sum(case when itemname='应发奖金' then moneys else 0 end) as '应发奖金',
sum(case when itemname='药比超标罚款' then moneys else 0 end) as '药比超标罚款',
sum(case when itemname='转院床目超标罚款' then moneys else 0 end) as '转院床目超标罚款',
sum(case when itemname='处方扣款' then moneys else 0 end) as '处方扣款',
sum(case when itemname='医保或药剂扣款' then moneys else 0 end) as '医保或药剂扣款'
from #RecordsMoneys_Add
group by moneyid)
SELECT a.months, b.dept_code ,b.dept_name ,
c.user_code , isnull(c.user_name,username) as user_name,a.moneys,e.tax
,a.userid,a.deptid,a.id,a.RecordUser,a.RecordTime,a.iscw,a.isExtra
FROM Records_Moneys a
inner join public_dept_list b on a.deptid=b.deptid
left outer join public_user_list c on c.userid=a.userid
left join cte d on a.id =d.moneyid
left join RecordMoney_tax e on a.userid=e.userid
where a.months='{0}'
order by a.months
--列名不固定,只能动态
declare @item_list nvarchar(max)
select @item_list=ISNULL(@item_list+',','')+'sum(case when itemname='''+itemname+''' then moneys else 0 end) as '''+itemname+''''
from (select distinct itemname from #RecordsMoneys_Add) as A
exec('select moneyid,'+@item_list+' into #A from #RecordsMoneys_Add group by moneyid
SELECT a.months, b.dept_code ,b.dept_name ,
c.user_code , isnull(c.user_name,username) as user_name,a.moneys,e.tax,
a.userid,a.deptid,a.id,a.RecordUser,a.RecordTime,a.iscw,a.isExtra
FROM Records_Moneys a
inner join public_dept_list b on a.deptid=b.deptid
left outer join public_user_list c on c.userid=a.userid
left join #A d on a.id =d.moneyid
left join RecordMoney_tax e on a.userid=e.userid
where a.months=''{0}''
order by a.months ')
CREATE TABLE [dbo].[RecordsMoneys_Add](
[id] [int] IDENTITY(1,1) NOT NULL,
[MoneyId] [int] NOT NULL,
[itemName] [varchar](50) NOT NULL,
[Moneys] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_RecordsMoneys_Add_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
我就是想要添加所有行,需要都select吗?
SELECT a.months, b.dept_code ,b.dept_name ,
c.user_code , isnull(c.user_name,username) as user_name,a.moneys,e.tax
,a.userid,a.deptid,a.id,a.RecordUser,a.RecordTime,a.iscw,a.isExtra
FROM Records_Moneys a
inner join public_dept_list b on a.deptid=b.deptid
left outer join public_user_list c on c.userid=a.userid
left join RecordsMoneys_Add d on a.id =d.MoneyId
left join RecordMoney_tax e on a.userid=e.userid
where a.months='{0}'
order by a.months
然后整个拼接起来,哈哈--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Name] nvarchar(26),[Moneys] DECIMAL(18,2))
Insert #T
select N'奖金',243 union all
select N'奖金',4514 union all
select N'药比超标罚款',-111
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select rn'
select @sql=@sql+' , SUM(case Name when '''+ Name +''' then [Moneys] else 0 end) ['+ Name +']'
from (select distinct Name from #T) as a
set @sql=@sql+' from (Select *,ROW_NUMBER()OVER(PARTITION BY Name ORDER BY GETDATE()) AS rn from #T)t group by rn'
EXEC(@sql)