【轉】SQL行列轉換

標籤：blog   http   ar   io   sp   for   on   資料   div   

原文地址：http://blog.csdn.net/kiki113/article/details/4105929 行列轉換等經典SQL語句

1.--行列轉換

原表:   姓名     科目   成績
           張三     語文    80
           張三     數學    90
           張三     物理    85
           李四     語文    85
           李四     物理    82
           李四     英語    90
           李四     政治    70
           王五     英語    90

轉換後的表：  姓名       數學    物理     英語    語文    政治 
                       李四         0         82        90      85       70
                       王五         0          0         90       0         0
                       張三        90        85         0       80        0

執行個體:
create table cj  --建立表cj
(
    ID       Int IDENTITY (1,1)     not null, --建立列ID,並且每次新增一條記錄就會加1
    Name     Varchar(50),   
    Subject  Varchar(50),
    Result   Int,  
    primary key (ID)      --定義ID為表cj的主鍵      
);
--Truncate table cj
--Select * from cj
Insert into cj
Select ‘張三‘,‘語文‘,80 union all 
Select ‘張三‘,‘數學‘,90 union all
Select ‘張三‘,‘物理‘,85 union all
Select ‘李四‘,‘語文‘,85 union all
Select ‘李四‘,‘物理‘,82 union all
Select ‘李四‘,‘英語‘,90 union all
Select ‘李四‘,‘政治‘,70 union all
Select ‘王五‘,‘英語‘,90
--行列轉換
Declare @sql varchar(8000)
Set @sql = ‘Select Name as 姓名‘
Select @sql = @sql + ‘,sum(case Subject when ‘‘‘+Subject+‘‘‘ then Result else 0 end) [‘+Subject+‘]‘
from (select distinct Subject from cj) as cj  --把所有唯一的科目的名稱都列舉出來
Select @sql = @sql+‘ from cj group by name‘
Exec (@sql)

2. 行列轉換--合并
原表:   班級    學號     
            1          1  
            1          2
            1          3
            2          1
            2          2
            3          1
轉換後的表:  班級  學號            
                       1   1,2,3
                       2   1,2
                       3   1  

執行個體:
Create table ClassNo  --建立表ClassNo
(
    ID Int IDENTITY(1,1)  not null,  --建立列ID,並且每次新增一條記錄就會加1
    Class  Varchar(50),    --班級列
    Number Varchar(50),    --學號列
    Primary Key(ID)        --定義ID為表ClassNo的主鍵
);
--Truncate Table ClassNo
--Select * from ClassNo
Insert Into ClassNo
Select 1,1 Union all
Select 1,2 Union all
Select 1,3 Union all
Select 2,1 Union all
Select 2,2 Union all
Select 3,1

建立一個合并的函數
--Drop Function KFReturn
Create Function KFReturn(@Class Varchar(50))
Returns Varchar(8000)
as 
Begin
Declare @str Varchar(8000)
Set @str = ‘‘
Select @str = @str + cast(Number as Varchar(50))  + ‘,‘ from ClassNo Where Class = @Class 
Set @str = SubString(@str,1,len(@str)-1)
Return(@str)
End

--調用自訂函數得到結果
Select Distinct Class,dbo.KFReturn(Class) From ClassNo

3:列轉行
--Drop Table ColumnToRow
Create table ColumnToRow
(
   ID Int IDENTITY(1,1)  not null,  --建立列ID,並且每次新增一條記錄就會加1
   a  int,
   b  int,
   c  int,
   d  int,
   e  int,
   f  int,
   g  int,
   h  int,
   Primary Key(ID)        --定義ID為表ColumnToRow的主鍵      
);
--Truncate Table ColumnToRow 
--Select * from ColumnToRow
Insert Into ColumnToRow 
Select 15,9,1,0,1,2,4,2 Union all
Select 22,34,44,5,6,7,8,7 Union all
Select 33,44,55,66,77,88,99,12

Declare @sql Varchar(8000)
Set @sql = ‘‘
Select @sql = @sql + rtrim(name) + ‘ from ColumnToRow union all Select ‘ from SysColumns Where id = object_id(‘ColumnToRow‘)
Set @sql = SubString(@sql,1,len(@sql)-70)
--70的長度就是這個字串‘from ColumnToRow union all Select ID from ColumnToRow union all Select ‘,因為它會把ID這一列的值也算進去，所以要把它截掉
Exec (‘Select ‘ + @sql + ‘ from ColumnToRow‘)

4. 如何取得一個資料表的所有列名
方法如下：先從sysobjects系統資料表中取得資料表的systemid,然後再syscolumns表中取得該資料表的所有列名。
SQL語句如下：
Declare @objid int,@objname char(40)
set @objname = ‘ColumnToRow‘
--第1種方法
select @objid = id from sysobjects where id = object_id(@objname)
select ‘Column_name‘ = name from syscolumns where id = @objid order by colid
--或也可以寫成
select name as ‘Column_name‘ from syscolumns where id = @objid order by colid
--第2種方法:
Select name as ‘Column_Name‘ from SysColumns where id = object_id(@objname) Order by colid

5. 通過SQL語句來更改使用者的密碼
修改別人的,需要sysadmin role 
Exec Sp_password ‘原始密碼‘,‘更改後密碼‘,‘帳號‘
Exec sp_password null,ok,sa

6. 怎麼判斷出一個表的哪些欄位不允許為空白？
Declare @objname Varchar(50)
set @objname = ‘ColumnToRow‘
Select Column_Name from information_schema.Columns where is_nullable = ‘No‘ and Table_Name = @objname

7. 如何在資料庫裡找到含有相同欄位的表？
a. 查已知列名的情況
Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.id
and b.type = ‘U‘ and a.name = ‘您要尋找的欄位名‘
b. 未知列名查所有在不同表出現過的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s 
Where s1.id = s.id and s.Type = ‘U‘ and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)

8.查詢第N行資料
假設id是主鍵： 
select * 
from (select top N * from 表) aa 
where not exists(select 1 from (select top N-1 * from 表) bb where aa.id=bb.id)

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))

【轉】SQL行列轉換