SQL 常用列转行例子
2011-12-09 15:19 Andrew.Wangxu 阅读(349) 评论(1) 编辑 收藏 举报这是一个列转行的例子,先上代码再上图吧~~ SQL Server 2005 测试通过~
CREATE TABLE TB(date SMALLDATETIME, matter NVARCHAR(5))
INSERT TB
SELECT '2008/12/15', N'吃饭' UNION ALL
SELECT '2008/12/15', N'上班' UNION ALL
SELECT '2008/12/15', N'睡觉' UNION ALL
SELECT '2008/12/16', N'休息' UNION ALL
SELECT '2008/12/16', N'逛街'
GO
CREATE FUNCTION F_COMBINESTR(@date SMALLDATETIME)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @SQL NVARCHAR(100)
SET @SQL=N''
SELECT @SQL=@SQL+N'-' + MATTER FROM TB WHERE DATE=@date
RETURN STUFF(@SQL,1,1,'')
END
GO
SELECT * FROM TB
SELECT DATE,MATTER=DBO.F_COMBINESTR(DATE) FROM TB GROUP BY DATE
DROP TABLE TB
DROP FUNCTION F_COMBINESTR