Click here to Skip to main content
14,743,331 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have table in SQL server called test and Column names fieldname,auditnewvalue
| fieldname  |  auditnewvalue |
|------------|----------------|
|Role_ID     |  34            |
|Resource_ID |	744137        |
|Role_ID     |  5             |
|Resource_ID |	974617        |
I have to get output like
| Role_ID    |  Resource_ID   |
|------------|----------------|
|34          |  744137        |
|5           |	974617        |


What I have tried:

select Role_ID, Resource_ID
from
(
  select auditnewvalue,fieldname
  from test
) d
pivot
(
  max(auditnewvalue)
  for fieldname in (Role_ID, Resource_ID)
) piv;
Posted
Updated 6-Feb-20 2:51am
v2
Comments
phil.o 6-Feb-20 7:39am
   
This is a terrible design imho to associate a row's value to the previous row. Nothing dictates the order in which SELECT fieldname, auditnewvalue FROM test; will return its results. Relying on this order is quite dangerous, then.

1 solution

You have nothing in each row that associates a particular Role_ID with a particular Resource_ID, nor do you have anything that suggests a specific order to the rows, so you are going to fail here.

You need to change your table schema. One idea would be to add an IDENTITY column that is automatically incremented as you add new rows. You would need to be very careful that you add rows in the correct sequence of Role_ID then Resource_ID then Role_ID etc

E.g.
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)
Now you have something to define the correct order you can do something like
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'
From the naming of the columns however, it looks like you would not be able to control the order in which rows are added (it looks like an audit table and you cannot really constrain the order in which things are updated). In that instance you should include a column in your table that in some way associates each update e.g. the Id of the original record.
Compare the code above with this
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'
   
Comments
Maciej Los 6-Feb-20 8:00am
   
Great answer!
CHill60 6-Feb-20 8:32am
   
Thank you
PrabhuJiya 6-Feb-20 8:31am
   
Thanks a lot @Chil60 , Its helped lot Great answer!
CHill60 6-Feb-20 8:32am
   
Thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900