我有一个表格，想实现以下效果

Question

select 'yfgz'+bmbh+'0000'+cast(yfgz as varcahr) from 表
union select 'ykgz'+bmbh+'0000'+cast(ykgz as varcahr) from 表
union select 'sfgz'+bmbh+'0000'+cast(sfgz as varcahr) from 表

Answer 1

CREATE TABLE HEHE
(
   BMBH CHAR(4),
   YFGZ MONEY,
   YKGZ MONEY,
   SFGZ MONEY
)
GO
INSERT INTO HEHE VALUES('0001',$1000.00,$50.00,$950.00)
INSERT INTO HEHE VALUES('0001',$2052.00,$52.00,$2000.00)
GOSELECT COL_NAME(OBJECT_ID('HEHE'), 2)+BMBH
+replicate('0',10-len(replace(cast(yfgz as varchar),'.','')))
+ replace(cast(yfgz as varchar),'.','')  from HEHE
union
SELECT COL_NAME(OBJECT_ID('HEHE'), 3)+BMBH
+replicate('0',10-len(replace(cast(ykgz as varchar),'.','')))
+ replace(cast(ykgz as varchar),'.','')  from HEHE
union
SELECT COL_NAME(OBJECT_ID('HEHE'), 4)+BMBH
+replicate('0',10-len(replace(cast(sfgz as varchar),'.','')))
+ replace(cast(sfgz as varchar),'.','')  from HEHEGO
DROP TABLE HEHE
/*结果集
SFGZ00010000095000
SFGZ00010000200000
YFGZ00010000100000
YFGZ00010000205200
YKGZ00010000005000
YKGZ00010000005200*/

Answer 2

Sorry 测试数据写错了 重复了
改为
INSERT INTO HEHE VALUES('0001',$1000.00,$50.00,$950.00)
INSERT INTO HEHE VALUES('0002',$2052.00,$52.00,$2000.00)
结果为
SFGZ00010000095000
SFGZ00020000200000
YFGZ00010000100000
YFGZ00020000205200
YKGZ00010000005000
YKGZ00020000005200

Answer 3

[create] table t 
(bmbh varchar(20),yfgz decimal(8,2),ykgz decimal(8,2),sfgz decimal(8,2))insert t
select '0001',1000.00,50.00,950.00 [union] 
select '0002',2052.00,52.00,2000.00declare @s varchar(2000)
set @s=''
select @s = @s + 'select '''+name+'''+bmbh+(case len(replace(cast('+quotename(name)+'as varchar),''.'',''''))
when 1 then ''000000000''
when 2 then ''00000000''
when 3 then ''0000000''
when 4 then ''000000''
when 5 then ''00000''
when 6 then ''0000'' end)
+replace(cast('+quotename(name)+' as varchar),''.'','''') from t union '
from syscolumns where [id]=object_id(N't') and name<>'bmbh'
select @s = reverse(stuff(reverse(RTRIM(@s)),1,5,''))
exec(@s)---------------------------------------------------------------
sfgz00010000095000
sfgz00020000200000
yfgz00010000100000
yfgz00020000205200
ykgz00010000005000
ykgz00020000005200

Answer 4

娟娟，好方法啊！:)把我的修改一下！declare @s varchar(2000)
set @s=''
select @s = @s + 'select '''+name+'''+bmbh+replicate(''0'',10-len(replace(cast('+quotename(name)+' as varchar),''.'','''')))
+replace(cast('+quotename(name)+' as varchar),''.'','''') from t union '
from syscolumns where [id]=object_id(N't') and name<>'bmbh'
select @s = reverse(stuff(reverse(RTRIM(@s)),1,5,''))
exec(@s)真是的要简单多了！Thanks! ：）