SQL field name custom display application instance

----------------------------------------------------------------------------------

-- Author: htl258 (Tony)

-- Date: 2010-06-30 09:35:18

-- Version: Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel x86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <x86> (build 2600: Service Pack 2)

-- Blog: http://blog.csdn.net/htl258

-- Subject: SQL field name custom display application instance

----------------------------------------------------------------------------------

--> Generate a test data table: [TB]
If object_id ('[TB]') is not null
Drop table [TB]
Go
Create Table [TB] ([a] [nvarchar] (10), [B] [nvarchar] (10), [C] [int])
Insert into [TB]
Select 'zhang san', 'beijing', '000000' Union all
Select 'Li si', 'beijing', '123'

--> Generate a test data table: [Cols]
If object_id ('[Cols]') is not null
Drop table [Cols]
Go
Create Table [Cols] ([tbname] [nvarchar] (10), [colname] [nvarchar] (10), [showname] [nvarchar] (10 ))
Insert into [Cols]
Select 'tb', 'A', 'name' Union all
Select 'tb', 'B', 'Contact information' Union all
Select 'tb', 'C', 'email'

-- Select * from [TB]
-- Select * from [Cols]

--> The SQL query is as follows:
Declare @ s varchar (8000)
Select @ s = isnull (@ s + ',','')
+ Quotename (A. [name]) + 'as' + quotename (B. [showname])
From syscolumns
Join Cols B
On object_id (B. tbname) = A. ID
And a. Name = B. colname
And B. [tbname] = 'tb'
Exec ('select' + @ s + 'from tb ')
/*
Name contact information email
-------------------------------
Zhang San, Beijing 134
Li Si, Beijing 135

(2 rows affected)
*/