之前朋友找我,他项目遇到这么一个问题
他系统显示了 这样的一个图做箱柜管理,但是他客户要求不是这样,要如图二一般
图一
图二
于是我给他说做个一存储过程然后绑定,显示,至于其他数据可以和其他数据表关联显示
于是我就试一下动手,我发现有点意思。用了一个笨方法,于是记录一下。
建立一个基础表
CREATE TABLE [dbo].[tbl_def](
[fldrow] [INT] NULL,
[fldcolum] [INT] NULL,
[fldBox] [INT] NULL,
[fldid] [INT] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tbl_def] PRIMARY KEY CLUSTERED
(
[fldid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
存储过程:
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE @licol INT;
--SET @licol=2--一个柜子有几列
DECLARE @row INT;
DECLARE @col INT;
DECLARE @box INT;
DECLARE @Fcol INT;
SELECT @row = fldrow ,
@col =fldBox*fldcolum,--@licol,--fldcolum ,
@box = fldBox
FROM dbo.tbl_def
WHERE fldid = 1;
SET @Fcol = @col / @box;
DECLARE @i INT;
DECLARE @j INT;
SET @i = 1;
SET @j = 1;
DECLARE @Query NVARCHAR(MAX);
SET @Query = ' DECLARE @temp TABLE ( id [nvarchar](50) ,';
DECLARE @iQuery NVARCHAR(MAX);
SET @iQuery = ' insert into @temp( id, ';
WHILE @i <= @col
BEGIN
IF @i != @col
BEGIN
SET @Query = @Query + 'col' + CONVERT(NVARCHAR(10), @i)
+ ' [nvarchar](50) ,';
SET @iQuery = @iQuery + 'col'
+ CONVERT(NVARCHAR(10), @i) + ' ,';
END;
ELSE
BEGIN
SET @Query = @Query + 'col' + CONVERT(NVARCHAR(10), @i)
+ ' [nvarchar](50) );';
SET @iQuery = @iQuery + 'col'
+ CONVERT(NVARCHAR(10), @i) + ' )';
END;
SET @i = @i + 1;
END;
DECLARE @j1Query NVARCHAR(MAX);
SET @j1Query = ' select 1,';
SET @i = 1;
DECLARE @tempf INT;
SET @tempf = 1;
--PRINT CONVERT(NVARCHAR(10),@row)
WHILE @i <= @row
BEGIN
SET @j = 1;
WHILE @j <= @col
BEGIN
-- PRINT CONVERT(NVARCHAR(10),@i) +'ddd '+CONVERT(NVARCHAR(10),@j)+'dddd '+CONVERT(NVARCHAR(10),@col)
IF @j != @col
BEGIN
PRINT 'i '+ CONVERT(NVARCHAR(10),@i)
+' j '+CONVERT(NVARCHAR(10),@j)
+' Row '+CONVERT(NVARCHAR(10),@row)
+' Column '+CONVERT(NVARCHAR(10),@col)
+' 求余 '+CONVERT(NVARCHAR(10),@Fcol)
+' 求余1 '+CONVERT(NVARCHAR(10),@col % @Fcol)
SET @j1Query = @j1Query + ''''
+ CONVERT(NVARCHAR(10), CEILING( CAST(@j*1.0/@Fcol AS FLOAT) )) + '-'
+ CONVERT(NVARCHAR(10), @i + @row
* (@tempf-1)) + ''' ,';
PRINT @j1Query --test
END;
ELSE
BEGIN
SET @j1Query = @j1Query + ''''
+ CONVERT(NVARCHAR(10), CEILING( CAST(@j*1.0/@Fcol AS FLOAT) )) + '-'
+ CONVERT(NVARCHAR(10), @i + @row
* (@tempf-1)) + ''' ';
PRINT @j1Query --test
IF @i = @row AND @j = @col
BEGIN
SET @j1Query = @j1Query + '; ';
END;
ELSE
BEGIN
SET @j1Query = @j1Query + ' union select '''+CONVERT(NVARCHAR(50),@i+1)+''', ';
END;
END;
IF ( @j % @Fcol = 0)--12%3
BEGIN
SET @tempf =1;
END;
ELSE
BEGIN
SET @tempf = @tempf + 1;
END
SET @j = @j + 1;
END;
SET @i = @i + 1;
END;
DECLARE @FQuery NVARCHAR(MAX);
PRINT @j1Query
SET @FQuery = @Query + @iQuery + @j1Query + ' select * from @temp ';
EXEC sp_executesql @FQuery;
END;
执行效果:
希望记录下来,让小伙伴参考一下,有更好的方法记得告诉我,必记笔记·