时间戳

首页 > 解决方案 > 具有有限列数的枢轴

sql - 具有有限列数的枢轴

问题描述

我在 Microsoft SQL 环境中遇到查询问题。由于客户的模板，我得到了我想从行转换为列的序列号。对我来说限制是客户的模板 - 最大列数可能是 10。我不知道如何达到我的愿景，即每 11-20、21-30 等列都将位于新行中。请参阅数据库中 13 行的示例：

我能做的：

产品01 产品02 产品03 产品04 产品05 产品06 产品07 产品08 产品09 产品10 产品11 产品12 产品13
BK018001B6 BK018001B7 BK018001B8 BK018001B9 BK018001BB BK018001BC BK018001BD BK018001BF BK018001BG BK018001BH BK018001BJ BK018001BK BK018001BL

我想做的事：

产品01 产品02 产品03 产品04 产品05 产品06 产品07 产品08 产品09 产品10
BK018001B6 BK018001B7 BK018001B8 BK018001B9 BK018001BB BK018001BC BK018001BD BK018001BF BK018001BG BK018001BH
BK018001BJ BK018001BK BK018001BL

我发现以下适用于 Microsoft SQL 的工作场所：https ://sqlzoo.net/ 。不幸的是，没有办法为您提供工作场所作为链接。您需要复制我的 SQL 代码来重现：

CREATE TABLE #sourceTable(SerialNo VARCHAR(20), ProductRowNumber VARCHAR(10))

INSERT INTO #sourceTable
(
    SerialNo,
    ProductRowNumber
)
VALUES
    ('BK018001B6', 'Product01'),
    ('BK018001B7', 'Product02'),
    ('BK018001B8', 'Product03'),
    ('BK018001B9', 'Product04'),
    ('BK018001BB', 'Product05'),
    ('BK018001BC', 'Product06'),
    ('BK018001BD', 'Product07'),
    ('BK018001BF', 'Product08'),
    ('BK018001BG', 'Product09'),
    ('BK018001BH', 'Product10'),
    ('BK018001BJ', 'Product11'),
    ('BK018001BK', 'Product12'),
    ('BK018001BL', 'Product13')

CREATE TABLE #productsTempTable (ProductSerialNo VARCHAR(20), ProductRowNumber VARCHAR(10))

INSERT INTO #productsTempTable
SELECT SerialNo, ProductRowNumber FROM #sourceTable

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF(
                    (SELECT ',' + QUOTENAME(ProductRowNumber)
                    FROM #productsTempTable
                    GROUP BY ProductRowNumber
                    ORDER BY ProductRowNumber
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                , 1, 1, '')

SET @query = N'SELECT ' + @cols + N' FROM 
             (
                SELECT ProductSerialNo, ProductRowNumber
                FROM #productsTempTable
            ) x
            PIVOT 
            (
                MAX(ProductSerialNo)
                FOR ProductRowNumber IN (' + @cols + N')
            ) p'

EXEC sp_executesql @query;

DROP table #productsTempTable, #sourceTable

实际上，为了完整起见，我正在以这种方式撰写“ProductRowNumber”列：

'Product' + RIGHT(100 + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(ORDER BY p.id)), 2) AS 'ProductRowNumber'

你能帮我解决这个问题吗？

谢谢你。

标签： sqlsql-server

解决方案

这个枢轴不需要是动态的。我们只需要对行号进行一些算术运算：

SELECT
  Product01, Product02, Product03, Product04, Product05, Product06, Product07, Product08, Product09, Product10
FROM (
    SELECT
      SerialNo,
      rn10 = (ROW_NUMBER() OVER (ORDER BY ProductRowNumber) - 1) / 10,
      ProductRN = CONCAT(
        'Product',
         FORMAT((ROW_NUMBER() OVER (ORDER BY ProductRowNumber) - 1) % 10 + 1, '00'))
    FROM #sourceTable st
) st
PIVOT (
    MAX(SerialNo) FOR ProductRN IN
      (Product01, Product02, Product03, Product04, Product05, Product06, Product07, Product08, Product09, Product10)
) pvt;

db<>小提琴

在您的实际查询中，您可以将 替换为ROW_NUMBER现有的行编号。

推荐阅读