Change Columns Name dynamically in sql

Question

I have a query result like this :

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.

How can I achieve this ?

Thanks

Edit : Here is the query.

  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)

Where does these values blue, green, brown come from? Do you want to write them manually for each user name? If so then list the columns' names manually not dynamically since, you will write the colors' names manually after all. — Mahmoud Gamal
– Mahmoud Gamal, Commented Apr 24, 2013 at 7:09
The number of columns is not known because it is the result of a pivot query. Please show us the query. — Prahalad Gaggar
– Prahalad Gaggar, Commented Apr 24, 2013 at 7:10

RichardTheKiwi · Accepted Answer · 2013-04-24 09:48:25Z

SQL Fiddle

Schema Setup:

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');

Query:

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) |

Ian Kenney · Accepted Answer · 2013-04-24 07:45:55Z

0

To get the mapping You can use a lookup table of old column name to new column name for example

CREATE TABLE colname(
  oldname varchar(20),
  newname varchar(20)
)

insert into colname values ( 'user1','user1 (blue)');
insert into colname values ( 'user2','user2 (green)');

then you can build an sql statement that uses this mapping

declare @sq varchar(2000)
set @sq ='select date'
select @sq = @sq +  ',' + oldname + ' as [' + newname +']' from colname
set @sq = @sq + 'from ( existing query goes here ) ' 
select @sq

when the sql in @sq looks good you can replace the last select with

exec ( @sq )

to run the query

answered Apr 24, 2013 at 7:45

Ian Kenney

6,4561 gold badge27 silver badges45 bronze badges

Comments

Freelancer · Accepted Answer · 2013-04-24 07:05:20Z

-2

select Date, User1 as blue,User2 as green,User3 as brown from tableName

Use query like this.

Make use of 'as' keyword for changing column name.

answered Apr 24, 2013 at 7:05

Freelancer

9,1027 gold badges46 silver badges81 bronze badges

1 Comment

mark1234 Over a year ago

Those are static column names. OP specifically asked for dynamic col names, not just how to rename them

Collectives™ on Stack Overflow

Change Columns Name dynamically in sql

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related