看了这个需求同事表示很麻烦，列是动态的，多一天多一个订单类型就多一列，我看了下，这不是sql行转列的节奏么。这个还真没搞过。大家pp这个设计是否合理：
1、模拟数据
2、确定思路，网上看了下动态sql实现，自己想了想好像还需要祭出游标神器，一番调式成功了。没有性能问题啊，呵呵，交货了。跟同事说了下思路，他表示看不惯游标里面嵌套游标。好吧，你自己实现吧，偶是有经验的运用游标，不是乱用，爱用不用。呵呵...
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
USE [SK_WMS_DB]GO/****** Object: StoredProcedure [dbo].[sp_warehouse_sum_byOrderDate] Script Date: 01/15/2014 17:16:50 ******/
SET
ANSI_NULLS ON
GOSET
QUOTED_IDENTIFIER ON
GOALTER
PROC [dbo].[sp_warehouse_sum_byOrderDate]
ASBEGIN declare
@sql varchar(max)
declare
@v_order_flag varchar(10)
declare
@v_dt Date
declare
@v_report_columnName varchar(60)
--订单类型临时表
SELECT
distinct
[OrderFlag] INTO
#OrderFlag_Info FROM
[SK_WMS_DB].[dbo].[Table_Report_Test]
ORDER
BY
[OrderFlag] DESC
--订单日期临时表
SELECT
distinct
OrderDT INTO
#OrderDT_Info FROM
[SK_WMS_DB].[dbo].[Table_Report_Test]
ORDER
BY
OrderDT
--表头临时表
SELECT
CAST(NULL
AS
DATE) ORDER_DT,
CAST
(‘‘
AS
varchar(60)) AS
REP_COL_NAME,
CAST
(‘‘
AS
varchar(10)) AS
OrderFlag
INTO
#REPORT_COLUMN
declare
cur_order_flag cursor
for
select
OrderFlag from
#OrderFlag_Info
open
cur_order_flag
fetch
next
from
cur_order_flag into
@v_order_flag
--根据订单类型生成列名
while @@FETCH_STATUS = 0
begin
declare
cur_OrderDT cursor
for
select
OrderDT from
#OrderDT_Info
open
cur_OrderDT
fetch
next
from
cur_OrderDT into
@v_dt
--日期+订单类型
while @@FETCH_STATUS = 0
begin
INSERT
INTO
#REPORT_COLUMN
select
@v_dt,
CAST(substring(CONVERT(char(10),@v_dt,102),6,5)+ @v_order_flag as
varchar(60)),
@v_order_flag
fetch
next
from
cur_OrderDT into
@v_dt
end close
cur_OrderDT
deallocate
cur_OrderDT
INSERT
INTO
#REPORT_COLUMN
select
‘1901-01-01‘,CAST(RTRIM(LTRIM(@v_order_flag))+‘小计‘
as
varchar(60)),@v_order_flag
fetch
next
from
cur_order_flag into
@v_order_flag
end
close
cur_order_flag
deallocate
cur_order_flag
declare
@colText varchar(max)
declare
cur_tbl cursor
for
SELECT
REP_COL_NAME FROM
#REPORT_COLUMN
WHERE
ORDER_DT IS
NOT
NULL
--返回结果临时表
SELECT
CAST
(‘‘
AS
VARCHAR(255)) AS
仓库名称,
CAST
(‘‘
AS
VARCHAR(255)) AS
库内区域名称,
CAST
(‘‘
AS
VARCHAR(255)) AS
物料号码,
CAST
(‘‘
AS
VARCHAR(255)) AS
品名,
CAST
(‘‘
AS
VARCHAR(255)) AS
包装
INTO
#Result_Report
--循环增加列
open
cur_tbl
fetch
next
from
cur_tbl into
@v_report_columnName
while @@FETCH_STATUS = 0
begin
SET
@colText = RTRIM(LTRIM(@v_report_columnName))
SET
@sql = ‘ALTER TABLE #Result_Report ADD [‘+@colText+‘] [numeric](18, 2) default 0‘
EXEC(@sql)
fetch
next
from
cur_tbl into
@v_report_columnName
end close
cur_tbl
deallocate
cur_tbl
--清空临时表记录
DELETE
FROM
#Result_Report
--开始准备数据,每个仓库循环一次
declare
@WareHouse varchar(60)
declare
@qty numeric(18,2)
declare
cur_data_fill
cursor
for
SELECT
distinct
[WareHouseName] FROM
[SK_WMS_DB].[dbo].[Table_Report_Test]
--填充记录
open
cur_data_fill
fetch
next
from
cur_data_fill into
@WareHouse
while @@FETCH_STATUS = 0
begin
insert
into
#Result_Report (仓库名称,库内区域名称,物料号码,品名,包装)
select
@WareHouse,‘‘
库内区域名称,‘‘
物料号码,‘‘
品名,‘‘
包装
declare
cur_sum_qty
cursor
for SELECT
REP_COL_NAME,ORDER_DT,OrderFlag FROM
#REPORT_COLUMN
WHERE
ORDER_DT IS
NOT
NULL open
cur_sum_qty
fetch
next
from
cur_sum_qty into
@v_report_columnName,@v_dt,@v_order_flag
while @@FETCH_STATUS = 0
begin
IF @v_dt <> ‘1901-01-01‘
SELECT
@qty = isnull(SUM(t.Qty),0) FROM
dbo.Table_Report_Test t
WHERE
t.WareHouseName = @WareHouse
AND
t.OrderDT = @v_dt
AND
t.OrderFlag = @v_order_flag
ELSE
SELECT
@qty = isnull(SUM(t.Qty),0) FROM
dbo.Table_Report_Test t
WHERE
t.WareHouseName = @WareHouse
AND
t.OrderFlag = @v_order_flag
SELECT
@sql = ‘UPDATE #Result_Report SET [‘+LTRIM(RTRIM(@v_report_columnName))+‘] = ‘+CAST(@qty AS
VARCHAR) +
‘ WHERE 仓库名称 = ‘‘‘+@WareHouse+‘‘‘‘
EXEC(@sql)
fetch
next
from
cur_sum_qty into
@v_report_columnName,@v_dt,@v_order_flag
end
close
cur_sum_qty
deallocate
cur_sum_qty
fetch
next
from
cur_data_fill into
@WareHouse
end close
cur_data_fill
deallocate
cur_data_fill
--返回结果集
SELECT
* FROM
#Result_Report
END |
3、成果：
写在这里自己备忘,也给同行看看还有更好的方法不,可以随便喷...