Как выполнить поворот, не зная фиксированных столбцов в T-SQL

У меня есть таблица с именем balance, которую я хочу повернуть, однако это довольно сложно, поскольку имена столбцов будут помечены 1,2,3, а балансы будут отсортированы по убыванию для каждого клиента.

Это скриншот текущей таблицы:

введите здесь описание изображения

И сводная таблица:

введите здесь описание изображения

Я просмотрел другие видео и сообщения, но не нашел решения, соответствующего моей текущей ситуации, чего я хочу достичь. Таким образом, в конечном результате клиент будет отсортирован по возрастанию, а баланс — по убыванию. Таким образом, для клиента 3 наибольший баланс 500 будет помещен в столбец 1, 300 — в столбец 2 и 250 — в столбец 3.

Скрипт для создания образца данных:

select Customer, Balance
into #a
from (
values
  (1,     250), 
  (2,     500), 
  (1,     205), 
  (2,     600), 
  (2,     700),
  (3,     300),
  (3,     500),
  (3,     250)
) v (Customer, Balance)

sql-server tsql ssms pivot-table ssms-2017
person JasonX    schedule 21.02.2020    source источник
comment
Также, пожалуйста, не используйте изображения, используйте форматированный текст.   -  person Dale K    schedule 21.02.2020
comment
@daleK SQL SSMS   -  person JasonX    schedule 21.02.2020
comment
К вашему сведению: SSMS — это не RDBMS, а клиентский интерфейс для SQL Server.   -  person Dale K    schedule 21.02.2020
comment
Краткий ответ: вы не можете. T-SQL не поддерживает определенные во время выполнения или параметризованные идентификаторы (например, имена столбцов). Вам придется либо использовать Dynamic-SQL (пожалуйста, не делайте этого), либо выполнять PIVOT в коде приложения. Используйте Dynamic SQL с PIVOT/UNPIVOT только в том случае, если вам нужно обрабатывать сводные данные внутри SQL Server.   -  person Dai    schedule 21.02.2020
comment
хорошо @Дай. Я знаю, что в этой ситуации я должен использовать динамический SQL. Моя проблема в том, как передать значения в порядке убывания в список столбцов [1], [2], [3]? и если, например, у клиента 2 было только 2 записи, то 0 будет помещено в столбец 3.   -  person JasonX    schedule 21.02.2020

Ответы (3)


arrow_upward
3
arrow_downward

Демонстрация на db‹>скрипке

Вы можете использовать ROW_NUMBER(), чтобы отметить количество значений, например: 1, 2, 3.

Обратите внимание: ORDER BY [Balance] DESC чтобы получить желаемое значение.

DECLARE 
    @columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';


 SELECT Customer, Balance, Col = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY [Balance] DESC)
 into #b
 FROM #a

SELECT @columns += QUOTENAME(Col) + ','
from (SELECT DISTINCT Col FROM #b) A

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);


SET @sql = 'SELECT * FROM ( SELECT Customer, Balance, Col FROM  #b) src PIVOT( MAX([Balance]) FOR Col IN ('+ @columns +')) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Вывод

введите здесь описание изображения

Обновлено

После объединения строк недокументирован и ненадежен. Это не всегда работает должным образом. Таким образом, вы должны решить с помощью 2 решений ниже

  1. Используйте STRING_AGG (начиная с SQL Server 2017 и более поздних версий)
SELECT STRING_AGG(QUOTENAME(Col), ', ')
from (SELECT DISTINCT Col FROM #b) A
// Output: [1], [2], [3]
  1. Используйте расширения XML
DECLARE  @columns NVARCHAR(MAX) = ''
SELECT @columns = (
  SELECT QUOTENAME(Col) + ', '
  FROM (SELECT DISTINCT Col FROM #b) A

  FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
SELECT @columns 
// Output: [1], [2], [3],

Спасибо за комментарий @GarethD. Проверьте это на db‹>скрипке.

person Nguyễn Văn Phong    schedule 21.02.2020
comment
Использование назначения переменных для объединения строк (SELECT @Column += ....) недокументирован и ненадежен. Это не всегда работает должным образом. Вы должны либо использовать STRING_AGG (если используете версию, которая его поддерживает), либо использовать расширения XML для объединения строк. - person GarethD; 21.02.2020
comment
Спасибо за ваш комментарий. Я обновлю с вашим советом в ближайшее время. Потому что я в сети с телефона. - person Nguyễn Văn Phong; 21.02.2020
comment
Гарет Д. Ух ты. Вы довольно осведомлены. Я поставил проблему в эту строку (SELECT @Column += ....). но я никак не мог понять, как его настроить, чтобы добиться того, чего я действительно хотел. Вы очень здорово придумали. phong, когда я использовал свои исходные данные при выполнении, столбцы были бы [3], [1], [2]. И я действительно хотел, чтобы это было [1], [2], [3] после выполнения процедуры. - person JasonX; 21.02.2020
comment
Я только что обновил свой ответ, пожалуйста, посмотрите. Благодаря вашему совету @GarethD - person Nguyễn Văn Phong; 22.02.2020
comment
@JasonX Я только что обновил свой ответ. Пожалуйста, посмотрите и дайте мне свой отзыв. - person Nguyễn Văn Phong; 23.02.2020

arrow_upward
0
arrow_downward

Попробуй это.

declare @balanceAMTCols varchar(max)='',
@statement nvarchar(4000)=''

 select @balanceAMTCols= stuff( 
            (
                select ','+ QUOTENAME(balanceamtcol) from (
             select  distinct   convert(varchar(10),(row_number() over(partition by Customer order by Customer))) 
            balanceamtcol FROM #a   ) as tbl
            for xml path('')),1,1,'')

set @statement= 'select Customer,'+@balanceAMTCols+' from (
    select  Customer,Balance,convert(varchar(10),(row_number() over(partition by Customer order by Customer))) as balanceamtcol  FROM #a  
) [CustomerTbl]
pivot
(
    max(Balance)
    for balanceamtcol
    in ('+@balanceAMTCols+') ) as pivatetble'

exec sp_executesql  @statement
person Balamurugan Annamalai    schedule 24.02.2020

arrow_upward
0
arrow_downward

Вы можете попробовать этот способ

select *,concat('Price',RANK() OVER (PARTITION BY i.REFERENCE_NO ORDER BY i.TASK_ID 
 DESC)) AS Rank  into #temp from [dbo].[Table_1] i

 select REFERENCE_NO,Price1,Price2,Price3 from 
   (
    select REFERENCE_NO,TASK_ID,Rank from #temp
    ) as cte 
    PIVOT (SUM(TASK_ID)
    FOR rank IN (Price1,Price2,Price3 ))
    as PVT
person Bhupendra kumar    schedule 24.02.2020