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