SQL字段名自定义显示应用实例

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

-- 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字段名自定义显示应用实例

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

--> 生成测试数据表: [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 '张三','北京','134' UNION ALL
SELECT '李四','北京','135'

--> 生成测试数据表: [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','姓名' UNION ALL
SELECT 'tb','b','联系方式' UNION ALL
SELECT 'tb','c','Email'

--SELECT * FROM [tb]
--SELECT * FROM [cols]

-->SQL查询如下:
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s+',','')
         + QUOTENAME(a.[name])+' AS '+QUOTENAME(b.[showname])
FROM syscolumns a
    JOIN cols b
        ON OBJECT_ID(b.tbname)=a.id
            AND a.name=b.colname
            AND b.[tbname]='tb'
EXEC('SELECT '+@s+' FROM tb')
/*
姓名         联系方式       Email
---------- ---------- -----------
张三         北京         134
李四         北京         135

(2 行受影响)
*/