1.行转列 和 2. sql计算总记录条数和指定的条件字段的总个数

SELECT count(*) sumNumber,sum(case when iscm = '1' then 1 else 0 end ) cms FROM  ps_jg_person

sumNumber ：总记录条数

cms ：iscm字段值为'1' 的总个数

行专列sql

//行转列
// @sql_str = 是主体的sql语句
//@sql_col = 是动态要展示的列
DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') +QUOTENAME([year])  from W_SJSJTB group by year ORDER BY [year] DESC
SET @sql_str = 'select * from W_SJSJTB pivot ( max([value]) for [year]  in ('+ @sql_col +')) a '
PRINT (@sql_str)
EXEC (@sql_str)



DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT TOP 3 @sql_col = ISNULL(@sql_col + ',','') +QUOTENAME([year])  from W_SJSJTB group by year ORDER BY [year] DESC
SET @sql_str = '
select * from(
select b.jgdm,b.year,b.value,b.model_id,c.mc ,c.hc from W_SJSJTB b , W_SJMODEL c WHERE b.model_id = c.id and b.jgdm = ''420000001008'') d pivot ( max([value]) for [year]  in ('+ @sql_col +')) a ORDER BY hc '
PRINT (@sql_str)
EXEC (@sql_str)