unpivot表列值

问题描述

我有一张桌子社区，它有一些价值，比如......

i have one table socitymain it''s some values like...

id  | socityname |  house |  population |  areaofsocity

1   |  abcd      |  10    |   150       |     10
2   |  cdf       |  5     |     15      |     20  
3   |  zxc       | 20     |   20        |     10   
4   |  bnm       | 98     |  10         |    30

i希望像这样在区域范围内重新调整..............

i want reslut in range of areawise like this..............

values     |  0to10 |   11to20 |  21to30  |  31up
socity     |   2    |     1    |    1     |
house      |  30    |   5      |   98     |
population |   170  |     15   |    10    |

我怎么能这样做...............

how i can do this...............

推荐答案

CREATE TABLE Aver(ques_id int,qtxt varchar(20),opid int,score int,performance varchar(30))

 insert into Aver values( 1,'gdggf',1,10,'Excellent'),( 2,'6t7ik,ikik',2,12,'Good'),(1,'gdggf',3,3,'Average'),(1,'gdggf',4,4,'Poor')


 select * from Aver
 declare @tab1e table(Id int,Qtxt varchar(20),Excellent int,Good int,Average int,Poor int)

 insert into @tab1e(ID)
 select distinct ques_id from Aver

update T set T.Qtxt=A.qtxt,T.Excellent=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Excellent'
update T set T.Qtxt=A.qtxt,T.Good=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Good'
update T set T.Qtxt=A.qtxt,T.Average=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Average'
update T set T.Qtxt=A.qtxt,T.Poor=A.score from @tab1e T inner Join Aver A on A.ques_id=T.Id where A.performance='Poor'

select Id,Qtxt,case when Excellent is null then 0 else Excellent end 'Excellent',case when Good is null then 0 else Good end 'Good',
case when Average is null then 0 else Average end 'Average',case when Poor is null then 0 else Poor end 'Poor'from @tab1e

with a as
(
    select convert(varchar,areaofsocity-10) + '-' + convert(varchar,areaofsocity) as value,
    count(socityname) as socity,
    sum(house) as house,
    sum(population) as population
    from
    (
        select 1  id ,  'abcd'    socityname  ,  10  house  ,   150    population   ,     10 areaofsocity union all
        select 2   ,  'cdf'       ,  5     ,     15      ,     20   union all
        select 3   ,  'zxc'       , 20     ,   20        ,     10    union all
        select 4   ,  'bnm'       , 98     ,  10         ,    30
    ) as a
    group by areaofsocity
)

select 'socity' as value,sum([0-10])[0-10],sum([10-20]) [10-20],sum([20-30]) [20-30]
from a
PIVOT  (sum (socity) for value in ([0-10],[10-20],[20-30])) as a

union all

select 'house' as value,sum([0-10]) [0-10],sum([10-20]) [10-20],sum([20-30]) [20-30]
from a
PIVOT  (sum (house) for value in ([0-10],[10-20],[20-30])) as a

union all

select 'population' as value,sum([0-10]),sum([10-20]),sum([20-30])
from a
PIVOT  (sum (population) for value in ([0-10],[10-20],[20-30])) as a

快乐编码！

：）

Happy Coding!
:)

这篇关于unpivot表列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！