SQL Server change column name for description

kq0g1dla 于 2023-04-04 发布在 SQL Server

关注(0)|答案(1)|浏览(153)

I have a table dbo.EX_DD12 that shows data, but the table columns have a code assigned ( DD12001, DD12002, DD12003....DD12055 ).

This query:

SELECT * FROM [dbo.EX_DD12]

returns results something like this:

Id | DD12001    | DD12002    | DD12003  
---+------------+------------+--------
 1 | 12/12/2005 | 10:00 a.m. | x  
 2 | 13/12/2005 | 10:00 a.m. | y  
 3 | 14/12/2005 | 09:00 a.m. | x

Using this query I can get the description:

SELECT 
    T0.name, T1.Description
INTO 
    #TABLE00
FROM 
    sys.columns T0
INNER JOIN 
    dbo.EXDT T1 ON T0.name = Code
WHERE 
    object_id = OBJECT_ID('dbo.EX_DD12') 
    AND T0.name LIKE 'DD12%'

The results look something like this:

Code    | Description  
--------+-------------
DD12001 | Date  
DD12002 | Time  
DD12003 | Priority

What can I do so that in a query the results looks like this?

Id | Date       | Time       | Priority
---+------------+------------+---------
 1 | 12/12/2005 | 10:00 a.m. | x  
 2 | 13/12/2005 | 10:00 a.m. | y  
 3 | 14/12/2005 | 09:00 a.m. | x

sql-server

来源：https://stackoverflow.com/questions/44270086/sql-server-change-column-name-for-description

1条答案

按热度按时间

polhcujo1#

In SQL Server:

Building a query dynamically and executing with sp_executesql :

declare @sql nvarchar(max);
declare @tbl sysname = 'dbo.EX_DD12'

select @sql = 'select' + char(10)
  + stuff((
    select char(10)+'  , '+quotename(c.name) + case 
        when c.name != d.description 
          then ' as ' +quotename(coalesce(d.[Description],c.name)) 
        else '' 
        end
      from sys.columns c 
        left join dbo.EXDT d
          on c.name = d.[Code]
      where c.object_id = object_id(@tbl)
      order by c.column_id 
    for xml path (''), type).value('.','nvarchar(max)')
    ,1,5,'    ')+ char(10)
  +'from '+quotename(schema_name(t.schema_id))+'.'+quotename(object_name(object_id(@tbl)))
  from sys.tables t where t.object_id = object_id(@tbl)

select @sql as CodeGenerated;
--exec sp_executesql @sql;

rextester demo: http://rextester.com/GGEL20070

returns:

+-----------------------------+
|        CodeGenerated        |
+-----------------------------+
| select                      |
|     [id]                    |
|   , [DD12001] as [Date]     |
|   , [DD12002] as [Time]     |
|   , [DD12003] as [Priority] |
| from [dbo].[EX_DD12]        |
+-----------------------------+

dynamic sql reference:

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server change column name for description

1条答案

相关问题

热门标签

最新问答