SQLTeam.com | Weblogs | Forums

How to Rename Column Names in Pivot Table

sql2014

#1

I have the following Code for Pivot table below. I would like to rename the following fields: IN ([Account Alias],[Op Completion],[Account Alias Fiber Set],[WIP Move],[Fiber RTS],[WIP Issue],[Fiber Scrap]tatus],[WIP Issue Fiber Set]) to something else.

For example [Account Alias] to TotalScrap, [Op Completion] to JobCompleted, and so on for the other column.

/****** Script for SelectTopNRows command from SSMS ******/
Select * FROM
(SELECT
[MES_MACHINE] As [LineNo]
,[DJ_NUMBER] AS [TubeJobNo]
,[PROCESSING_ACTION]
-- ,[ITEM_NUMBER]
,[TRANSACTION_QTY]
FROM [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch]) AS SourceData
PIVOT (
SUM(TRANSACTION_QTY)
FOR PROCESSING_ACTION
IN ([Account Alias]
,[Op Completion]
,[Account Alias Fiber Set]
,[WIP Move]
,[Fiber RTS]
,[WIP Issue]
,[Fiber Scrap]
,[Job Status]
,[WIP Issue Fiber Set])
) AS PivotTable
Order by [LineNo], [TubeJobNo] DESC


#2

this might work for you

select 
  [LineNo]
,S [TubeJobNo]
,[PROCESSING_ACTION]
,[TRANSACTION_QTY]
 ,[Account Alias] as TotalScrap
 , [Op Completion] as JobCompleted

 from (

Select * FROM	
(SELECT
[MES_MACHINE] As [LineNo]
,[DJ_NUMBER]	AS [TubeJobNo]
,[PROCESSING_ACTION]
-- ,[ITEM_NUMBER]
,[TRANSACTION_QTY]
FROM [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch]) AS SourceData
PIVOT (
SUM(TRANSACTION_QTY)
FOR PROCESSING_ACTION
IN ([Account Alias]
,[Op Completion]
,[Account Alias Fiber Set]
,[WIP Move]
,[Fiber RTS]
,[WIP Issue]
,[Fiber Scrap]
,[Job Status]
,[WIP Issue Fiber Set])
) AS PivotTable) x

Order by [LineNo], [TubeJobNo] DESC

#3

Thank you mike01 - It did not work.

The [PROCESSING_ACTION] has all the current headings for the Pivot table, such as:
[Account Alias]
,[Op Completion]
,[Account Alias Fiber Set]
,[WIP Move]
,[Fiber RTS]
,[WIP Issue]
,[Fiber Scrap]
,[Job Status]
,[WIP Issue Fiber Set]

I want to change all these headings.

Current output is:


#4

Can you provide some ddl and sample data. This works, you'll see I renamed [21] and [23] to Col1 and Col2

create table #t1 (name varchar(20), Address varchar(20), ClaimNumber int, Phone varchar(20)) 
create table #t2 (ClaimNumber int, ClaimCode smallint) 
 
insert into #t1  
select 'Joe', '1234 Road',1, '111-1111' union all 
select 'Sue', '456 Road',2, '222-2222' union all 
select 'Mike', '78 Street',3, '333-3333' union all 
select 'Jim', '1 Main',4, '444-4444'  
 
insert into #t2 
select 1, 21 union all 
select 1, 23 union all 
select 1, 54 union all 
select 1, 26 union all 
select 1, 83 union all 
select 2, 27 union all 
select 2, 30 union all 
select 3, 54  
 
  
--PIVOT  
DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT ClaimCode  
FROM #t2 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
EXEC ('select name, Address, ClaimNumber, Phone,[21] as Col1, [23] as Col2, *
from (
SELECT *  
FROM   
(  
        SELECT a.name, a.Address, a.ClaimNumber, a.Phone, ClaimCode 
        FROM #t1 a inner join #t2 b 
                        on a.ClaimNumber = b.ClaimNumber 
) t  
PIVOT (avg(ClaimCode) FOR ClaimCode IN (' + @ColsList + ')) PVT ) x')

#5

Thank you mike01.