sql行转列问题

2015-09-10 01:31:01 217 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

jujian19881004 墨衍会员 · AI 创作全网分发关注 + 加入社区

CRC校验查表法详解 CRC校验的查表法理解算法原理基础计算查表法原理及计算查表法中的表CRC校验中数据逆序CRC校验中的其它参数算法原理 CRC(Cyclic Redundance Check)循环冗余校验，是基于某个多项式计算出一组校验码，用于核对数据传输过程中是否被更改或传输错误基础计算假设需要检验的数据 g :10100111，这串二进制码可以表示为代数多项式g(x) = x^7 + x^5 + x^2 + x +1, 而作为除数的多项式 h(x) 可以自由选择或者按照国际标准选择，一般h(x)的阶数如果是m 阅读详情

想从上面的图通过sqlserver语句变成下图的格式，请教sql语句应该如何写

...全文

VMware Converter 6.2.0保姆级教程：从物理机到ESXi主机的完整迁移指南（含常见错误排查）本文提供VMware Converter 6.2.0从物理机迁移至ESXi主机的完整保姆级教程。详细解析了迁移前的环境规划、核心转换过程步骤、版本差异对比，并重点针对代理安装失败、任务卡顿、虚拟机无法启动等常见错误提供了实战排查与解决方案，确保P2V迁移顺利进行。阅读详情

jujian19881004

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

有问又问 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

打赏
举报

给个文本的测试数据啊！
回答问题还要先看图打字？

sql行转列问题

相关推荐