fieldname row and column name

Question

fieldname row and column name

Shambhu Rai 1,411

Hi Expert,  
  
i wanted to get the data according to field name  
  
create table main2( testid int ,oldvalue int, newvalue int, fieldname varchar(100),SaleDateId date, quantity int,cons int)  
--drop table main2  
insert  main2  
values  
  
(1,1,10,'quantity','20240307',10,1),  
(1,2,10,'cons','20240307',1,10),  
(2,3,10,'quantity','20240307',10,1),  
(2,4,10,'cons','20240307',1,10),  
(2,5,10,'con','20240307',1,10)  
  
expected output  
  
  
![183005-image.png][1]  
  
like this 100s of column available but it should be sorted based on the fieldname and it will  come next to field name column order by testid

Viorel 122.6K Reputation points

2022-03-14T22:28:02.94+00:00

Do you have two columns only to be sorted: quantity and cons?

2 answers

Your answer

Viorel 122.6K Reputation points

2022-03-14T22:28:02.94+00:00

Do you have two columns only to be sorted: quantity and cons?

Answer 1

Shambhu Rai 1,411

expected output

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-03-14T22:09:30.04+00:00

I may be missing something, but your expected output looks quite similar to the data in the table. The expected output has the columns in different order, and the testid for oldvalue = 5 is different.. Did you past the wrong data?
Shambhu Rai 1,411 Reputation points

2022-03-15T01:49:46.617+00:00

new value is updated in cons and quantity table and there can be n number of column . In the fieldname rows column name is mentioned that column should be available next to it

i repeat whatsoever column name available in in field name rows that should come next to it . In actual there are many columns availble in the table but for underpick standing i have given limited column ...how it will the same column from field name rows that is the logic
Bert Zhou-msft 3,436 Reputation points

2022-03-15T06:10:26.447+00:00

Hi，@Shambhu Rai

I'm not very clear about your needs, filename corresponds to other adjacent columns in this table, do you want to change the testid value according to the column value, and please give us an example, for example, when the filename is quantity, I see When there is such a field in the table, how does his quantity and cons columns change？

Thanks!
Bert Zhou

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

In the fieldname rows column name is mentioned that column should be available next to it.

The order of how the column appears is determined when you write the SELECT list. If you are talking about the value, you can do:

SELECT testid, oldvalue, newvalue, fieldname,
       CASE fieldname 
            WHEN 'quantity' THEN quantity
            WHEN 'cons'     THEN cons
       END AS ValueNextToFieldName,
       SaleDateId
FROM   main2

Share via

fieldname row and column name

2 answers

Your answer