try Select Max(Case (aa - 1) % 5 When 0 Then aa Else 0 End) As aa1, Max(Case (aa - 1) % 5 When 1 Then aa Else 0 End) As aa2, Max(Case (aa - 1) % 5 When 2 Then aa Else 0 End) As aa3, Max(Case (aa - 1) % 5 When 3 Then aa Else 0 End) As aa4, Max(Case (aa - 1) % 5 When 4 Then aa Else 0 End) As aa5 From 表 Group By (aa - 1) / 5
--創建測試環境 Create Table 表 (aa Int) --插入數據 Insert 表 Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10 Union All Select 11 Union All Select 12 Union All Select 13 GO --測試 Select Max(Case (aa - 1) % 5 When 0 Then aa Else Null End) As aa1, Max(Case (aa - 1) % 5 When 1 Then aa Else Null End) As aa2, Max(Case (aa - 1) % 5 When 2 Then aa Else Null End) As aa3, Max(Case (aa - 1) % 5 When 3 Then aa Else Null End) As aa4, Max(Case (aa - 1) % 5 When 4 Then aa Else Null End) As aa5 From 表 Group By (aa - 1) / 5 GO --刪除測試環境 Drop Table 表 --結果 /* aa1 aa2 aa3 aa4 aa5 1 2 3 4 5 6 7 8 9 10 11 12 13 NULL NULL */
declare @strsql varchar(8000) set @strsql='select '+ 。。就是相当于这样的写法
給你寫個動態的吧--創建測試環境 Create Table 表 (aa Int) --插入數據 Insert 表 Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10 Union All Select 11 Union All Select 12 Union All Select 13 GO --測試 Declare @S Varchar(8000) Select @S = '' Select @S = @S + ', Max(Case (aa - 1) % 5 When ' + Cast(I As Varchar) + ' Then aa Else Null End) As aa' + Cast(I + 1 As Varchar) From (Select Distinct (aa - 1) % 5 As I From 表) A Order By I Select @S = 'Select ' + Stuff(@S, 1, 1, '') + ' From 表 Group By (aa - 1) / 5' EXEC(@S) GO --刪除測試環境 Drop Table 表 --結果 /* aa1 aa2 aa3 aa4 aa5 1 2 3 4 5 6 7 8 9 10 11 12 13 NULL NULL */
Select
Max(Case (aa - 1) % 5 When 0 Then aa Else 0 End) As aa1,
Max(Case (aa - 1) % 5 When 1 Then aa Else 0 End) As aa2,
Max(Case (aa - 1) % 5 When 2 Then aa Else 0 End) As aa3,
Max(Case (aa - 1) % 5 When 3 Then aa Else 0 End) As aa4,
Max(Case (aa - 1) % 5 When 4 Then aa Else 0 End) As aa5
From
表
Group By
(aa - 1) / 5
Create Table 表
(aa Int)
--插入數據
Insert 表 Select 1
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10
Union All Select 11
Union All Select 12
Union All Select 13
GO
--測試
Select
Max(Case (aa - 1) % 5 When 0 Then aa Else Null End) As aa1,
Max(Case (aa - 1) % 5 When 1 Then aa Else Null End) As aa2,
Max(Case (aa - 1) % 5 When 2 Then aa Else Null End) As aa3,
Max(Case (aa - 1) % 5 When 3 Then aa Else Null End) As aa4,
Max(Case (aa - 1) % 5 When 4 Then aa Else Null End) As aa5
From
表
Group By
(aa - 1) / 5
GO
--刪除測試環境
Drop Table 表
--結果
/*
aa1 aa2 aa3 aa4 aa5
1 2 3 4 5
6 7 8 9 10
11 12 13 NULL NULL
*/
看来CASE 用处大大的
发现
CSDN上提到的问题
大多是让CASE解决的
学习了!
set @strsql='select '+ 。。就是相当于这样的写法
Create Table 表
(aa Int)
--插入數據
Insert 表 Select 1
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10
Union All Select 11
Union All Select 12
Union All Select 13
GO
--測試
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ', Max(Case (aa - 1) % 5 When ' + Cast(I As Varchar) + ' Then aa Else Null End) As aa' + Cast(I + 1 As Varchar)
From (Select Distinct (aa - 1) % 5 As I From 表) A Order By I
Select @S = 'Select ' + Stuff(@S, 1, 1, '') + ' From 表 Group By (aa - 1) / 5'
EXEC(@S)
GO
--刪除測試環境
Drop Table 表
--結果
/*
aa1 aa2 aa3 aa4 aa5
1 2 3 4 5
6 7 8 9 10
11 12 13 NULL NULL
*/