PrabhuJiya Ответов: 1

Как преобразовать значения строк в столбцы для нескольких записей

У меня есть таблица в SQL server под названием test и имена столбцов fieldname,auditnewvalue
| fieldname  |  auditnewvalue |
|------------|----------------|
|Role_ID     |  34            |
|Resource_ID |	744137        |
|Role_ID     |  5             |
|Resource_ID |	974617        |
Я должен получить вывод типа
| Role_ID    |  Resource_ID   |
|------------|----------------|
|34          |  744137        |
|5           |	974617        |


Что я уже пробовал:

select Role_ID, Resource_ID
from
(
  select auditnewvalue,fieldname
  from test
) d
pivot
(
  max(auditnewvalue)
  for fieldname in (Role_ID, Resource_ID)
) piv;

phil.o

Это ужасный дизайн imho, чтобы связать значение строки с предыдущей строкой. Ничто не диктует порядок, в котором SELECT fieldname, auditnewvalue FROM test; вернет свои результаты. Полагаться на этот приказ довольно опасно.

1 Ответов

Рейтинг:
7

CHill60

У вас нет ничего в каждой строке, что связывает определенный Role_ID с определенным Resource_ID, и у вас нет ничего, что предполагает определенный порядок строк, поэтому вы потерпите неудачу здесь.

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

Напр.

declare @test table (
     id int identity(1,1), 
     fieldname varchar(50), 
     auditnewvalue int)

insert into @test (fieldname,auditnewvalue) values
('Role_ID',34),
('Resource_ID',	744137),
('Role_ID',5),
('Resource_ID',	974617)
Теперь у вас есть что то чтобы определить правильный порядок вы можете сделать что то вроде 
SELECT auditnewvalue AS Role_ID, Resource_ID
FROM
(
	select fieldname, auditnewvalue, LEAD(auditnewvalue,1) OVER (ORDER BY ID) as Resource_ID
	from @test
) as x
WHERE fieldname = 'Role_ID'
Однако, судя по именованию столбцов, вы не сможете контролировать порядок добавления строк (это похоже на таблицу аудита, и вы действительно не можете ограничить порядок обновления вещей). В этом случае вы должны включить в свою таблицу столбец, который каким-то образом связывает каждое обновление, например идентификатор исходной записи.
Сравните приведенный выше код с этим
declare @test2 table (idOfOriginal int, fieldname varchar(50), auditnewvalue int)
-- imitate the trigger that is creating these records
insert into @test2 (idOfOriginal, fieldname,auditnewvalue) values
(2,'Role_ID',5),
(1,'Role_ID',34),
(1,'Resource_ID',	744137),
(2,'Resource_ID',	974617)

SELECT auditnewvalue AS Role_ID, Resource_ID
FROM
(
	select fieldname, auditnewvalue, LEAD(auditnewvalue,1) OVER (ORDER BY idOfOriginal, fieldname) as Resource_ID
	from @test2
) as x
WHERE fieldname = 'Resource_ID'

Maciej Los

Отличный ответ!

CHill60

Спасибо

PrabhuJiya

Большое спасибо @Chil60 , его очень помог отличный ответ!

CHill60

Спасибо