CREATE TABLE tb(A INT,B INT,C INT,D INT,E INT) INSERT tb SELECT 2,3,4,5,6 UNION ALL SELECT 1,2,3,5,7 UNION ALL SELECT 1,3,4,5,NULL GO--SELECT * FROM tb GO /* A B C D E 2 3 4 5 6 1 2 3 5 7 1 3 4 5 7*/DECLARE @s1 VARCHAR(8000),@s2 VARCHAR(8000)SELECT @s2=ISNULL(@s2+'+','') + ' ISNULL(RTRIM(''|''+RTRIM(' + name + ')),'''')' , @s1=ISNULL(@s1+'+','') + ' ISNULL((CASE WHEN '+ name + ' IS NULL THEN NULL ELSE ''|''+''' + name + ''' END),'''')' FROM syscolumns WHERE id=OBJECT_ID('tb') EXEC('SELECT STUFF(' + @s1 + ',1,1,'''') n1,STUFF(' + @s2 + ',1,1,'''') n2 FROM tb') GO /* n1 n2 A|B|C|D|E 2|3|4|5|6 A|B|C|D|E 1|2|3|5|7 A|B|C|D 1|3|4|5 */DROP TABLE tb Go
发现个问题 CREATE TABLE tb(A INT,B INT,C INT,D INT,E INT) INSERT tb SELECT 2,3,4,5,6 UNION ALL SELECT 1,2,NULL,5,7 UNION ALL SELECT 1,3,4,5,NULL试试 对列就不对了 如果为空就以0来代替
select n1 = 'A¦B¦C' , n2 = cast(a as varchar) + '|' + cast(b as varchar) + '|' + cast(c as varchar) from tb
INSERT tb SELECT 2,3,4,5,6
UNION ALL SELECT 1,2,3,5,7
UNION ALL SELECT 1,3,4,5,7
GOSELECT * FROM tb
GO
/*
A B C D E
2 3 4 5 6
1 2 3 5 7
1 3 4 5 7*/DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s+'|','') + name FROM syscolumns WHERE id=OBJECT_ID('tb')
SELECT @s=''''+@s+''' n1,'+ 'RTRIM(' + REPLACE(@s,'|',')+''|''+RTRIM(' ) + ') n2'
EXEC('SELECT ' + @s + ' FROM tb')
GO
/*
n1 n2
A|B|C|D|E 2|3|4|5|6
A|B|C|D|E 1|2|3|5|7
A|B|C|D|E 1|3|4|5|7
*/DROP TABLE tb
Go
4 5 NULL
3 4 5
4 5 6这是一组数据要导过来写进表 T 里第二次可能有这么一组数据
a b c d e 4 4 4 4 null
3 5 null 4 5再写入
T的结果集应该是这样
n1 n2
a|b|c 4|5|0
a|b|c 3|4|5
a|b|c 4|5|6
a|b|c|d|e 4|4|4|4|0
a|b|c|d|e 3|5|0|4|5
也就是导过来一个表 需要把列名放入一个字段,值放入一个字段里
SELECT @s=ISNULL(@s+'|','') + name FROM syscolumns WHERE id=OBJECT_ID('Table1')
SELECT @s=''''+@s+''' n1,'+ 'RTRIM(' + REPLACE(@s,'|',')+''|''+RTRIM(' ) + ') n2'
EXEC('SELECT ' + @s + ' FROM Table1')
GO我执行了一下报了这样的错误..服务器: 消息 195,级别 15,状态 10,行 1
'ALB' 不是可以识别的 函数名。
奇怪 没看到用这个啊??
'ALB ' 不是可以识别的 函数名。
我不明白为什么会报这样的错误 是不是某个值为空导致的??
INSERT tb SELECT 2,3,4,5,6
UNION ALL SELECT 1,2,3,5,7
UNION ALL SELECT 1,3,4,5,NULL
GO--SELECT * FROM tb
GO
/*
A B C D E
2 3 4 5 6
1 2 3 5 7
1 3 4 5 7*/DECLARE @s1 VARCHAR(8000),@s2 VARCHAR(8000)SELECT @s2=ISNULL(@s2+'+','') + ' ISNULL(RTRIM(''|''+RTRIM(' + name + ')),'''')' ,
@s1=ISNULL(@s1+'+','') + ' ISNULL((CASE WHEN '+ name + ' IS NULL THEN NULL ELSE ''|''+''' + name + ''' END),'''')'
FROM syscolumns WHERE id=OBJECT_ID('tb')
EXEC('SELECT STUFF(' + @s1 + ',1,1,'''') n1,STUFF(' + @s2 + ',1,1,'''') n2 FROM tb')
GO
/*
n1 n2
A|B|C|D|E 2|3|4|5|6
A|B|C|D|E 1|2|3|5|7
A|B|C|D 1|3|4|5
*/DROP TABLE tb
Go
CREATE TABLE tb(A INT,B INT,C INT,D INT,E INT)
INSERT tb SELECT 2,3,4,5,6
UNION ALL SELECT 1,2,NULL,5,7
UNION ALL SELECT 1,3,4,5,NULL试试 对列就不对了 如果为空就以0来代替
替换成我的表名提示
服务器: 消息 195,级别 15,状态 10,行 1
'ALB ' 不是可以识别的 函数名。
该列就不显示了如果该值为空还是要显示列的 不过列的值以0来显示CREATE TABLE tb(A INT,B INT,C INT,D INT,E INT)
INSERT tb SELECT 2,NULL,4,5,6
UNION ALL SELECT 1,2,NULL,5,7
UNION ALL SELECT 1,3,4,5,NULLn1 n2
A|C|D|E 2|4|5|6
A|B|D|E 1|2|5|7
A|B|C|D 1|3|4|5其实应该是
n1 n2
A|B|C|D|E 2|0|4|5|6
A|B|C|D|E 1|2|0|5|7
A|B|C|D|E 1|3|4|5|0
那么建议如下处理:DECLARE @s1 VARCHAR(8000),@s2 VARCHAR(8000)SELECT @s2=ISNULL(@s2+'+','') + ' ISNULL(RTRIM(''|''+RTRIM([' + name + '])),'''')' ,
@s1=ISNULL(@s1+'+','') + ' ISNULL((CASE WHEN ['+ name + '] IS NULL THEN NULL ELSE ''|''+''' + name + ''' END),'''')'
FROM syscolumns WHERE id=OBJECT_ID('tb')
EXEC('SELECT STUFF(' + @s1 + ',1,1,'''') n1,STUFF(' + @s2 + ',1,1,'''') n2 FROM tb')另外,你所谓的"0就不对了"
指的是将表中值为0的不显示(即把0和null一样来处理),还是将null处理为0
INSERT tb SELECT 2,3,4,5,6
UNION ALL SELECT 1,2,NULL,5,7
UNION ALL SELECT 1,3,4,5,NULL
UNION ALL SELECT NULL,3,4,5,NULL
GOSELECT * FROM tb
GO
/*
A B C D E
--------------------------
2 3 4 5 6
1 2 NULL 5 7
1 3 4 5 NULL
NULL 3 4 5 NULL*/DECLARE @s1 VARCHAR(8000),@s2 VARCHAR(8000)
SELECT @s2=ISNULL(@s2+'+''|''+','') + ' ISNULL(RTRIM([' + name + ']),''0'')' ,
@s1=ISNULL(@s1+'+''|''+','') + ' ISNULL((CASE WHEN ['+ name + '] IS NULL THEN NULL ELSE ''' + name + ''' END),''0'')'
FROM syscolumns WHERE id=OBJECT_ID('tb')
EXEC('SELECT ' + @s1 + ' n1,' + @s2 + ' n2 FROM tb')
PRINT @s1
PRINT @s2
GO
/*
n1 n2
----------------------
A|B|C|D|E 2|3|4|5|6
A|B|0|D|E 1|2|0|5|7
A|B|C|D|0 1|3|4|5|0
0|B|C|D|0 0|3|4|5|0
*/DROP TABLE tb
Go
简化一下.CREATE TABLE tb(A VARCHAR(50),B VARCHAR(50),C VARCHAR(50),D VARCHAR(50),E VARCHAR(50))
INSERT tb SELECT 2,3,4,5,6
UNION ALL SELECT 1,2,NULL,5,7
UNION ALL SELECT 1,3,4,5,NULL
UNION ALL SELECT NULL,3,4,5,NULL
GOSELECT * FROM tb
GO
/*
A B C D E
--------------------------
2 3 4 5 6
1 2 NULL 5 7
1 3 4 5 NULL
NULL 3 4 5 NULL*/DECLARE @s1 VARCHAR(8000),@s2 VARCHAR(8000)
SELECT @s2=ISNULL(@s2+'+''|''+','') + ' ISNULL([' + name + '],''0'')' ,
@s1=ISNULL(@s1+'+''|''+','') + ' (CASE WHEN ['+ name + '] IS NULL THEN ''0'' ELSE ''' + name + ''' END)'
FROM syscolumns WHERE id=OBJECT_ID('tb')
EXEC('SELECT ' + @s1 + ' n1,' + @s2 + ' n2 FROM tb')GO
/*
n1 n2
----------------------
A|B|C|D|E 2|3|4|5|6
A|B|0|D|E 1|2|0|5|7
A|B|C|D|0 1|3|4|5|0
0|B|C|D|0 0|3|4|5|0
*/DROP TABLE tb
Go
SELECT A,'A' FROM tb