在SQL中动态更改列名 [英] Change Columns Name dynamically in sql
本文介绍了在SQL中动态更改列名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的查询结果:
Date User1 User2 User3 ....
----------------------------------
1/1/2000 55 78 98 ...
1/1/2001 26 33 56 ...
1/1/2002 88 67 12 ...
列数未知,因为它是数据透视查询的结果.
The number of columns is not known because it is the result of a pivot query.
我想将列的名称更改为如下所示:
I would like to change the name the columns to something that looks like this :
Date User1 (blue) User2 (green) User3(brown)
颜色是我从另一个表中检索到的信息.
The color is an information I retrieve from another table.
我该如何实现?
谢谢
这是查询.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(C.Name)
from [History]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT [Date],' + @cols +'
from
(
select [Date], Name, Value
from [History]
) x
pivot
(
max(value)
for Name in (' + @cols + ')
) p '
execute(@query)
推荐答案
架构设置:
create table history (date datetime, name varchar(10), value int);
insert history values
('20130101', 'user1', 123),
('20130101', 'user2', 124),
('20130101', 'user3', 125),
('20130102', 'user1', 223),
('20130102', 'user3', 223),
('20130103', 'user2', 323);
create table colours (name varchar(10), colour_name varchar(10));
insert colours values
('user1', 'blue'),
('user2', 'green'),
('user3', 'brown');
查询:
DECLARE @scols nvarchar(max),
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((
SELECT ',' + QUOTENAME(C.Name)
from (select distinct name from [History]) C
ORDER BY C.Name
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
select @scols = STUFF((
SELECT ',' + QUOTENAME(Name) + ' AS ' + QUOTENAME(colour_Name)
from (select distinct c.name, x.colour_name
from [History] C
JOIN colours x on x.name = c.name) y
ORDER BY Name
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
set @query = '
SELECT [Date],' + @scols +'
from (
select [Date], Name, Value
from [History]
) x
pivot
(
max(value)
for Name in (' + @cols + ')
) p ';
-- print @query --<< uncomment this line to see the query that gets generated
exec (@query);
结果 :
Results:
| DATE | BLUE | GREEN | BROWN |
-------------------------------------------------------------
| January, 01 2013 00:00:00+0000 | 123 | 124 | 125 |
| January, 02 2013 00:00:00+0000 | 223 | (null) | 223 |
| January, 03 2013 00:00:00+0000 | (null) | 323 | (null) |
这篇关于在SQL中动态更改列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文