转：fcuandy 行转列,列分行,行合并列,etc

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

CREATE TABLE ta(id INT IDENTITY ( 1 , 1 ),cid INT ,name VARCHAR ( 10 ))
GO
INSERT 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
同一分类中取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 a
INNER 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 a
INNER 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.其它的组合写法再不累赘




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

CREATE TABLE tb(cid INT ,className VARCHAR ( 10 ))
GO
INSERT 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 b
INNER 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默认转换为内连),采用哪种方式依具你的业务需求。



-- --------------------------------------------------------
/*
示例 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 + ' , ' , '' ) + name FROM ta WHERE cid = @cid
    RETURN @s
END
GO
SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS FROM ta
GO

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

SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS INTO tx FROM ta
GO
SELECT * FROM tx
GO
-- 以系统表构建identity列,并以连表方式来将列拆成行
SELECT IDENTITY ( INT , 1 , 1 ) id INTO # 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 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 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