SQL Server: сортировка значений в столбцах LIKE с динамическим поворотом

Версия: Microsoft SQL Server 2014.

Я успешно создал динамическую сводную таблицу (с помощью), и теперь у меня есть вопрос относительно имен столбцов и их значений.

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

SELECT @cols = STUFF((SELECT distinct ',' 
                        + QUOTENAME('Component_' + cast(rn as varchar(10))) 
                      FROM dbo.table
                      CROSS APPLY
                          (SELECT row_number() over(partition by UPC order by ComponentNum) rn
                           FROM dbo.table) x
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'SELECT UPC, ' + @cols + ' FROM 
             (
                SELECT UPC, ComponentNum,
                  ''Component_''
                    + cast(row_number() over(partition by UPC order by ComponentNum) as varchar(10)) val
                FROM dbo.table) x
            PIVOT
            (
                MAX(ComponentNum)
                FOR val IN (' + @cols + ')
            ) p '

execute(@query)

После поворота таблицы было создано 27 столбцов Component_X.

Предполагается, что 27 столбцов представляют различные виды деталей, связанных с уникальным номером UPC. Не все UPC имеют 27 видов деталей.

Кажется, что в зависимости от того, какой номер строки был указан в разделенной группе номеров UPC, он был назначен в сводном столбце.

Это указывает мне, что мне нужно отсортировать исходные данные перед запуском сводки, не так ли? Глядя на таблицу результатов ниже, вы можете видеть, что номер детали 543 показан под Component_13, Component_1 и Component_10 для разных номеров UPC.

ID          UPC       Component_13      Component_1      Component_10     
------------------------------------------------------------------------
1           123        543                 NULL                345
2           321        345                 543                 765
3           213        654                 345                 NULL         
4           312        765                 NULL                543

Моя проблема в том, что я не могу допустить, чтобы номер детали плавал между разными столбцами. Если номер детали относится к типу "Component_13", он должен оставаться в этом столбце.

Моя цель состоит в том, чтобы каждая итерация Component_X представляла определенный тип детали.

Component_1 = Bolts
Component_2 = Nuts
Component_3 = Washers

Другая проблема заключается в том, что мне также нужно перечислить несколько номеров деталей «Болт» в свой собственный столбец для поиска другой частью программного обеспечения. Любой номер UPC может иметь любое количество «болтов» разных размеров с разными номерами деталей.

Конечная таблица результатов может выглядеть так:

ID          UPC       Bolt1      Bolt2      Bolt3      Nut1      Nut2     
------------------------------------------------------------------------
1           123        1.5         1         NULL       0.5      .375
2           321        2.0       NULL        NULL       .625      NULL    
3           213        0.25      .875        .375       NULL      NULL         
4           312        NULL      NULL        NULL       1.25      .625

Вероятно, это невозможно, но я должен был спросить, может ли кто-нибудь помочь с решением.


sql-server dynamic-pivot sql-server-2014-express
person Seth Johnson    schedule 06.05.2017    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете использовать REPLICATE для создания номер из двух или более цифр, который можно легко заказать.

create table test(rn varchar(10));

insert into test 
values ('1'),  ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'), ('11'), ('12');

 select num
 from 
     (select 'Component_' + replicate('0',  2 - len(rn)) + rn as num
      from test) x
 order by num;

Результат:

 | num          |
 | :----------- |
 | Component_01 |
 | Component_02 |
 | Component_03 |
 | Component_04 |
 | Component_05 |
 | Component_06 |
 | Component_07 |
 | Component_08 |
 | Component_09 |
 | Component_10 |
 | Component_11 |
 | Component_12 |

dbfiddle здесь

person McNets    schedule 06.05.2017
comment
@marc_s этот формат автоматически генерируется dbfiddle - person McNets; 06.05.2017