Sql语句求帮忙
怎么把后面的6列变成下面的一列显示123456
[解决办法]
方法1:拼接SQL,这个编写麻烦,效率低。
方法2:全文索引,这个就没那么好搞了
[解决办法]
给你写了一通俗易懂,相信你能看得懂,若要加查询条件可以ORDER BY 前面加即可。
select * from
(
select BigCategory,SmallCategroy,Model_No ,1 as months
,sum(case when BizType='S' then Month1Num else 0 end ) as S
,sum(case when BizType='I' then Month1Num else 0 end ) as I
,sum(case when BizType='P' then Month1Num else 0 end )as P
from test
GROUP BY BigCategory,SmallCategroy,Model_No
union all
select BigCategory,SmallCategroy,Model_No ,2 as months
,sum(case when BizType='S' then Month2Num else 0 end ) as S
,sum(case when BizType='I' then Month2Num else 0 end ) as I
,sum(case when BizType='P' then Month2Num else 0 end )as P
from test
GROUP BY BigCategory,SmallCategroy,Model_No
union all
select BigCategory,SmallCategroy,Model_No ,3 as months
,sum(case when BizType='S' then Month3Num else 0 end ) as S
,sum(case when BizType='I' then Month3Num else 0 end ) as I
,sum(case when BizType='P' then Month3Num else 0 end )as P
from test
GROUP BY BigCategory,SmallCategroy,Model_No
union all
select BigCategory,SmallCategroy,Model_No ,4 as months
,sum(case when BizType='S' then Month4Num else 0 end ) as S
,sum(case when BizType='I' then Month4Num else 0 end ) as I
,sum(case when BizType='P' then Month4Num else 0 end )as P
from test
GROUP BY BigCategory,SmallCategroy,Model_No
union all
select BigCategory,SmallCategroy,Model_No ,5 as months
,sum(case when BizType='S' then Month5Num else 0 end ) as S
,sum(case when BizType='I' then Month5Num else 0 end ) as I
,sum(case when BizType='P' then Month5Num else 0 end )as P
from test
GROUP BY BigCategory,SmallCategroy,Model_No
union all
select BigCategory,SmallCategroy,Model_No ,6 as months
,sum(case when BizType='S' then Month6Num else 0 end ) as S
,sum(case when BizType='I' then Month6Num else 0 end ) as I
,sum(case when BizType='P' then Month6Num else 0 end )as P
from test
GROUP BY BigCategory,SmallCategroy,Model_No
) K
ORDER BY BigCategory,SmallCategroy,Model_No