Cast column name to specific character during Pivot table creation - SQL

Question

I have a table test which has a column Label. It has Data which is longers than 50 characters in length.

When I create a pivot table from the 'test' table , it uses those long charactered data as column name.

My requirement is to user cast function to limit the column names to say 26 characters.

I use the below script , but it doesnt work as desired.

create table V_Test as
select * from 
    (select * from Test) x
pivot (sum(Average) for Label in (
    S03_CreatePlansdadsada,
    S03_CreatePlan_T01_NavigateTosdsadsaded,
    S03_CreatePlan_T03_abcdefgmanagementsdasda,
    S03_CreatePlan_T16_SetStatusToOngoingasdasda,
    S03_CreatePlan_T17_Ldsdssdadsadas                           
)
) p

Thanks in advance for the help

Possible duplicate of [Change column name while using PIVOT SQL Server 2008](http://stackoverflow.com/questions/8122862/change-column-name-while-using-pivot-sql-server-2008) — trincot, Jun 13 '16 at 14:50
I was able to create the table by modifying the first two lines to the below : select * into V_Test from .. However , no luck in trimming the column names. Any help is appreciated.. — Tiyasa Talukdar, Jun 14 '16 at 07:40

LukStorms · Accepted Answer · 2016-06-15T08:18:07.787

Since the label names are hard-coded anyway, then using aliases for them should be fine.

On MS SQL Server it would look something like this.
Just using a variable table and a temporary table for the purpose of demonstration.

DECLARE @Test TABLE (PlanID int, Label varchar(50), Average int);
insert into @Test values 
(1,'S03_CreatePlansdadsada',10),
(1,'S03_CreatePlan_T01_NavigateTosdsadsaded',20),
(1,'S03_CreatePlan_T03_abcdefgmanagementsdasda',30),
(1,'S03_CreatePlan_T16_SetStatusToOngoingasdasda',40),
(1,'S03_CreatePlan_T17_Ldsdssdadsadas',50),
(1,'S03_CreatePlansdadsada',60),
(1,'S03_CreatePlan_T01_NavigateTosdsadsaded',70),
(1,'S03_CreatePlan_T03_abcdefgmanagementsdasda',80),
(1,'S03_CreatePlan_T16_SetStatusToOngoingasdasda',90),
(1,'S03_CreatePlan_T17_Ldsdssdadsadas',100);

IF OBJECT_ID('tempdb..#tmpTest') IS NOT NULL DROP TABLE #tmpTest;

select 
PlanID,
S03_CreatePlansdadsada as CreatePlans,
S03_CreatePlan_T01_NavigateTosdsadsaded as T01_NavigateTo,
S03_CreatePlan_T03_abcdefgmanagementsdasda as T03_managements,
S03_CreatePlan_T16_SetStatusToOngoingasdasda as T16_SetStatusToOng,
S03_CreatePlan_T17_Ldsdssdadsadas as T17_Lsd
into #tmpTest
from @Test
pivot (sum(Average) for Label in (
    S03_CreatePlansdadsada,
    S03_CreatePlan_T01_NavigateTosdsadsaded,
    S03_CreatePlan_T03_abcdefgmanagementsdasda,
    S03_CreatePlan_T16_SetStatusToOngoingasdasda,
    S03_CreatePlan_T17_Ldsdssdadsadas                           
    )
) p;

select * from  #tmpTest;

This would return the following results:

PlanID  CreatePlans T01_NavigateTo  T03_managements T16_SetStatusToOng  T17_Lsd
1           70          90          110             130                 150

The dynamic way is a bit more complicated, since the SQL needs to be constructed so it can be executed. (beware of code injection)

IF OBJECT_ID('tempdb..#tmpTestData') IS NOT NULL DROP TABLE #tmpTestData;
CREATE TABLE #tmpTestData (PlanID int, Label varchar(50), Average int);

insert into #tmpTestData values 
(1,'S03_CreatePlansdadsada',10),
(1,'S03_CreatePlan_T01_NavigateTosdsadsaded',20),
(1,'S03_CreatePlan_T03_abcdefgmanagementsdasda',30),
(1,'S03_CreatePlan_T16_SetStatusToOngoingasdasda',40),
(1,'S03_CreatePlan_T17_Ldsdssdadsadas',50),
(1,'S03_CreatePlansdadsada',60),
(1,'S03_CreatePlan_T01_NavigateTosdsadsaded',70),
(1,'S03_CreatePlan_T03_abcdefgmanagementsdasda',80),
(1,'S03_CreatePlan_T16_SetStatusToOngoingasdasda',90),
(1,'S03_CreatePlan_T17_Ldsdssdadsadas',100);

declare @Columns nvarchar(max);
declare @AliasedColumns nvarchar(max);
set  @Columns = STUFF((SELECT ', ' + QUOTENAME(Label) FROM  #tmpTestData GROUP BY Label FOR  XML PATH(''), TYPE).value('.', 'NVARCHAR(max)') ,1,1,'');
set  @AliasedColumns = STUFF((SELECT ', ' + QUOTENAME(Label) +' as '+ QUOTENAME(substring(Label,5,14)) FROM  #tmpTestData GROUP BY Label FOR  XML PATH(''), TYPE).value('.', 'NVARCHAR(max)') ,1,1,'');

declare @SQL nvarchar(max);

IF OBJECT_ID('tempdb..##tmpPivotTest') IS NOT NULL DROP TABLE ##tmpPivotTest;
set @SQL = 'select PlanID, '+ @AliasedColumns + ' 
into ##tmpPivotTest
from #tmpTestData pivot (sum(Average) for Label in ('+ @Columns +')) p';

--select @SQL;
exec (@SQL);

select * from ##tmpPivotTest;
IF OBJECT_ID('tempdb..##tmpPivotTest') IS NOT NULL DROP TABLE ##tmpPivotTest;

For an Oracle database it would look something like this:

CREATE TABLE V_Test AS
SELECT * FROM (select Label, Average from Test)
PIVOT (
   SUM(Average) AS sum_average FOR (Label) IN (
     'S03_CreatePlansdadsada' as CreatePlans,
     'S03_CreatePlan_T01_NavigateTosdsadsaded' as T01_NavigateTo,
     'S03_CreatePlan_T03_abcdefgmanagementsdasda' as T03_Managements,
     'S03_CreatePlan_T16_SetStatusToOngoingasdasda' as T16_SetStatusToOng,
     'S03_CreatePlan_T17_Ldsdssdadsadas' as T17_Lsd                   
   )
) p;

Thank you , but this doesnt work for me. I get an error in 'as'. Sorry I didnt mention clearly before. This is SQL server — Tiyasa Talukdar, Jun 14 '16 at 06:38
I was able to create the table by modifying the first two lines to the below : select * into V_Test from .. However , no luck in trimming the column names. Any help is appreciated. — Tiyasa Talukdar, Jun 14 '16 at 07:38
Does the sql-server version I added work for you? It doesn't have to be a temporary table that only exists during the session. — LukStorms, Jun 14 '16 at 07:45

Cast column name to specific character during Pivot table creation - SQL

1 Answers1