SQL Serverでの行列変換Pivot UnPivot
35511 ワード
PIVOTはカラム値をカラム名に回転させるために使用され、SQL Server 2000では集約関数とCASE文を組み合わせて実現することができる
PIVOTの一般的な構文は、PIVOT(集約関数(列)FOR列in(…))AS P
完全な構文:
table_source
PIVOT(
集約関数(value_column)
FOR pivot_column
IN(
)
UNPIVOTは、列名を列値(すなわち列転行)に変換するために使用され、SQL Server 2000ではUNIONで実現することができる
完全な構文:
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(
)
注意:PIVOT、UNPIVOTはSQL Server 2005の構文です.データベースの互換性レベルを変更するには、データベースのプロパティ->オプション->互換性レベルを90に変更します.
、
1、
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb( varchar(10), varchar(10), int)
insertintotbvalues(' ',' ',74)
insertintotbvalues(' ',' ',83)
insertintotbvalues(' ',' ',93)
insertintotbvalues(' ',' ',74)
insertintotbvalues(' ',' ',84)
insertintotbvalues(' ',' ',94)
go
select*fromtb
go
---------- ---------- -----------
74
83
93
74
84
94
2、 SQL Server 2000 SQL
--c
select ,
max(case when' 'then else0end) ,
max(case when' 'then else0end) ,
max(case when' 'then else0end)
fromtb
groupby
---------- ----------- ----------- -----------
74 84 94
74 83 93
3、 SQL Server 2000 SQL
--SQL SERVER 2000 SQL, 、 、 。( )
-- sql
declare@sqlvarchar(500)
set@sql='select '
select@sql=@sql+',max(case when '''+ +''' then else 0 end)['+ +']'
from(selectdistinct fromtb)a-- from tb group by ，
set@sql=@sql+' from tb group by '
exec(@sql)
-- isnull(),
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+' max(case when '''+ +''' then else 0 end) ['+ +']'
from(selectdistinct fromtb)asa
set@sql='select ,'+@sql+' from tb group by '
exec(@sql)
---------- ----------- ----------- -----------
84 94 74
83 93 74
4、 SQL Server 2005 SQL
select*fromtb pivot(max( )for in( , , ))a
5、 SQL Server 2005 SQL
-- stuff()
declare@sqlvarchar(8000)
set@sql='' -- @sql
select@sql=@sql+','+ fromtbgroupby --
set@sql=stuff(@sql,1,1,'')-- ','
set@sql='select * from tb pivot (max( ) for in ('+@sql+'))a'
exec(@sql)
-- isnull()
declare@sqlvarchar(8000)
–-
select@sql=isnull(@sql+',','')+ fromtbgroupby
set@sql='select * from tb pivot (max( ) for in ('+@sql+'))a'
exec(@sql)
、 、
1、 SQL Server 2000 SQL
--SQL SERVER 2000 SQL
select ,
max(case when' 'then else0end) ,
max(case when' 'then else0end) ,
max(case when' 'then else0end) ,
sum( ) ,
cast(avg( *1.0)asdecimal(18,2))
fromtb
groupby
---------- ----------- ----------- ----------- -----------
74 84 94 252 84.00
74 83 93 250 83.33
2、 SQL Server 2000 SQL
--SQL SERVER 2000 SQL
declare@sqlvarchar(500)
set@sql='select '
select@sql=@sql+',max(case when '''+ +''' then else 0 end)['+ +']'
from(selectdistinct fromtb)a
set@sql=@sql+',sum( ) ,cast(avg( *1.0) as decimal(18,2)) from tb group by '
exec(@sql)
3、 SQL Server 2005 SQL
selectm.*,n. ,n.
from
(select*fromtb pivot(max( )for in( , , ))a)m,
(select ,sum( ) ,cast(avg( *1.0)asdecimal(18,2))
fromtb
groupby )n
wherem. =n.
4、 SQL Server 2005 SQL
-- stuff()
--
declare@sqlvarchar(8000)
set@sql='' -- @sql
select@sql=@sql+','+ fromtbgroupby --
-- select @sql = @sql + ','+ from (select distinct from tb)a
set@sql=stuff(@sql,1,1,'')-- ','
set@sql='select m.* , n. ,n. from
(select * from (select * from tb) a pivot (max( ) for in ('+@sql+')) b) m ,
(select ,sum( ) , cast(avg( *1.0) as decimal(18,2)) from tb group by ) n
where m. = n. '
exec(@sql)
-- isnull()
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+ fromtbgroupby
set@sql='select m.* , n. ,n. from
(select * from (select * from tb) a pivot (max( ) for in ('+
@sql+')) b) m ,
(select ,sum( ) , cast(avg( *1.0) as decimal(18,2)) from tb group by ) n
where m. = n. '
exec(@sql)
、
1、
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb( varchar(10), int, int, int)
insertintotbvalues(' ',74,83,93)
insertintotbvalues(' ',74,84,94)
go
select*fromtb
go
---------- ----------- ----------- -----------
74 83 93
74 84 94
2、 SQL Server 2000 SQL
--SQL SERVER 2000 SQL。
select*from
(
select , =' ', = fromtb
unionall
select , =' ', = fromtb
unionall
select , =' ', = fromtb
) t
orderby ,case when' 'then1when' 'then2when' 'then3end
---------- ---- -----------
74
84
94
74
83
93
2、 SQL Server 2000 SQL
--SQL SERVER 2000 SQL。
-- 。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+' union all ','')+' select , [ ]='
+quotename(Name,'''')+' , [ ] = '+quotename(Name)+' from tb'
fromsyscolumns
whereName!=' 'andID=object_id('tb')-- tb，
orderbycolid
exec(@sql+' order by ')
go
3、 SQL Server 2005 SQL
--SQL SERVER 2005 SQL
select , , fromtb unpivot ( for in([ ],[ ],[ ])) t
4、 SQL Server 2005 SQL
--SQL SERVER 2005 SQL
declare@sqlnvarchar(4000)
select@sql=isnull(@sql+',','')+quotename(Name)
fromsyscolumns
whereID=object_id('tb')andNamenotin(' ')
orderbyColid
set@sql='select ,[ ],[ ] from tb unpivot ([ ] for [ ] in('+@sql+'))b'
exec(@sql)この文章は原作者の整理を経て得られたもので、転載を歓迎します.転載する時、本文の住所を加えてください.本明細書は、署名2.5中国大陸ライセンス契約に基づいて発行されます.ビジネス目的のために使用したり、使用したりしないでください.ただし、本明細書の署名張志濤を保持する必要があります(質問やライセンスに関する協議を含むリンクは、メッセージをくれを参照してください.