将记录3列
问题描述：
将记录3列基于记录计数在SQL Server将记录3列
我想选择从查询的记录。我有3个栏显示记录 我已经根据计数来分割记录，那就是我需要将记录分成10行，每行有 。
此外，在我有30多个记录的事件，它需要栏，显示记录1
请参考下面的屏幕截图相连。
Reg SNO Reg SNO Reg SNO 1 11 21 2 12 22 3 10 20 30 Reg SNO Reg SNO Reg SNO 31 41 51 32 42 52 33 40 50 60
记录应该像上面给出的Excel表格一样。
我试过下面的查询在一个存储过程。但看起来像我失踪一些逻辑连接，甚至奇数的功能，我已经在where Condition.If给出了大于30的记录它不是检索作为我预期。 任何帮助将非常感激。
CREATE PROC MYTEST
AS
IF OBJECT_ID('tempdb..#test1') IS NOT NULL
DROP TABLE #test1
IF OBJECT_ID('tempdb..#test2') IS NOT NULL
DROP TABLE #test2
IF OBJECT_ID('tempdb..#test3') IS NOT NULL
DROP TABLE #test3
SELECT ROW_NUMBER() OVER (ORDER BY rno) 'rno' ,
Reg ,
SerialNo
INTO #test1
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmployeeId) 'rno' ,
Reg ,
SerialNo
FROM tRe
) AS c
WHERE (rno % \10 > 0
AND (rno/10) % 3 = 0
)
OR (rno % 10 = 0
AND (rno/10) % 3 = 1
)
SELECT ROW_NUMBER() OVER (ORDER BY rno) 'rno1' ,
Reg AS Reg1 ,
SerialNo AS SerialNo1
INTO #test2
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmployeeId) 'rno' ,
Reg ,
SerialNo
FROM tRe
) AS c
WHERE (rno % 10 > 0
AND (rno/10) % 3 = 1
)
OR (rno % 10 = 0
AND (rno/10) % 3 = 0
)
SELECT ROW_NUMBER() OVER (ORDER BY rno) 'rno2' ,
Reg AS Reg2 ,
SerialNo AS SerialNo2
INTO #test3
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmployeeId) 'rno' ,
Reg ,
SerialNo
FROM tRe
) AS c
WHERE (rno % \10 > 0
AND (rno/10) % 3 = 0
)
OR (rno % 10 = 0
AND (rno/10) % 3 = 1
)
SELECT *
FROM #test1 a
FULL OUTER JOIN #test2 b ON a.rno = b.rno1
FULL outer join #test3 c on b.rno1=c.rno2
EXEC MYTEST
答
试试这个，在脚本中留言。
-- Create Employee table with row numbers
IF OBJECT_ID('tempdb..#Emps') IS NOT NULL DROP TABLE #Emps
SELECT
ROW_NUMBER() OVER (ORDER BY EmployeeId) 'rno'
, Reg
, SerialNo
INTO #Emps
FROM tRe
CREATE CLUSTERED INDEX CIX_tempEmps_rno ON #Emps (rno)
DECLARE @NumberOfPages INT = 50
;WITH innerTally -- Create your numbers to fill up your first page i.e. 1 - 30
AS
(
SELECT 1 AS ID
UNION ALL
SELECT ID + 1
FROM innerTally
WHERE ID < 30
),
outerTally
AS
(
-- Split your rows from previous table into three columns
SELECT
a.ID as Col1
, b.ID as Col2
, c.ID as Col3
FROM innerTally a
JOIN innerTally b
ON b.ID = a.ID + 10
AND b.ID <= 20
JOIN innerTally c
ON c.ID = b.ID + 10
UNION ALL
-- Create additional pages
SELECT
Col1 + 30
, Col2 + 30
, Col3 + 30
FROM OuterTally
WHERE Col1 < (@NumberOfPages - 1) * 30 + 10 -- Arbitrary limiter, just keep it as big as or bigger than you require
)
-- Join your table 3 times for each set of column ID's
SELECT
e1.rno
, e1.Reg
, e1.SerialNo
, e2.rno
, e2.Reg
, e2.SerialNo
, e3.rno
, e3.Reg
, e3.SerialNo
FROM OuterTally t
JOIN #Emps e1
ON e1.rno = t.Col1
JOIN #Emps e2
ON e2.rno = t.Col2
JOIN #Emps e3
ON e3.rno = t.Col3
ORDER BY 1
OPTION (MAXRECURSION 100) -- if you need more than 100 pages, you'll have to set this higher. You can set it to be the the same as @NumberOfPages (but you can't use a variable)
答
以下将数据分割成30行页面，然后将每个页面成10 3列...
编辑：更新以包括一个NVARCHAR数据类型列中。
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
ItemNo INT NOT NULL PRIMARY KEY CLUSTERED,
SomeString NVARCHAR(15) NOT NULL
);
INSERT #TestData (ItemNo, SomeString)
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
CAST(ao.object_id AS NVARCHAR(15))
FROM
sys.all_objects ao;
-- SELECT * FROM #TestData td;
--==============================================
WITH
cte_PageCol AS (
SELECT
td.ItemNo,
PageNum = DENSE_RANK() OVER (PARTITION BY m30.Mod30 ORDER BY td.ItemNo),
ColumnNum = DENSE_RANK() OVER (PARTITION BY m10.Mod10 ORDER BY m30.Mod30),
td.SomeString
FROM
#TestData td
CROSS APPLY (VALUES (ISNULL(NULLIF(td.ItemNo % 30, 0), 30))) m30 (Mod30)
CROSS APPLY (VALUES (ISNULL(NULLIF(m30.Mod30 % 10, 0), 10))) m10 (Mod10)
),
cte_AddRN AS (
SELECT
pc.ItemNo, pc.PageNum, pc.ColumnNum, pc.SomeString,
RN = ROW_NUMBER() OVER (PARTITION BY pc.PageNum, pc.ColumnNum ORDER BY pc.ItemNo)
FROM
cte_PageCol pc
)
SELECT
ar.PageNum,
Col1 = MAX(CASE WHEN ar.ColumnNum = 1 THEN ar.ItemNo END),
Col1_Val = MAX(CASE WHEN ar.ColumnNum = 1 THEN ar.SomeString END),
Col2 = MAX(CASE WHEN ar.ColumnNum = 2 THEN ar.ItemNo END),
Col2_Val = MAX(CASE WHEN ar.ColumnNum = 2 THEN ar.SomeString END),
Col3 = MAX(CASE WHEN ar.ColumnNum = 3 THEN ar.ItemNo END),
Col3_Val = MAX(CASE WHEN ar.ColumnNum = 3 THEN ar.SomeString END)
FROM
cte_AddRN ar
GROUP BY
ar.PageNum,
ar.RN
ORDER BY
ar.PageNum,
ar.RN;
结果样本...
PageNum Col1 Col1_Val Col2 Col2_Val Col3 Col3_Val
-------------------- ----------- --------------- ----------- --------------- ----------- ---------------
1 1 2121987014 11 1595261016 21 1832241610
1 2 1140411510 12 1403260332 22 1848639041
1 3 934103092 13 1355260161 23 464516666
1 4 1751830240 14 1419260389 24 435497504
1 5 111799388 15 1451260503 25 448516609
1 6 1671069267 16 1435260446 26 1090523542
1 7 1819977838 17 1371260218 27 1635970274
1 8 1611261073 18 358244459 28 127004803
1 9 1643261187 19 1867925954 29 786990954
1 10 1627261130 20 1851925897 30 432516552
2 31 1474524910 41 1002746925 51 1748617718
2 32 1643157077 42 841162320 52 1860618117
2 33 1675157191 43 873162434 53 980462995
+0
但是，当我通过nvarcahr值显示转换错误你能指导我吗。 –
+0
答案已更新为包含NVARCHAR数据类型。 –
+1
是的感谢它对所有列都正常工作，结果如预期。 –
哪里是截屏？ – Squirrel
我已经尝试过，但它显示错误。 –