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

*/

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