ID Name
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 009
10 010如何使用语句变成
001 002 003 004 005
006 007 008 009 010
高手请指教啊...
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 009
10 010如何使用语句变成
001 002 003 004 005
006 007 008 009 010
高手请指教啊...
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID INT,Name VARCHAR(3))
INSERT INTO #T
SELECT '1','001' UNION ALL
SELECT '2','002' UNION ALL
SELECT '3','003' UNION ALL
SELECT '4','004' UNION ALL
SELECT '5','005' UNION ALL
SELECT '6','006' UNION ALL
SELECT '7','007' UNION ALL
SELECT '8','008' UNION ALL
SELECT '9','009' UNION ALL
SELECT '10','010'--SQL查询如下:SELECT
MAX(CASE (ID-1)%5 WHEN 0 THEN Name ELSE '' END) [Name1],
MAX(CASE (ID-1)%5 WHEN 1 THEN Name ELSE '' END) [Name1],
MAX(CASE (ID-1)%5 WHEN 2 THEN Name ELSE '' END) [Name1],
MAX(CASE (ID-1)%5 WHEN 3 THEN Name ELSE '' END) [Name1],
MAX(CASE (ID-1)%5 WHEN 4 THEN Name ELSE '' END) [Name1]
FROM #T
GROUP BY (ID-1)/5/*
Name1 Name1 Name1 Name1 Name1
----- ----- ----- ----- -----
001 002 003 004 005
006 007 008 009 010(2 行受影响)
*/
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID INT,Name VARCHAR(3))
INSERT INTO #T
SELECT '1','001' UNION ALL
SELECT '2','002' UNION ALL
SELECT '3','003' UNION ALL
SELECT '4','004' UNION ALL
SELECT '5','005' UNION ALL
SELECT '6','006' UNION ALL
SELECT '7','007' UNION ALL
SELECT '8','008' UNION ALL
SELECT '9','009' UNION ALL
SELECT '10','010'--SQL查询如下:SELECT
MAX(CASE (ID-1)%5 WHEN 0 THEN Name ELSE '' END) [Name1],
MAX(CASE (ID-1)%5 WHEN 1 THEN Name ELSE '' END) [Name2],
MAX(CASE (ID-1)%5 WHEN 2 THEN Name ELSE '' END) [Name3],
MAX(CASE (ID-1)%5 WHEN 3 THEN Name ELSE '' END) [Name4],
MAX(CASE (ID-1)%5 WHEN 4 THEN Name ELSE '' END) [Name5]
FROM #T
GROUP BY (ID-1)/5/*
Name1 Name2 Name3 Name4 Name5
----- ----- ----- ----- -----
001 002 003 004 005
006 007 008 009 010(2 行受影响)
*/
ALTER proc p_show
@tbname sysname, --要处理的表名
@col int=2 --一条要显示多少条记录
as
declare @fd varchar(8000),@fds varchar(8000)
,@id char(38)select @fd='',@fds=''
select @fds=@fds+',['+name+']'
,@fd=@fd+',['+name+']'
+case status
when 0x80 then '=cast(['+name+'] as decimal(38,0))'
else '' end
from syscolumns
where id=object_id(@tbname)
select @fds=substring(@fds,2,8000)
,@id='['+cast(newid() as char(36))+']'
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@i int
select @s1='',@s2='',@s3='',@i=0
while @i<@col
select @s1=@s1+',@'+cast(@i as varchar)
,@s2=@s2+',@'+cast(@i as varchar)+'='''
+@fds+' from # where '+@id+'%'
+cast(@col as varchar)+'='
+cast(@i as varchar)+''''
,@s3=@s3+'+''select ''+@'+cast(@i as varchar)
,@i=@i+1
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)print('declare '+@s1+'
select '+@s2+'
print('+@s3+')
')exec('select '+@id+'=identity(int,0,1)'+@fd+' into # from ['+@tbname+']
select * from # where '+@id+'%2=0
select * from # where '+@id+'%2=1')
http://topic.csdn.net/u/20080920/15/424c77bf-7610-4888-be85-9a43e70f55c6.html