sql行转列问题

jujian19881004 2015-09-10 01:31:01


想从上面的图通过sqlserver语句变成下图的格式,请教sql语句应该如何写
...全文
183 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
有问又问 2015-09-14
  • 打赏
  • 举报
回复
-小蕾- 2015-09-14
  • 打赏
  • 举报
回复


WITH table1(Com_Budget_subjects_code,Com_Budget_subjects_title,Com_Budget_subjects_type,YEARS,YSPrice,SJPrice) AS (
    SELECT 660101,'工资','固定费用',2014,100,0 UNION ALL
    SELECT 660101,'工资','固定费用',2015,1100,307 UNION ALL
    SELECT 660102,'奖金','变动费用',2014,200,0 UNION ALL
    SELECT 660102,'奖金','变动费用',2015,2200,608
)
select Com_Budget_subjects_code,Com_Budget_subjects_title,Com_Budget_subjects_type,
max(case YEARS when '2014' then YSPrice else 0 end) '2014YSPrice',
max(case YEARS when '2014' then SJPrice else 0 end) '2014SJPrice',
max(case YEARS when '2015' then YSPrice else 0 end) '2015YSPrice',
max(case YEARS when '2015' then SJPrice else 0 end) '2015SJPrice'
from table1
group by Com_Budget_subjects_code,Com_Budget_subjects_title,Com_Budget_subjects_type order by Com_Budget_subjects_code





Tiger_Zhao 2015-09-11
  • 打赏
  • 举报
回复
WITH 
/* 测试数据
table1(Com_Budget_subjects_code,Com_Budget_subjects_title,Com_Budget_subjects_type,YEARS,YSPrice,SJPrice)AS(
SELECT 660101,'工资','固定费用',2014,100,0 UNION ALL
SELECT 660101,'工资','固定费用',2015,1100,307 UNION ALL
SELECT 660102,'奖金','变动费用',2014,200,0 UNION ALL
SELECT 660102,'奖金','变动费用',2015,2200,608
),*/
a AS (
SELECT Com_Budget_subjects_code,
Com_Budget_subjects_title,
Com_Budget_subjects_type,
CONVERT(varchar(11),YEARS)+'YSPrice' AS Item,
YSPrice AS Price
FROM table1
UNION ALL
SELECT Com_Budget_subjects_code,
Com_Budget_subjects_title,
Com_Budget_subjects_type,
CONVERT(varchar(11),YEARS)+'SJPrice' AS Item,
SJPrice AS Price
FROM table1
)
SELECT *
FROM a
PIVOT (SUM(Price) FOR
Item IN ([2014YSPrice], [2014SJPrice],
[2015YSPrice], [2015SJPrice])
) P

Com_Budget_subjects_code Com_Budget_subjects_title Com_Budget_subjects_type 2014YSPrice 2014SJPrice 2015YSPrice 2015SJPrice
------------------------ ------------------------- ------------------------ ----------- ----------- ----------- -----------
660101 工资 固定费用 100 0 1100 307
660102 奖金 变动费用 200 0 2200 608
道玄希言 2015-09-10
  • 打赏
  • 举报
回复


;with cte(code, title, [types], [years], yjprice, sjprice) as
(
select '660101','工资','固定费用','2014',100,0 union all
select '660101','工资','固定费用','2015',1100,307 union all
select '660102','奖金','变动费用','2014',200,0 union all
select '660102','奖金','变动费用','2015',2200,608 
)
select a.code, a.title, a.[types], 
a.[2014yj], a.[2014sj],
b.[2015yj], b.[2015sj]
from
(
select code, title, [types], yjprice as [2014yj], sjprice as [2014sj] 
from cte where years = '2014'
) as a
inner join
(
select code, yjprice as [2015yj], sjprice as [2015sj] 
from cte where years = '2015'
) as b
on a.code = b.code

kingzhh 2015-09-10
  • 打赏
  • 举报
回复
declare @ta table( code nvarchar(6), title nvarchar(12), type_s nvarchar(12), years int, yiprice int, sjprice int) insert into @ta (code,title,type_s,years,yiprice,sjprice) values ('660101','工资','固定费用',2014,100,0), ('660101','工资','固定费用',2015,1100,307), ('660102','奖金','变动费用',2014,200,0), ('660102','奖金','变动费用',2015,2200,608); select code,Title,type_s, max(case when years=2014 then yiprice end) as [2014yiprice], max(case when years=2014 then sjprice end) as [2014siprice], max(case when years=2015 then yiprice end) as [2015yiprice], max(case when years=2015 then sjprice end) as [2015siprice] from @ta group by code,title,type_s
jujian19881004 2015-09-10
  • 打赏
  • 举报
回复
Com_Budget_subjects_code Com_Budget_subjects_title Com_Budget_subjects_type YEARS YSPrice SJPrice 660101 工资 固定费用 2014 100 0 660101 工资 固定费用 2015 1100 307 660102 奖金 变动费用 2014 200 0 660102 奖金 变动费用 2015 2200 608
Tiger_Zhao 2015-09-10
  • 打赏
  • 举报
回复
给个文本的测试数据啊!
回答问题还要先看图打字?

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧