SQL Server table field value to column name example

A few days ago, my colleague asked me how to convert a field value to a column. I wrote a simple Demo to share it. The Code is as follows: -- create a test table and add Test Data createtable # temp (amoney, bvarchar (10) * insertinto # temp (a, B) values () insert

A few days ago, a colleague asked me how to convert a field value to a column. I wrote a simple Demo to share it. The Code is as follows: -- create a test table and add Test Data create table # temp (a money, B varchar (10)/* insert into # temp (a, B) values (10, '1') insert

SQL Server table field value to column name example

A few days ago, my colleague asked me how to convert the field value into a field column and wrote a simple Demo to share it.

The Code is as follows:

-- Create a test table and add Test Data
Create table # temp (a money, B varchar (10 ))

/*
Insert into # temp (a, B) values (10, '1 ')
Insert into # temp (a, B) values (20, '2 point ')
Insert into # temp (a, B) values (20, '3 ')
Insert into # temp (a, B) values (20, '4 point ')
Insert into # temp (a, B) values (20, '5 ')
Insert into # temp (a, B) values (30, '6 point ')
Insert into # temp (a, B) values (20, '7 point ')
Insert into # temp (a, B) values (48, '8 point ')
Insert into # temp (a, B) values (20, '9 point ')
Insert into # temp (a, B) values (15, '10 ')
*/
Select * from # temp

-- Query Result
Declare @ SQL nvarchar (max) = '';
Select @ SQL = @ SQL + (case @ SQL when ''then'' 'else', 'end) + 'max (case B when''' + B + ''' then a else 0 end) ['+ B +'] 'from # temp;
Set @ SQL = 'select' + @ SQL + 'from # temp ;';
Exec sp_executesql @ SQL;

-- Drop table # temp

Posted on

,