select 名字,sum(case when 类型 = 'A' then 1 else 0 end) [A], sum(case when 类型 = 'B' then 1 else 0 end) [B] from tb group by 名字
select 名字,sum(case when 类型='A' then 1 else 0 end )as A, sum(case when 类型='B' then 1 else 0 end )as B from tb group by 名字
select 名字,sum(case when 类型 = 'A' then 1 else 0 end) [A], sum(case when 类型 = 'B' then 1 else 0 end) [B] from tb group by 名字
select 名字, [A], [B] FROM tab PIVOT(COUNT(类型) for 类型 in(A,B))AS a
与这样写: select 名字,sum(case 类型 when 'A'then 1 else 0 end)as A, sum(case 类型 when 'B' then 1 else 0 end) as B from tab group by 名字 有什么不同
楼上的都正解. select 名字,sum(case when 类型='A' then 1 else 0 end) as [A], sum(case when 类型='B' then 1 else 0 end) as [B] from tb group by 名字试下这个 select 名字 ,case when 类型='A' then count("A") else 0 as [A], case when 类型='B' then count("B") else 0 as [B] from tb group by 名字
CREATE TABLE #TEMP(NAME VARCHAR(50),IDD VARCHAR(50)) INSERT INTO #TEMP SELECT '甲','A' UNION ALL SELECT '甲','B' UNION ALL SELECT '甲','A' UNION ALL SELECT '甲','A' UNION ALL SELECT '甲','C' UNION ALL SELECT '乙','A' UNION ALL SELECT '乙','B' UNION ALL SELECT '丙','A' UNION ALL SELECT '丙','B' DECLARE @SQL VARCHAR(8000),@S VARCHAR(8000) SELECT @SQL = ISNULL(@SQL ,'') + ('isnull(['+IDD+'],''0'') as '+IDD+',') FROM #TEMP GROUP BY IDD set @SQL =SUBSTRING(@SQL,0,LEN(@sql)) SELECT @S = ISNULL(@S + '],[' , '') + IDD FROM (SELECT NAME,IDD,COUNT(IDD) AS COU FROM #TEMP GROUP BY NAME,IDD) AS A GROUP BY IDD SET @S = '[' + @S + ']' EXEC(' SELECT NAME,'+@sql+' FROM ( SELECT NAME,IDD,COUNT(IDD) AS COU FROM #TEMP GROUP BY NAME,IDD ) AS A PIVOT (MAX(COU) FOR IDD IN ('+@s+')) B' ) DROP TABLE #TEMP ------------------------ NAME A B C -------------------------------------------------- ----------- ----------- ----------- 丙 1 1 0 甲 3 1 1 乙 1 1 0(3 行受影响)
--2005以上select 名字,A,B from tb piovt(count(类型) for 类型 in(A,B))a
select 名字,sum(case when 类型 = 'A' then 1 else 0 end) [A],
sum(case when 类型 = 'B' then 1 else 0 end) [B]
from tb
group by 名字
select 名字,sum(case when 类型='A' then 1 else 0 end )as A,
sum(case when 类型='B' then 1 else 0 end )as B
from tb
group by 名字
sum(case when 类型 = 'B' then 1 else 0 end) [B]
from tb
group by 名字
select 名字,
[A],
[B]
FROM tab
PIVOT(COUNT(类型) for 类型 in(A,B))AS a
select 名字,sum(case 类型 when 'A'then 1 else 0 end)as A,
sum(case 类型 when 'B' then 1 else 0 end) as B
from tab
group by 名字
有什么不同
select 名字,sum(case when 类型='A' then 1 else 0 end) as [A],
sum(case when 类型='B' then 1 else 0 end) as [B]
from tb group by 名字试下这个
select 名字 ,case when 类型='A' then count("A") else 0 as [A],
case when 类型='B' then count("B") else 0 as [B]
from tb
group by 名字
CREATE TABLE #TEMP(NAME VARCHAR(50),IDD VARCHAR(50))
INSERT INTO #TEMP
SELECT '甲','A' UNION ALL
SELECT '甲','B' UNION ALL
SELECT '甲','A' UNION ALL
SELECT '甲','A' UNION ALL
SELECT '甲','C' UNION ALL
SELECT '乙','A' UNION ALL
SELECT '乙','B' UNION ALL
SELECT '丙','A' UNION ALL
SELECT '丙','B' DECLARE @SQL VARCHAR(8000),@S VARCHAR(8000)
SELECT @SQL = ISNULL(@SQL ,'') + ('isnull(['+IDD+'],''0'') as '+IDD+',') FROM #TEMP GROUP BY IDD
set @SQL =SUBSTRING(@SQL,0,LEN(@sql))
SELECT @S = ISNULL(@S + '],[' , '') + IDD FROM (SELECT NAME,IDD,COUNT(IDD) AS COU FROM #TEMP GROUP BY NAME,IDD) AS A GROUP BY IDD
SET @S = '[' + @S + ']'
EXEC('
SELECT NAME,'+@sql+'
FROM (
SELECT NAME,IDD,COUNT(IDD) AS COU
FROM #TEMP GROUP BY NAME,IDD
) AS A PIVOT (MAX(COU) FOR IDD IN ('+@s+')) B'
)
DROP TABLE #TEMP
------------------------
NAME A B C
-------------------------------------------------- ----------- ----------- -----------
丙 1 1 0
甲 3 1 1
乙 1 1 0(3 行受影响)