行转列动态sql

----建表语句

IF object_id('
[tb]
') IS NOT NULL DROP TABLE [
tb
] CREATE TABLE [
tb
]([
sIDNo
] int,
[
sAccount
] int) INSERT [
tb
]
                  SELECT 1001, 15451551
UNION ALL
SELECT 1001, 13151345
UNION ALL
SELECT 1001, 15484565
UNION ALL
SELECT 1002, 15487963
UNION ALL
SELECT 1002, 21544454
UNION ALL
SELECT 1003, 95235646
UNION ALL
SELECT 1003, 12321512

--创建函数

CREATE FUNCTION [dbo].[f_str](@no int) RETURNS varchar(50)
AS BEGIN DECLARE @s varchar(800) SELECT @s = isnull(@s + '
 
', '') + ltrim(sAccount)
                                         FROM tb
                                         WHERE sIDNo = @no RETURN @s END

--查询语句

SELECT sIDNo, dbo.f_str(sIDNo) AS sAccount
FROM tb t
GROUP BY sIDNo