日期:2014-05-18  浏览次数:20472 次

关于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]
GO
CREATE TABLE [tb]([czy_id] INT,[type] INT)
INSERT [tb]
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 90,2
GO

--> 测试语句:
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           2
90          0           1

(2 行受影响)



*/