SQL語句精典收藏

1. 行列轉換
假設有張學產生績表(CJ)如下
Name    Subject     Result
張三    語文        80
張三    數學        90
張三    物理        85
李四    語文        85
李四    數學        92
李四    物理        82

想變成    
姓名   語文   數學   物理
張三   80     90     85
李四   85     92     82

解決如下
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
 from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)

另外在Access中還提供了TransForm來實現行列轉換
TRANSFORM count(Result) AS number
SELECT 姓名
FROM 學產生績表
GROUP BY 姓名
PIVOT Subject;

TransForm 用法如下:
=========================================================
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

TRANSFORM 語句可分為以下幾個部分：

部分            描述
aggfunction 在選定資料上運作的 SQL 合計函數。
selectstatement  SELECT 語句。
pivotfield 在查詢的結果集中建立欄位標題時用的欄位或運算式。
value1, value2 用來建立欄位標題的固定值。

說明
使用交叉資料表查詢來摘要資料時，從指定的欄位或運算式中選定值作為欄位標題，
這樣，可以用比選定查詢更緊湊的格式來觀察資料。
TRANSFORM 是可選的，但在使用它時，要作為  SQL 字串中的第一個語句。
它出現在 SELECT 語句（指定作為列名的欄位的）之前，還出現在 GROUP BY 子句
（指定行分組的）之前。可以有選擇地包含其它子句，例如 WHERE 子句，它指定附
加的選擇或排序條件。也可以將子查詢當作謂詞，特別是在叉表查詢的 WHERE 子句中。

pivotfield 返回的值被用作查詢結果集中的欄位標題。
例如，在交叉資料表查詢中，將根據銷售圖表按銷售月份建立 12 個列。
可以限制 pivotfield 用列在可選的 IN 子句中的固定值（value1, value2）來建立標題。
也可以用沒有資料存在的固定值來建立附加的列。
==================================================================================

2. 列行轉換
暫時保留

3. 行列轉換--加合并
有表A,
 id pid
 1   1
 1   2
 1   3
 2   1
 2   2
 3   1
如何化成表B:
 id pid
  1  1,2,3
  2  1,2
  3  1

建立一個合并的函數
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--調用自訂函數得到結果
select distinct id,dbo.fmerg(id) from 表A

4. 如何取得一個資料表的所有列名
方法如下：先從SYSTEMOBJECT系統資料表中取得資料表的SYSTEMID,然後再SYSCOLUMN表中取得該資料表的所有列名。
SQL語句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

是不是太簡單了？ 呵呵 不過經常用阿.

5. 通過SQL語句來更改使用者的密碼

修改別人的,需要sysadmin  role    
EXEC  sp_password  NULL,  'newpassword',  'User'

如果帳號為SA執行EXEC  sp_password  NULL,  'newpassword',  sa  

6.怎麼判斷出一個表的哪些欄位不允許為空白？

select  COLUMN_NAME  from  INFORMATION_SCHEMA.COLUMNS  where  IS_NULLABLE='NO'  and  TABLE_NAME=tablename  

7.如何在資料庫裡找到含有相同欄位的表？
a. 查已知列名的情況
SELECT  b.name  as  TableName,a.name  as  columnname  
From  syscolumns    a  INNER  JOIN    sysobjects  b    
ON  a.id=b.id    
AND  b.type='U'    
AND  a.name='你的欄位名字'  
b. 未知列名查所有在不同表出現過的列名
Select  o.name  As  tablename,s1.name  As  columnname  
From  syscolumns  s1,  sysobjects  o  
Where  s1.id  =  o.id  
   And  o.type  =  'U'  
   And  Exists  (  
       Select  1  From  syscolumns  s2    
       Where  s1.name  =  s2.name    
       And  s1.id  <>  s2.id  
       )
8.查詢第xxx行資料

假設id是主鍵：  
select  *  
from  (select  top  xxx  *  from  yourtable)  aa  
where  not  exists(select  1  from  (select  top  xxx-1  *  from  yourtable)  bb  where  aa.id=bb.id)
如果使用遊標也是可以的  
fetch  absolute  [number]  from  [cursor_name]  
行數為絕對行數

9.SQL Server日期計算
a. 一個月的第一天
SELECT  DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0)  
b. 本周的星期一
SELECT  DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0)
c. 一年的第一天
SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)  
d. 季度的第一天
SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0)  
e. 上個月的最後一天
SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0))  
f. 去年的最後一天
SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0))
g. 本月的最後一天
SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))  
h. 本月的第一個星期一
select  DATEADD(wk,  DATEDIFF(wk,0,                                                          
                               dateadd(dd,6-datepart(day,getdate()),getdate())        
                                                                                                 ),  0)      
i. 本年的最後一天
SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0))。