SQL Dynamic Column Query

In order to achieve the free setting of tabular data in a database, we often design a table, or a column definition (keyvalue), to define the meaning of each field in a column with a super-many table. But the simple things in design are easily forgotten by people, as follows a simple but very loose list query.

--CREATE TABLE KeyValue--(--[key] NVARCHAR (),--[value] NVARCHAR--)CREATE TABLETablenumber (NameNVARCHAR( -), Filed1NVARCHAR( -), Filed2NVARCHAR( -), Filed3NVARCHAR( -), Filed4NVARCHAR( -), Filed5NVARCHAR( -),)INSERT  intodbo.        Tablenumber (Name, Filed1, Filed2, Filed3, Filed4, Filed5 )VALUES(N'Zhang 32',--Name-nvarchar (+)N'USA 2',--Filed1-nvarchar (+)N'Hebei 2',--Filed2-nvarchar (+)N'Tangshan 2',--Filed3-nvarchar (+)N"',--Filed4-nvarchar (+)N"'  --Filed5-nvarchar (+)        )INSERT  intoKeyValueVALUES('Filed1','National')INSERT  intoKeyValueVALUES('Filed2','Province')INSERT  intoKeyValueVALUES('Filed3','City')DECLARE @msg NVARCHAR(Max)="'SELECT @msg=@msg+','+[Key]+' as'+Value fromKeyValueSET @msg='name as names'+@msg;DECLARE @sql NVARCHAR(Max);SET @sql='Select'+@msg +'From Tablenumber'EXEC(@sql)

This design can be used for custom list queries, Excel export, easy to use, and more convenient to write in C #, but the disadvantage is to rely on the database.

SQL Dynamic Column Query