CREATE TABLE TB([列1] INT, [列2] VARCHAR(1), [列3] INT, [列4] INT, [列5] VARCHAR(2)) INSERT TB SELECT 1, 'w', null, null, 'w' UNION ALL SELECT 1, 'w', 2, null, 's' UNION ALL SELECT 1, 'w', null, null, 'w' UNION ALL SELECT 1, null, null, null, 'fg' UNION ALL SELECT 1, 'w', null, null, 'w'DECLARE @STR VARCHAR(8000) SET @STR='' SET @STR=ISNULL((SELECT TOP 1 ',[列1]' FROM TB WHERE [列1] IS NOT NULL),'')+ +ISNULL((SELECT TOP 1 ',[列2]' FROM TB WHERE [列2] IS NOT NULL),'')+ +ISNULL((SELECT TOP 1 ',[列3]' FROM TB WHERE [列3] IS NOT NULL),'')+ +ISNULL((SELECT TOP 1 ',[列4]' FROM TB WHERE [列4] IS NOT NULL),'')+ +ISNULL((SELECT TOP 1 ',[列5]' FROM TB WHERE [列5] IS NOT NULL),'')SET @STR=STUFF(@STR, 1, 1, '')EXEC('SELECT '+@STR+' FROM TB')DROP TABLE TB /* 列1 列2 列3 列5 ----------- ---- ----------- ---- 1 w NULL w 1 w 2 s 1 w NULL w 1 NULL NULL fg 1 w NULL w*/
CREATE TABLE TB([列1] INT, [列2] VARCHAR(1), [列3] INT, [列4] INT, [列5] VARCHAR(2)) INSERT TB SELECT 1, 'w', null, null, 'w' UNION ALL SELECT 1, 'w', 2, null, 's' UNION ALL SELECT 1, 'w', null, null, 'w' UNION ALL SELECT 1, null, null, null, 'fg' UNION ALL SELECT 1, 'w', null, null, 'w'DECLARE @STR NVARCHAR(MAX) SET @STR=N'' SELECT @STR=@STR+N'+ISNULL((SELECT TOP 1 N'','+NAME+N''' FROM TB WHERE '+NAME+' IS NOT NULL),'''')' FROM syscolumns WHERE ID=OBJECT_ID('TB') SET @STR=N'DECLARE @STR NVARCHAR(MAX) '+ N'SELECT @STR='+STUFF(@STR, 1, 1, '')+ N'SET @STR=STUFF(@STR, 1, 1, '''')'+ N'EXEC(''SELECT ''+@STR+'' FROM TB'')' EXEC(@STR)DROP TABLE TB /* 列1 列2 列3 列5 ----------- ---- ----------- ---- 1 w NULL w 1 w 2 s 1 w NULL w 1 NULL NULL fg 1 w NULL w(5 row(s) affected) */
INSERT TB
SELECT 1, 'w', null, null, 'w' UNION ALL
SELECT 1, 'w', 2, null, 's' UNION ALL
SELECT 1, 'w', null, null, 'w' UNION ALL
SELECT 1, null, null, null, 'fg' UNION ALL
SELECT 1, 'w', null, null, 'w'DECLARE @STR VARCHAR(8000)
SET @STR=''
SET @STR=ISNULL((SELECT TOP 1 ',[列1]' FROM TB WHERE [列1] IS NOT NULL),'')+
+ISNULL((SELECT TOP 1 ',[列2]' FROM TB WHERE [列2] IS NOT NULL),'')+
+ISNULL((SELECT TOP 1 ',[列3]' FROM TB WHERE [列3] IS NOT NULL),'')+
+ISNULL((SELECT TOP 1 ',[列4]' FROM TB WHERE [列4] IS NOT NULL),'')+
+ISNULL((SELECT TOP 1 ',[列5]' FROM TB WHERE [列5] IS NOT NULL),'')SET @STR=STUFF(@STR, 1, 1, '')EXEC('SELECT '+@STR+' FROM TB')DROP TABLE TB
/*
列1 列2 列3 列5
----------- ---- ----------- ----
1 w NULL w
1 w 2 s
1 w NULL w
1 NULL NULL fg
1 w NULL w*/
INSERT TB
SELECT 1, 'w', null, null, 'w' UNION ALL
SELECT 1, 'w', 2, null, 's' UNION ALL
SELECT 1, 'w', null, null, 'w' UNION ALL
SELECT 1, null, null, null, 'fg' UNION ALL
SELECT 1, 'w', null, null, 'w'DECLARE @STR NVARCHAR(MAX)
SET @STR=N''
SELECT @STR=@STR+N'+ISNULL((SELECT TOP 1 N'','+NAME+N''' FROM TB WHERE '+NAME+' IS NOT NULL),'''')'
FROM syscolumns
WHERE ID=OBJECT_ID('TB')
SET @STR=N'DECLARE @STR NVARCHAR(MAX) '+
N'SELECT @STR='+STUFF(@STR, 1, 1, '')+
N'SET @STR=STUFF(@STR, 1, 1, '''')'+
N'EXEC(''SELECT ''+@STR+'' FROM TB'')'
EXEC(@STR)DROP TABLE TB
/*
列1 列2 列3 列5
----------- ---- ----------- ----
1 w NULL w
1 w 2 s
1 w NULL w
1 NULL NULL fg
1 w NULL w(5 row(s) affected)
*/
是sql2005的语法
况且 @STR的长度如果超过max,就不行了,还是谢谢你了我用游标解决了,谢谢大家