--将同一个表中 不同[Group]属性的添加列分成一整行
SELECT * INTO VP_Temp FROM
(
SELECT DISTINCT A.ID,A.TypeName,A.ItemType,A.isBad,
A.sNo sNo1,A.CheckItem CheckItem1,A.BadValue BadValue1,A.[Group] Group1,
B.sNo sNo2,B.CheckItem CheckItem2,B.BadValue BadValue2,B.[Group] Group2,
C.sNo sNo3,C.CheckItem CheckItem3,C.BadValue BadValue3,C.[Group] Group3,
D.sNo sNo4,D.CheckItem CheckItem4,D.BadValue BadValue4,D.[Group] Group4
FROM
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=1) A
LEFT OUTER JOIN
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=2) B
ON A.ID=B.ID AND A.TypeName=B.TypeName AND A.ItemType=B.ItemType AND A.isBad=B.isBad
LEFT OUTER JOIN
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=3) C
ON B.ID=C.ID AND B.TypeName=C.TypeName AND B.ItemType=C.ItemType AND B.isBad=C.isBad
LEFT OUTER JOIN
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=4) D
ON C.ID=D.ID AND C.TypeName=D.TypeName AND C.ItemType=D.ItemType AND C.isBad=D.isBad
)TAB
--每四个一组
序号 值 组属性
1 妲己 1
2 后羿 2
3 鲁班 3
4 狄仁杰 4
5 甄姬 1
6 安琪拉 2
7 亚瑟 3
8 小乔 4
9 周瑜 1
10 孙尚香 2
--先补足相同的数量 利用存储过程
序号 值 组属性
1 妲己 1
2 后羿 2
3 鲁班 3
4 狄仁杰 4
5 甄姬 1
6 安琪拉 2
7 亚瑟 3
8 小乔 4
9 周瑜 1
10 孙尚香 2
11 null 3 --能被4整除的
12 null 4 --能被4整除的
然后变成
--其中rn为rownumber()
rn 序号 值 组属性 序号 值 组属性 序号 值 组属性 序号 值 组属性
1 1 妲己 1 2 后羿 2 3 甄姬 3 4 鲁班 4
2 5 狄仁杰 1 6 庄周 2 7 孙膑 3 8 赵云 4
3 9 宫本 1 10 张飞 2 11 关羽 3 12 项羽 4
4 13 虞姬 1 14 NULL 2 15 NULL 3 16 NULL 4
附上我的存储过程
--------------------------------原物料验货单打印-------------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'up_PrintCheckMaterial' AND type = 'P')
DROP PROCEDURE up_PrintCheckMaterial
GO
CREATE PROCEDURE up_PrintCheckMaterial
@CheckNo varchar(100), --单号
@SourceType varchar(20), --来源单号
@Category varchar(100), --检验类别
@ItemType varchar(100) --检验大类
WITH ENCRYPTION
AS
--CREATE TEMP TABLE Print_Temp
IF(OBJECT_ID('Print_Temp') IS NOT NULL) DROP TABLE Print_Temp --DELETE TABLE
SELECT DISTINCT
ROW_NUMBER() over(Order by B.ItemType ASC) sNo,
C.TypeName,B.ItemType,A.CheckItem,C.isBad,B.BadValue,'' [Group]
INTO Print_Temp
FROM QCCheckItem A
LEFT OUTER JOIN CheckDetail B ON A.COMPANYCODE=B.COMPANYCODE AND A.ITEMTYPE=B.ITEMTYPE
LEFT OUTER JOIN QCItemType C ON A.ITEMTYPE=C.ITEMTYPE
WHERE B.CHECKNO=@CheckNo AND B.SOURCETYPE=@SourceType AND A.SEQ=B.CHECKITEMSEQ
AND B.ItemType IN
(SELECT ItemType FROM QCCheckCategoryType
WHERE Category=@Category) AND B.ItemType=@ItemType
DECLARE @Length INT
SELECT @Length=COUNT(sNo) FROM Print_Temp
IF(@Length=0)
BEGIN
SELECT ''ID,''TypeName,''ItemType,''isBad,
''sNo1,''CheckItem1,''BadValue1,''Group1,
''sNo2,''CheckItem2,''BadValue2,''Group2,
''sNo3,''CheckItem3,''BadValue3,''Group3,
''sNo4,''CheckItem4,''BadValue4,''Group4
END ELSE BEGIN
--游标 修改组属性
DECLARE @id INT =1
DECLARE @currentNum int =1
DECLARE tmp_Print_cursor CURSOR
FOR
SELECT sNo FROM Print_Temp
OPEN tmp_Print_cursor
FETCH tmp_Print_cursor INTO @id
WHILE ( @@fetch_status = 0 )
BEGIN
UPDATE Print_Temp set [Group]=@currentNum where sNo =@id
set @currentNum+=1
if @currentNum>4
begin
set @currentNum=1
end
FETCH tmp_Print_cursor INTO @id
END
CLOSE tmp_Print_cursor
DEALLOCATE tmp_Print_cursor
--找数据
DECLARE @TypeName VARCHAR(100)
DECLARE @isBad BIT
SELECT @TypeName=TypeName,@isBad=isBad FROM QCItemType WHERE ItemType=@ItemType
--补数据 2
DECLARE @COUNT2 INT
SELECT @COUNT2=COUNT(*) FROM Print_Temp WHERE [GROUP]=1
DECLARE @ROW2 INT
SELECT @ROW2=COUNT(*) FROM Print_Temp WHERE [GROUP]=2
IF(@COUNT2=@ROW2)
BEGIN
EXEC up_PrintCheckMaterial @CheckNo,@SourceType,@Category,@ItemType
END
ELSE BEGIN
WHILE @ROW2<@COUNT2
BEGIN
INSERT INTO Print_Temp VALUES((SELECT MAX(sNo)+1 FROM Print_Temp),@TypeName,@ItemType,NULL,@isBad,NULL,2)
SELECT @ROW2=@ROW2+1
END
END
--补数据 3
DECLARE @COUNT3 INT
SELECT @COUNT3=COUNT(*) FROM Print_Temp WHERE [GROUP]=1
DECLARE @ROW3 INT
SELECT @ROW3=COUNT(*) FROM Print_Temp WHERE [GROUP]=3
IF(@COUNT3=@ROW3)
BEGIN
EXEC up_PrintCheckMaterial @CheckNo,@SourceType,@Category,@ItemType
END
ELSE BEGIN
WHILE @ROW3<@COUNT3
BEGIN
INSERT INTO Print_Temp VALUES((SELECT MAX(sNo)+1 FROM Print_Temp),@TypeName,@ItemType,NULL,@isBad,NULL,3)
SELECT @ROW3=@ROW3+1
END
END
--补数据 4
DECLARE @COUNT4 INT
SELECT @COUNT4=COUNT(*) FROM Print_Temp WHERE [GROUP]=1
DECLARE @ROW4 INT
SELECT @ROW4=COUNT(*) FROM Print_Temp WHERE [GROUP]=4
IF(@COUNT4=@ROW4)
BEGIN
EXEC up_PrintCheckMaterial @CheckNo,@SourceType,@Category,@ItemType
END
ELSE BEGIN
WHILE @ROW4<@COUNT4
BEGIN
INSERT INTO Print_Temp VALUES((SELECT MAX(sNo)+1 FROM Print_Temp),@TypeName,@ItemType,NULL,@isBad,NULL,4)
SELECT @ROW4=@ROW4+1
END
END
--SELECT * FROM Print_Temp ORDER BY [GROUP],sNo--WHERE [Group]=@Group
--数据合并成一行 CREATE TEMP2 TABLE VP_Temp
IF(OBJECT_ID('VP_Temp') IS NOT NULL) DROP TABLE VP_Temp--DELETE TABLE
SELECT * INTO VP_Temp FROM
(
SELECT DISTINCT A.ID,A.TypeName,A.ItemType,A.isBad,
A.sNo sNo1,A.CheckItem CheckItem1,A.BadValue BadValue1,A.[Group] Group1,
B.sNo sNo2,B.CheckItem CheckItem2,B.BadValue BadValue2,B.[Group] Group2,
C.sNo sNo3,C.CheckItem CheckItem3,C.BadValue BadValue3,C.[Group] Group3,
D.sNo sNo4,D.CheckItem CheckItem4,D.BadValue BadValue4,D.[Group] Group4
FROM
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=1) A
LEFT OUTER JOIN
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=2) B
ON A.ID=B.ID AND A.TypeName=B.TypeName AND A.ItemType=B.ItemType AND A.isBad=B.isBad
LEFT OUTER JOIN
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=3) C
ON B.ID=C.ID AND B.TypeName=C.TypeName AND B.ItemType=C.ItemType AND B.isBad=C.isBad
LEFT OUTER JOIN
(SELECT ROW_NUMBER() over(Order by sNo ASC) ID,* FROM Print_Temp WHERE [Group]=4) D
ON C.ID=D.ID AND C.TypeName=D.TypeName AND C.ItemType=D.ItemType AND C.isBad=D.isBad
)TAB
SELECT * FROM VP_Temp ORDER BY ID
END
GO
EXEC up_PrintCheckMaterial '19071200801','DR','A01','c001'
EXEC up_PrintCheckMaterial '19071200801','DR','A01','C10086'
数据前：
SELECT INTO后：
