关于SQL竖转横解决方法

关于SQL竖转横
czy_id type
1 1 
1 2
1 2
90 2
想得到结果
czy_id type1count type2count 
  1 1 2
  90 0 1  
问该如何实现

类型为1的归总，和类型为2的归总

[解决办法]
两种方式：2005以后有用

SQL code

/*    实现行转列  a  name    objec   score  a       EN      89  a       CH      78  a       HO      99  b       EN      34  b       CH      88  b       HO      66  要求输出结果为：  name    EN  CH  HO  a       89  78  99  b       34  88  66  */    USE tempdb  IF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL   DROP TABLE t      CREATE TABLE t  (      NAME    CHAR(10),      objec   CHAR(10),      score   FLOAT  )  INSERT INTO t  VALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)    SELECT * FROM T    SELECT NAME ,MAX(CASE objec WHEN 'EN' THEN score ELSE 0 END ) EN,MAX(CASE objec WHEN 'CH' THEN score ELSE 0 END ) CH,MAX(CASE objec WHEN 'HO' THEN score ELSE 0 END ) HO  FROM t  GROUP BY NAME   --=======================================================================================================================================================  /*    实现行转列  a  name    objec   score  a       EN      89  a       CH      78  a       HO      99  b       EN      34  b       CH      88  b       HO      66  要求输出结果为：  name    objec       totalsorce  a       EN,CH,HO    266  b       EN,CH,HO    188  */    USE tempdb  IF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL   DROP TABLE t    CREATE TABLE t  (      NAME    CHAR(10),      objec   CHAR(10),      score   FLOAT  )  INSERT INTO t  VALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)    SELECT * FROM T    --为了去除objec中的最后一个逗号，插入一个临时表，并使用substring处理  SELECT name ,(SELECT LTRIM(RTRIM(objec))+',' FROM T WHERE objec=t.objec FOR XML PATH('')) objec,SUM(temp.score) totalscroe INTO #tmp  FROM T temp  GROUP BY name       SELECT NAME ,SUBSTRING(objec,0,LEN(objec)-1) objec ,totalscroe  FROM #tmp
[解决办法]
SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([czy_id] INT,[type] INT)INSERT [tb]SELECT 1,1 UNION ALLSELECT 1,2 UNION ALLSELECT 1,2 UNION ALLSELECT 90,2GO--> 测试语句：SELECT [czy_id],sum(case when [type]=1 then 1 else 0 end) as type1count ,sum(case when [type]=2 then 1 else 0 end) as type2count FROM [tb] group by [czy_id]/*czy_id      type1count  type2count----------- ----------- -----------1           1           290          0           1(2 行受影响)*/