效果
SELECT ROW_NUMBER() OVER (ORDER BY IncomeAmount) AS Idx, * FROM DailyIncome
;WITH CET1 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY IncomeAmount) AS Idx, * FROM DailyIncome
),
CET2 AS
(
SELECT ceiling(MAX(idx)/4.0) AS Cnt FROM CET1--如果有28条,这里的值就是7,且只有一行
)
SELECT
a.VendorId as VendorId1,a.IncomeDay AS IncomeDay1,a.IncomeAmount AS IncomeAmount1,--1-7
b.VendorId as VendorId2,b.IncomeDay AS IncomeDay2,b.IncomeAmount AS IncomeAmount2,--8-14
c.VendorId as VendorId3,c.IncomeDay AS IncomeDay3,c.IncomeAmount AS IncomeAmount3,--15-21
d.VendorId as VendorId4,d.IncomeDay AS IncomeDay4,d.IncomeAmount AS IncomeAmount4--22-28
FROM
(
SELECT * FROM CET1 a WHERE IDX<=(SELECT cnt FROM CET2 )
) a
LEFT JOIN
(
SELECT * FROM CET1 a --WHERE IDX
) b
ON a.idx=b.idx-(SELECT cnt FROM CET2 )
LEFT JOIN
(
SELECT * FROM CET1 a --WHERE IDX
) c
ON b.idx=c.idx-(SELECT cnt FROM CET2)
LEFT JOIN
(
SELECT * FROM CET1 a --WHERE IDX
) d
ON c.idx=d.idx-(SELECT cnt FROM CET2)