输入是
1,2,3,4,5,6,7
输出为
1
2
3
4
5
6
7
6 个解决方案
#1
论坛太多了.
#2
--行列转换示例--列变行
--测试数据
create table 表(id int,proc1 decimal(20,1),proc2 decimal(20,1),proc3 decimal(20,1))
insert 表 select 12,3.4,6.7,1.1
union all select 13,5.6,10.3,5.6
union all select 14,7.9,9.0,9.9
go
--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''select id,[proc]='''''+name+''''',value=['+name+'] from 表'''
,@s3=@s3+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'id'
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s3=substring(@s3,16,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select * from(''+'+@s3+'+'')a order by id'')')
go
--删除测试
drop table 表
/*--测试结果
id proc value
----------- ----- ----------------------
12 proc1 3.4
12 proc2 6.7
12 proc3 1.1
13 proc3 5.6
13 proc2 10.3
13 proc1 5.6
14 proc1 7.9
14 proc2 9.0
14 proc3 9.9
(所影响的行数为 9 行)
--*/
#3
declare @s varchar(100),@sql varchar(8000)1
set @s='1,2,3,4,5,6,7'
set @sql= 'select '+replace(@s,',',' as id union all select ')
exec(@sql)
2
3
4
5
6
7
#4
CREATE TABLE testColConvertRow
(IdCol varchar(10),StrCol varchar(800))
GO
INSERT INTO testColConvertRow
SELECT 'A','10;15;17;18;45;89;25;36;8799;566;666;988'
UNION ALL
SELECT 'B','10;15;17;18;45;89;25;36;8799;566;666;988'
UNION ALL
SELECT 'C','10;15;17;18;45;89;25;36;8799;566;666;988'
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ColConvertRow'
AND type = 'P')
DROP PROCEDURE dbo.ColConvertRow
GO
CREATE PROCEDURE dbo.ColConvertRow
AS
DECLARE @sql varchar(8000)
DECLARE @col1 varchar(100)
DECLARE @col2 varchar(3000)
declare @tb table(a varchar(10),b varchar(800))
CREATE TABLE #re (col1 varchar(10),col2 varchar(30))
SET NOCOUNT ON
insert into @tb select IdCol,strCol from testColConvertRow
DECLARE c_cur cursor FAST_FORWARD
FOR SELECT a,b FROM @tb
OPEN c_cur
FETCH NEXT FROM c_cur INTO @col1,@col2
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='INSERT INTO #re(col1,col2) '
SET @sql=@sql+'SELECT '''+@col1+''','''+REPLACE(@col2,';','''UNION ALL SELECT '''+@col1+''' AS Col1,''')+''''
EXEC(@SQL)
FETCH NEXT FROM c_cur INTO @col1,@col2
END
CLOSE c_cur
DEALLOCATE c_cur
SELECT * FROM #RE
SET NOCOUNT OFF
GO
#5
declare @s as varchar(100)
set @s = '1,2,3,4,5,6,7'
set @s = replace(@s , ',' , ' union select ')
exec('select ' + @s)
/*
-----------
1
2
3
4
5
6
7
*/
#6
--但是要建立一个nums表 辅助表
create table nums
(
nums int
)
insert into select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
declare @str nvarchar(max)
set @str='1,2,3,4,5';
select num,substring(@str,num,1)from nums
where num<=len(@str)
and charindex(',',left(@str,num),num)=0
#1
论坛太多了.
#2
--行列转换示例--列变行
--测试数据
create table 表(id int,proc1 decimal(20,1),proc2 decimal(20,1),proc3 decimal(20,1))
insert 表 select 12,3.4,6.7,1.1
union all select 13,5.6,10.3,5.6
union all select 14,7.9,9.0,9.9
go
--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''select id,[proc]='''''+name+''''',value=['+name+'] from 表'''
,@s3=@s3+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'id'
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s3=substring(@s3,16,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select * from(''+'+@s3+'+'')a order by id'')')
go
--删除测试
drop table 表
/*--测试结果
id proc value
----------- ----- ----------------------
12 proc1 3.4
12 proc2 6.7
12 proc3 1.1
13 proc3 5.6
13 proc2 10.3
13 proc1 5.6
14 proc1 7.9
14 proc2 9.0
14 proc3 9.9
(所影响的行数为 9 行)
--*/
#3
declare @s varchar(100),@sql varchar(8000)1
set @s='1,2,3,4,5,6,7'
set @sql= 'select '+replace(@s,',',' as id union all select ')
exec(@sql)
2
3
4
5
6
7
#4
CREATE TABLE testColConvertRow
(IdCol varchar(10),StrCol varchar(800))
GO
INSERT INTO testColConvertRow
SELECT 'A','10;15;17;18;45;89;25;36;8799;566;666;988'
UNION ALL
SELECT 'B','10;15;17;18;45;89;25;36;8799;566;666;988'
UNION ALL
SELECT 'C','10;15;17;18;45;89;25;36;8799;566;666;988'
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ColConvertRow'
AND type = 'P')
DROP PROCEDURE dbo.ColConvertRow
GO
CREATE PROCEDURE dbo.ColConvertRow
AS
DECLARE @sql varchar(8000)
DECLARE @col1 varchar(100)
DECLARE @col2 varchar(3000)
declare @tb table(a varchar(10),b varchar(800))
CREATE TABLE #re (col1 varchar(10),col2 varchar(30))
SET NOCOUNT ON
insert into @tb select IdCol,strCol from testColConvertRow
DECLARE c_cur cursor FAST_FORWARD
FOR SELECT a,b FROM @tb
OPEN c_cur
FETCH NEXT FROM c_cur INTO @col1,@col2
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='INSERT INTO #re(col1,col2) '
SET @sql=@sql+'SELECT '''+@col1+''','''+REPLACE(@col2,';','''UNION ALL SELECT '''+@col1+''' AS Col1,''')+''''
EXEC(@SQL)
FETCH NEXT FROM c_cur INTO @col1,@col2
END
CLOSE c_cur
DEALLOCATE c_cur
SELECT * FROM #RE
SET NOCOUNT OFF
GO
#5
declare @s as varchar(100)
set @s = '1,2,3,4,5,6,7'
set @s = replace(@s , ',' , ' union select ')
exec('select ' + @s)
/*
-----------
1
2
3
4
5
6
7
*/
#6
--但是要建立一个nums表 辅助表
create table nums
(
nums int
)
insert into select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
declare @str nvarchar(max)
set @str='1,2,3,4,5';
select num,substring(@str,num,1)from nums
where num<=len(@str)
and charindex(',',left(@str,num),num)=0