行转列,列分行,行合并列

/*=============
===fcuandy=====
===2008.1.23===
=============*/

CREATE TABLE ta(idINT IDENTITY(1,1),cidINT,nameVARCHAR(10))
GO
INSERT taSELECT 1,'a'
UNION ALL SELECT 1,'b'
UNION ALL SELECT 1,'c'
UNION ALL SELECT 2,'d'
UNION ALL SELECT 3,'e'
UNION ALL SELECT 3,'f'
GO

/*
示例 1
同一分类中取1条或n条。
单表及多表的写法
*/

-----------------------------------------------------------
--以ta为例，cid为分类id,每个id取一条,我以取最小id为约束条件
SELECT a.* FROM ta a
   WHERE NOT EXISTS(SELECT 1 FROM taWHERE cid=a.cidAND id<a.id)
SELECT a.* FROM ta a
   WHERE 1>(SELECT COUNT(*)FROM taWHERE cid=a.cidAND id<a.id)

SELECT a.* FROM ta a
   WHERE idIN (SELECT TOP 1 IDFROM taWHERE cid=a.cidORDER BY id)
SELECT a.* FROM ta a
   WHERE id= (SELECT TOP 1 IDFROM taWHERE cid=a.cidORDER BY id)
SELECT a.* FROM ta a
   WHERE idIN (SELECT MIN(ID)FROM taWHERE cid=a.cid)
SELECT a.* FROM ta a
   WHERE id= (SELECT MIN(ID)FROM taWHERE cid=a.cid)
SELECT a.*
   FROM ta a
INNER JOIN
    (SELECT MIN(id) miFROM taGROUP BY cid) b
   ON id= mi
--etc.其它的组合写法再不累赘

--以cid为分类，每个cid取id最小的2条记录，2可以适当修改
SELECT a.* FROM ta a
   WHERE 2>(SELECT COUNT(*)FROM taWHERE cid=a.cidAND id<a.id)
SELECt a.* FROM ta a
   WHERE idIN (SELECT TOP 2 IDFROM taWHERE cid=a.cidORDER BY ID)
SELECT a.*
   FROM ta a
INNER JOIN
    (SELECT ID,CNT=(SELECT COUNT(*)FROM taWHERE cid=x.cidAND id<x.id)FROM ta x) b
   ON a.id= b.idAND cnt<2
--etc.其它的组合写法再不累赘




--上面是针对ta单表。 如果多表，下面以二表为例

CREATE TABLE tb(cidINT,classNameVARCHAR(10))
GO
INSERT tbSELECT 1,'A'
UNION ALL SELECT 2,'B'
UNION ALL SELECT 3,'C'
GO
--ta,tb以cid关联，取每个cid中id最小的一条记录,需要 tb.className,tb.cid,ta.id,ta.name列。
SELECT b.*,a.*
   FROM tb b
INNER JOIN ta a
   ON a.cid= b.cid
   WHERE NOT EXISTS(SELECT 1 FROM taWHERE cid=a.cidAND id<a.id)
GO
--多表与单表取数思路一对致，多一次连表操作，可以照上面单表的把其它写法改出来.
--同样，取每个cid中id最小的前n条记录，一样的方法
--需要注意的是连表时，可以用内连，左连，或是老式的多表写法(from ta ,tb where ta.cid=tb.cid默认转换为内连),采用哪种方式依具你的业务需求。



----------------------------------------------------------
/*
示例 2
所谓的多行同组合并
*/

--以ta为例，以cid分组合并，产生如下的数据结果
/*
cid     nameS
1    a,b,c
2    d
3    e,f
*/
--函数实现
CREATE FUNCTION myJoinSTR
(
@cid INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
   DECLARE @s VARCHAR(1000)
   SELECT @s=ISNULL(@s+',','')+ nameFROM taWHERE cid= @cid
   RETURN @s
END
GO
SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameSFROM ta
GO

/*
示例 3
列拆分为行.
以上例生成的数据格式为示例表,将a,b,c以,分融成行,即上个示例的反操作
*/

SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameSINTO txFROM ta
GO
SELECT * FROM tx
GO
--以系统表构建identity列,并以连表方式来将列拆成行
SELECT IDENTITY(INT,1,1) idINTO #FROM syscolumns,sysobjects

SELECT id,cid,RIGHT(STUFF(nameS+',',id,LEN(names),''),CHARINDEX(',',REVERSE(STUFF(','+nameS+',',id,LEN(names),'')))) name
   FROM tx a
INNER JOIN # b
   ON SUBSTRING(names+',',id,1)=','
ORDER BY cid
--以动态语句或循环，或函数的方式略去


/*示例 4
行转列
这是论坛上问的最多的，没啥好说的，照猫画虎。
*/

--单表，以ta为例. 静态行转列,设cid所有出现的可能值已知
SELECT    
    cid_1=MAX(CASE WHEN cid=1 THEN nameELSE NULL END),
    cid_2=MAX(CASE WHEN cid=2 THEN nameELSE NULL END),
    cid_3=MAX(CASE WHEN cid=3 THEN nameELSE NULL END)
   FROM ta
--单表,以ta为例，动态行转列,设cid所有出现的可能值未知

DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + ',cid_' + RTRIM(cid)+ '= MAX(CASE WHEN cid=' + RTRIM(cid)+ ' THEN name ELSE null END)' FROM taGROUP BY cid
SELECT @s='SELECT' + STUFF(@s,1,1,'')+ ' FROM ta'
--你可以在这里PRINT @s 看看，就知道跟上面的静态行转列一样的了。 会写静态行转列，就没理由写不出动态的。多表的同理，把多表的静态行转列写出来，那么动态的也就出来了
EXEC(@s)
GO

DROP TABLE tx,#
GO

DROP TABLE ta,tb
DROP FUNCTION myJoinSTR
GO