SQL ½«ÁÐת»»ÎªÐУ¬Ï൱ÓÚ½«±í½á¹¹Ðýת90¶È
	
    
    
	½«ÁÐת»»ÎªÐУ¬Ï൱ÓÚ½«±í½á¹¹Ðýת90¶È
T_Student ±í
Stud_ID
Sex
Name
1
ÄÐ
Tom
2
Å®
Anne
3
ÄÐ
Jack
 
Ö´ÐÐ: Exec proColumnToRow ’T_Student’,’Name’,’ New_ID’ 
 
ת»»ºóµÄ±í
 
New_ID
Tom
Anne
Jack
Stud_ID
1
2
3
Sex
ÄÐ
Å®
ÄÐ
 
--½«ÁÐת»»ÎªÐÐ
create proc proColumnToRow
       @tbname sysname, --Òª´¦ÀíµÄ±íÃû
       @fdname sysname, --ijһÁеÄֵת»»ÎªÐ±íµÄÁÐ
       @new_fdname sysname='' --½«Ô´±íµÄÁÐת»»ÎªÐÂÁеÄÖµ¡£@new_fdnameΪÐÂÁеÄÁÐÃû
as
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 + '=''' + case isnull(@new_fdname , '') when '' then ''
       else @new_fdname + '=' end + '''''' + name + '''''''',
       @s3 = @s3 + 'select 
 @' + @i + '=@' + @i + '+'',['' + cast(' + @fdname + 
       ' as varchar)+'']=''''''+cast([' + name + '] as varchar)+'''''''' from [' + @tbname + ']',
       @s4 = @s4 + ',@' + @i + '=''select 
 ''+@' + @i,
       @s5 = @s5 + '+'' union all ''+@' + @i,
       @i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname
 
select 
 @s1=substring(@s1,2,8000),
       @s2=substring(@s2,2,8000),
       @s4=substring(@s4,2,8000),
       @s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'selec
    
     
	
	
    
    
	Ïà¹ØÎĵµ£º
        
    
    ÔÚSQL Server 2008Ï£¬ÍøÉÏÒÔǰÁ÷ÐеĽضÏÈÕÖ¾²¢ÊÕËõÎļþµÄ·½·¨²»ÊʺÏÁË£¬±¨Óï·¨´íÎó¡£ 
ͬʱÒòΪÎÒµÄÊý¾Ý¿âÈÕÖ¾Ôö³¤·Ç³£¿ì£¬´ó¸ÅÿÌìÔÚ1GÒÔÉÏ£¬Òò´Ë±ØÐë¶ÔÆä½øÐÐÊÕËõ¡£ 
¾¹ýÔÚϵͳÉϵIJâÊÔ£¬¸Ð¾õÓÃSQL SERVERµÄά»¤¼Æ»®¸ü¿ÉÐУ¬ÏÖ½«²Ù×÷·½Ê½¼ò½éÈçÏ£º 
×¢£ºÏÂÃæµÄ˵Ã÷ÖÐÒ²Ìáµ½ÁËʹÓòîÒ챸·Ý£¬ÒòΪÊý¾Ý¿âÎļþÔö³¤Ò²·Ç ......
	
    
        
    
    1, ʲôÊÇͳ¼ÆÐÅÏ¢  
    ÒÔÏÂÊǹٷ½µÄ¶Ôͳ¼ÆÐÅÏ¢µÄÃèÊö£º
     °´ÕÕĬÈÏÉèÖã¬Èç¹û±íÖеÄijÁÐûÓÐË÷Òý£¬ÔòSQL Server»á×Ô¶¯Îª¸ÃÁд´½¨Í³¼Æ¡£È»ºó£¬²éѯÓÅ»¯Æ÷ÆÀ¹À¸ÃÁÐÖÐÊý¾Ý·Ö²¼·¶Î§µÄͳ¼ÆÐÅÏ¢£¬ÒÔÑ¡ÔñÒ»¸ö¸üΪÓÐЧµÄ²éѯ´¦Àí·½°¸¡£·Ö±æ×Ô¶¯´´½¨µÄͳ¼ÆºÜ¼òµ¥£¬ÔÚSQL Serve ......
	
    
        
    
    SQLÖÐWHERE×Ó¾äÖпÉÒÔÀûÓÃLIKEÀ´Æ¥ÅäÏàËÆµÄÊý¾ÝÏµ±Ê±Îҵõ½µÄ½áÂÛÊÇ¿ÉÒÔʹÓÃ"%"À´Æ¥Åä×Ö·û´®£¬ÔÚSPUFIÖÐʹÓ㬵ÄÈ·¿ÉÒԵõ½×Ö·û´®Æ¥ÅäµÄЧ¹û¡£È磺
    SELECT * from TEST
          WHERE TESTID LIKE "CZX%";
    ÉÏÃæÕâ¾äÔÚSPUFIÖÐÌ ......
	
    
        
    
    ---ÉϸöÔÂÔ³õµÚÒ»Ìì
select CONVERT(varchar(12) , DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,getdate())),0), 112 ) 
---ÉϸöÔÂÔÂÄ©×îºóÒ»Ìì
select CONVERT(varchar(12),dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()),0)), 112 )
1. ÏÔʾ±¾ÔµÚÒ»Ìì 
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select co ......