*/

--以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

/*

*/

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
--以动态语句或循环，或函数的方式略去

行转列
*/

--单表，以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

SQL code/*=============
===fcuandy=====
===2008.1.10===
=============*/

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条或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.其它的组合写法省略

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默认转换为内连),
采用哪种方式依具你的业务需求。



--------------------------------------------
/*