解决行转列,列分行,行合并列的疑难问题

SQL code/*================fcuandy========2008.1.10================*/CREATE TABLE ta(id INT IDENTITY(1,1),cid INT,name VARCHAR(10))GOINSERT ta SELECT 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条或n条。单表及多表的写法*/-------------------------------------------------------------以ta为例，cid为分类id,每个id取一条,我以取最小id为约束条件SELECT a.* FROM ta a    WHERE NOT EXISTS(SELECT 1 FROM ta WHERE cid=a.cid AND id<a.id)SELECT a.* FROM ta a    WHERE 1>(SELECT COUNT(*) FROM ta WHERE cid=a.cid AND id<a.id)SELECT a.* FROM ta a    WHERE id IN (SELECT TOP 1 ID FROM ta WHERE cid=a.cid ORDER BY id)SELECT a.* FROM ta a    WHERE id = (SELECT TOP 1 ID FROM ta WHERE cid=a.cid ORDER BY id)SELECT a.* FROM ta a    WHERE id IN (SELECT MIN(ID) FROM ta WHERE cid=a.cid)SELECT a.* FROM ta a    WHERE id = (SELECT MIN(ID) FROM ta WHERE cid=a.cid)SELECT a.*     FROM ta aINNER JOIN     (SELECT MIN(id) mi FROM ta GROUP BY cid) b    ON id = mi--etc.其它的组合写法再不累赘--以cid为分类，每个cid取id最小的2条记录，2可以适当修改SELECT a.* FROM ta a    WHERE 2>(SELECT COUNT(*) FROM ta WHERE cid=a.cid AND id<a.id)SELECt a.* FROM ta a    WHERE id IN (SELECT TOP 2 ID FROM ta WHERE cid=a.cid ORDER BY ID)SELECT a.*    FROM ta aINNER JOIN(SELECT ID,CNT=(SELECT COUNT(*) FROM ta WHERE cid=x.cid AND id<x.id) FROM ta x) b    ON a.id = b.id AND cnt<2--etc.其它的组合写法省略

CREATE TABLE tb(cid INT,className VARCHAR(10))GOINSERT tb SELECT 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 bINNER JOIN ta a    ON a.cid = b.cid    WHERE NOT EXISTS(SELECT 1 FROM ta WHERE cid=a.cid AND id<a.id)GO--多表与单表取数思路一对致，多一次连表操作，可以照上面单表的把其它写法改出来.--同样，取每个cid中id最小的前n条记录，一样的方法--需要注重的是连表时，可以用内连，左连，或是老式的多表写法(from ta ,tb where ta.cid=tb.cid默认转换为内连),采用哪种方式依具你的业务需求。--------------------------------------------/*

*/--以ta为例，以cid分组合并，产生如下的数据结果/*cid     nameS1    a,b,c2    d3    e,f*/--函数实现CREATE FUNCTION myJoinSTR(@cid INT)RETURNS VARCHAR(1000)ASBEGINDECLARE @s VARCHAR(1000)SELECT @s=ISNULL(@s ',','')   name FROM ta WHERE cid = @cidRETURN @sENDGOSELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS FROM taGO/*

*/SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS INTO tx FROM taGOSELECT * FROM txGO--以系统表构建identity列,并以连表方式来将列拆成行SELECT IDENTITY(INT,1,1) id INTO # FROM syscolumns,sysobjectsSELECT id,cid,RIGHT(STUFF(nameS ',',id,LEN(names),''),CHARINDEX(',',REVERSE(STUFF(',' nameS ',',id,LEN(names),'')))) name    FROM tx aINNER JOIN # b    ON SUBSTRING(names ',',id,1)=','ORDER BY cid--以动态语句或循环，或函数的方式略去

行转列*/--单表，以ta为例. 静态行转列,设cid所有出现的可能值已知SELECT         cid_1=MAX(CASE WHEN cid=1 THEN name ELSE NULL END),    cid_2=MAX(CASE WHEN cid=2 THEN name ELSE NULL END),    cid_3=MAX(CASE WHEN cid=3 THEN name ELSE 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 ta GROUP BY cidSELECT @s='SELECT '   STUFF(@s,1,1,'')   ' FROM ta'--你可以在这里PRINT @s 看看，就知道跟上面的静态行转列一样的了。 会写静态行转列，就没理由写不出动态的。多表的同理，把多表的静态行转列写出来，那么动态的也就出来了EXEC(@s)GODROP TABLE tx,#GODROP TABLE ta,tbDROP FUNCTION myJoinSTRGO