How to convert row values to column for multiple records

Question

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:

SQL

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

Posted 6-Feb-20 1:22am

PrabhuJiya

Updated 6-Feb-20 1:51am

CHill60

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2020-02-06T01:52:00

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.

SQL

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

SQL

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

SQL

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'