--根据表A获得
declare @sql nvarchar(max)
 set sql='select A as  部门A,B as 部门B，C as 部门C from tb'

USE TEMPDB
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
GO
CREATE TABLE A(
COL1 VARCHAR(10) 
,COL2 VARCHAR(50)
)
INSERT INTO A 
SELECT 'A','部门A' UNION ALL
SELECT 'B','部门B' UNION ALL
SELECT 'C','部门C'
GO
DECLARE @STR VARCHAR(MAX)
SELECT @STR=ISNULL(@STR+',','SELECT ')+''''+COL1+''' AS ['+COL2+']'
FROM A
PRINT (@STR+' FROM TB')
/*
SELECT 'A' AS [部门A],'B' AS [部门B],'C' AS [部门C] FROM TB
*/