CODEWITHSUNDEEP
sql-serversql-server-2008pivot
SpB
SpB

Reputation: 41

SQL Server 08 - pivot - Change Column Name

I have this code:

SELECT * FROM(
   SELECT A.Id,
          B.Note,
          C.Value,
          C.Ammount
   FROM Table1 A
   LEFT JOIN Table2 B ON A.Id = B.Id
   LEFT JOIN Table3 C ON B.Id = c.Id AND B.Name = C.Name
   INNER JOIN(
      SELECT Name, LName, AxValue, Code, Number
      FROM Table Ax
      Where (Code = 80 AND Name = 'Bo') AS D ON D.AxValue = C.Value)
)AS Node1
PIVOT(
   SUM(Ammount)
   FOR Value IN ([1], [2])
)AS Node2

and the result is something like this:

Id   Note   1    2
--------------------
01   ok    500  100

Is there any way to rename the last two columns with names(Ex1,Ex2) instead of numbers(1,2)?

Upvotes: 4

Views: 1485

Answers (2)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

Replace : 

SELECT * FROM(
...

By

SELECT Id, Note, [1] as Ex1, [2] as Ex2 FROM(
...

It is always better to replace * by the columns you need. You can alias them with as.

You can find more information here: Using Table Aliases

And here under column_ alias: SELECT Clause

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172518

Try this:

SELECT Id, Note, [1] as Ex1, [2] as Ex2 FROM(
   SELECT A.Id,
          B.Note,
          C.Value,
          C.Ammount
   FROM Table1 A
   LEFT JOIN Table2 B ON A.Id = B.Id
   LEFT JOIN Table3 C ON B.Id = c.Id AND B.Name = C.Name
   INNER JOIN(
      SELECT Name, LName, AxValue, Code, Number
      FROM Table Ax
      Where (Code = 80 AND Name = 'Bo') AS D ON D.AxValue = C.Value)
)AS Node1
PIVOT(
   SUM(Ammount)
   FOR Value IN ([1], [2])
)AS Node2

Instead of using * you need to specify the name of your column.

Upvotes: 0

Related Questions