// drop table #table_name1 -- 建立一个临时表,把一个表的数据的所有列,一个一个地排成行
// create table #table_name1
// (
// id int ,
// Member_Name nvarchar ( 2000 ),
// pname nvarchar ( 2000 ),
// p1 int
// )
// declare @sql varchar ( 8000 )
// set @sql = ' insert #table_name1 select * from( '
// select @sql = @sql + ' select id,name,pname=( ''' + names + ''' ), ' + names
//+ ' from ppmf union all '
// from (
// SELECT c.name as names
// FROM syscolumns c INNER JOIN
// sysobjects o ON c.id = o.id
// WHERE (o.name = ' ppmf ' and colid > 2 )) aa
// select @sql = left ( @sql , len ( @sql ) - 9 ) + ' )a order by id,pname '
// print ( @sql )
// exec ( @sql )
// select * from #table_name1
// -- ------------------
// 1 :ppmf表结构
// id char
// name char
// p1 int
// p2 int
// p3 int
// p4 int
// 2 :数据
// id name p1 p2 p3 p4
// -- ------------------------------------------------------------------
// 1 a 10 30
// 2 b 30 50
// 3 c 100
// 排列后的#table_name1数据
// id Member_Name pname p1
// 1 a p1 10
// 1 a p2 NULL
// 1 a p3 30
// 1 a p4 NULL
// 2 b p1 NULL
// 2 b p2 30
// 2 b p3 NULL
// 2 b p4 50
// 3 c p1 100
// 3 c p2 NULL
// 3 c p3 NULL
// 3 c p4 NULL
-- --------------------------------------------------------------
create table #table_name1
(
id int IDENTITY ( 1 , 1 ),
p0 varchar ( 8000 ),
p1 float
)
declare @sql varchar ( 8000 )
set @sql = ' insert #table_name1 select * from( '
select @sql = @sql + ' select Ball_Pay=( ''' + names + ''' ), ' + names
+ ' from tb_Guess_Nums_Sum union all '
from (
SELECT c.name as names
FROM syscolumns c INNER JOIN
sysobjects o ON c.id = o.id
WHERE (o.name = ' tb_Guess_Nums_Sum ' and colid > 2 )) aa
select @sql = left ( @sql , len ( @sql ) - 9 ) + ' )a '
print ( @sql )
exec ( @sql )
delete from tb_Guess_Nums_Sum where id > 0
-- select * from #table_name1