① sql行列轉換(sql行列轉換最簡單的方法)
很遺憾賀陸行列之間不是那麼隨便說轉換就轉換的
行描述的是一個對象列只是對象的一個屬性禪悔頃
java里說的是
萬物皆對象
只要是對象就可以吧特徵抽象成一個類
這就對應於資料庫的表
膚淺的說
表就相當於一個類
比如人這一類有手屬性,腳屬性,頭屬性....就不多列舉了
他們構成了一個人（人類表的一行）而N多擁有相同特徵的人就組成了人類（人類這個表）
樓主你現在要做的事情無異於要把人給肢解要把每一個人的手或腳或其他單一部件代替某一個人的全部部件(當做屬性)
說簡單一點就是樓主你要的效果就是讓人類表的美一個人都不同而且不同的方式還很奇怪一個前敏人只有手一個又只有腳一直有隻有頭.....最後整的相同特徵全無
這完全不符合歸為一類化為一個表的初衷嘛
不說能不能化行為列
就算可以換行成列那又有什麼意思呢？
完全沒有意義嘛
而且在實際開發中也不會有化行成列的需求
② SQL行轉列應用的動態實現方式
SQL行轉列的需求，在項目中還是經常可見的，尤其報表類的應用，更是非常廣泛！上期我們講了SQL行轉列的靜態實現方式，本期搞一下行轉列的動態實現方案，解決方案並不唯一，這里採用存儲過程的實現方式！
接下來我們詳細講解下SQL動態行轉列的實現步驟：
這里還是老套路，IT編程人入門的經典學生選課表系列，學生表、課程表、成績表！就拿這套耳熟能詳的表結構進行講解！
插入模擬的數據，用於動態行轉案例的使用！
這一步相對還是比較重要，畢竟我們要在一個靜態的行轉列基礎之上，構建動態的行轉列應用，課程數據會有動態變化，學生也會選擇新開的課程，這樣靜態模式勢必不會有效，但參照靜態模板，去開發動態的模式，則更加有參照性！
通過測試，數據效果沒有問題，正是我們期待的樣子！
動態行轉列無疑需要使用SQL編程的技術，動態的遞歸課程名稱，這樣才可以一勞永逸的解決問題！
先編寫動態的SQL腳本：
測試結果與靜態SQL完全一致，看來問題已經解決，接下來就是優化的問題了！
將上述的動態腳本封裝成存儲過程，第一可以盡量地提升查詢效率，第二方便代碼段的調用！
封裝完存儲過程，我們再執行一下，看看結果！果然沒有任何問題，與預期完全一致！
這時候我們更改一下數據，課程表中新增物理、化學兩門課程，諾克薩斯之手分別選擇了兩門課程，蓋倫僅僅選擇了化學，武器大師逃學，倆門課都沒有選擇。
數據改變之後，我們繼續測試一下，再次執行我們編寫好的存儲過程，結果非常完美，隨著數據的變化，查詢的結果集也是對應的變化，非常NICE，大功告成了！
連續倆篇的文章更新，SQL行轉列在項目中的應用都已經涵蓋了。即將步入年底了，肯定有很多小夥伴被客戶、領導追著搞各種報表，希望對小夥伴們有些許的幫助。#學編程# #互聯網# #IT#
③ sql 如何動態多行轉多列
使用存儲過程。
方法如下：
1、先查詢出表裡面的列名稱
2、行列轉換
舉例
1、查詢student欄位名
SELECT
a.colorder as _colOrder, --欄位序號
a.name as _colName --欄位名
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
where d.name='student' --所要查詢的表
order by a.id,a.colorder
2、行列轉換
SELECT
MAX (CASE WHEN SetupID = 1 THEN SetupName ELSE NULL END ) AS [1],
MAX (CASE WHEN SetupID = 2 THEN SetupName ELSE NULL END ) AS [2],
MAX (CASE WHEN SetupID = 3 THEN SetupName ELSE NULL END ) AS [3],
MAX (CASE WHEN SetupID = 4 THEN SetupName ELSE NULL END ) AS [4]
......
FROM table
④ sql動態多行轉列，PIVOT怎麼能轉兩列
樓上那個用過sqlserver嗎？
PIVOT是支持的！！！！！
不過看你的語法，肯定報錯。PIVOT最好基於SELECT * 並且先把全部需求的欄位轉換未列：
select * from
(select CONVERT(varchar(100),年月)+'受理' 欄位,sum(受理數量) 數量 from
表a
GROUP BY 年月
UNION ALL
select CONVERT(varchar(100),年月)+'辦結'欄位,sum(辦結數量) 數量 from
表a
GROUP BY 年月)v
PIVOT ( MAX(v.數量) FOR v.欄位 IN ( [2019-01受理],
[2019-02受理],
[2019-03受理],
[2019-04受理],
[2019-01辦結],
[2019-02辦結],
[2019-03辦結],
[2019-04辦結] )) 網路知道回答用
⑤ sql 2000 結合兩張表「動態」實現 行轉列
第一步,sql拼接：
select'sum(casewhenarea='''||area||'''then1else0end)'asrows
fromtable
然後取得上面結果的記錄集,組合拼接 進行第二步，示例如下：
selectbDate，sum(casewhenarea='保養一區'then1else0end)as保養一區,
sum(casewhenarea='保養二區'then1else0end)as保養二區,
sum(casewhenarea='保養三區'then1else0end)as保養三區,
sum(casewhenarea='保養四區'then1else0end)as保養四區,
sum(casewhenarea='保養五區'then1else0end)as保養五區
fromtable
groupbybDate
⑥ SQL 行列轉換
行列轉換等經典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')
⑦ sql行列轉換
--測試環境MSSQL2008
--先創建一個表值函數,負責字元串拆分
CreateFunction[dbo].[f_split1](@SourceSqlVarchar(8000),@StrSeprateVarchar(2))
Returns@tempTable(idIntIdentity(1,1),colVarchar(100))
As
begin
Declare@chasVarchar(100)
Set@SourceSql=@SourceSql+@StrSeprate
While(@SourceSql<>'')
Begin
Set@ch=left(@SourceSql,Charindex(@StrSeprate,@SourceSql,1)-1)
Insert@temp(col)Values(@ch)
Set@SourceSql=Stuff(@SourceSql,1,Charindex(@StrSeprate,@SourceSql,1),'')
End
Return
End
go
--建表
createtable#table(namenvarchar(500),valuenvarchar(max))
--插入數據
insertinto#tablevalues('aaa','45,44,44,44,43,43,43,43,43,43')
insertinto#tablevalues('bbb','45,44,44,44,44,44,44,44,44,43')
insertinto#tablevalues('ccc','13,12,12,12,4')
--由於N不固定,下面就動態生成SQL
Declare@Iint
Declare@WVarchar(1000)=''
Declare@SVarchar(4000)=''
Declare@SqlVarchar(8000)=''
select@I=max(len(value)-len(replace(value,',','')))from#table
While@I>0
Begin
Set@W=',['+Convert(Varchar(10),@I)+']'+@W
Set@S=',['+Convert(Varchar(10),@I)+']AsTop'+Convert(Varchar(10),@I)+@S
Set@I=@I-1
End
Set@Sql='
selectname'+@S+'from#table
outerapplydbo.f_split1(value,'','')
pivot
(
max(col)
For
idin('+stuff(@W,1,1,'')+')
)p
orderbyname'
--執行
Exec(@sql)
⑧ SQL 行列轉換
前段時間有人問了一個問題，就是SQL的行列轉換，當時有點懵沒答上來，後來細細想一想，其實最近的一個項目就已經用到了。
基礎數據如下：
要求根據當年的月份去統計出每一個ID的匯總金額。就是把行中的月切換到列中。
最後效果如下：
參考資料： https://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx#%E5%A4%8D%E6%9D%82%20PIVOT%20%E7%A4%BA%E4%BE%8B
帶批註的 PIVOT 語法。
如果聚合函數與 PIVOT 一起使用，則計算聚合時將不考慮出現在值列中的任何空值。
新建兩張表
查詢結果
--UNPIVOT 函數
--單純用SQL 處理 ,原理很簡單，就是對每個需要置換的列單獨處理出來
最終效果：
--用上面的結果新建表
--PIVOT
-- SQL直接處理，先用CASE 語句將每行處理出來，然後在用聚合去處理合並相同ID的行。
⑨ sql動態實現行列轉換
題：假設有張學生成績表(tb)如下:
姓名 課程 分數
張三 語文 74
張三 數學 83
張三 物理 93
李四 語文 74
李四 數學 84
李四 物理 94
想變成(得到如下結果)：
姓名 語文 數學 物理
---- ---- ---- ----
李四 74 84 94
張三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 課程 varchar(10) , 分數 int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 靜態SQL,指課程只有語文、數學、物理這三門課程。(以下同)
select 姓名 as 姓名 ,
max(case 課程 when '語文' then 分數 else 0 end) 語文,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '物理' then 分數 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 動態SQL,指課程不止語文、數學、物理這三門課程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)