Share via

Pivot Table

Cholotron 161 Reputation points
2021-02-12T00:30:06.477+00:00

Hi,
I need some help creating a pivot table

This is the data : for each employee there are 14 Days)
how do i create a pivot table? Day (day_date) as columns?

here is my table difinition 

CREATE TABLE [dbo].[tc_shiftx](  
	[shift_id] [int] IDENTITY(1,1) NOT NULL,  
	[emp_id] [char](5) NOT NULL,  
	[emp_group] [char](8) NULL,  
	[week_id] [int] NOT NULL,  
	[day_date] [date] NOT NULL,  
	[umc_sh] [decimal](6, 2) NOT NULL CONSTRAINT [DF_tc_shiftx_umc_sh]  DEFAULT ((0)),  
	[umc_vac] [decimal](6, 2) NOT NULL CONSTRAINT [DF_tc_shiftx_umc_vac]  DEFAULT ((0)),  
	[umc_bank] [decimal](6, 2) NOT NULL CONSTRAINT [DF_tc_shiftx_umc_bank]  DEFAULT ((0)),  
	[umc_over] [decimal](6, 2) NOT NULL CONSTRAINT [DF_tc_shiftx_umc_over]  DEFAULT ((0)),  
 CONSTRAINT [PK_tc_shiftx] PRIMARY KEY CLUSTERED   
(  
	[shift_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]

data looks like this
67148-qus1.png

i need a pivot table similar to this: (Is it posible?)

67206-qus2.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,666 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,206 Reputation points
    2021-02-12T02:49:28.843+00:00

    Hi @Cholotron ，

    Welcome to Microsoft Q&A!

    Actually it is a little difficulty to write a query to get your expected output using TSQL.

    You could check whether there is a better way using Excel, SSIS, SSRS or other tool.

    In TSQL, I took a lot of time and could perform as below. You could check whether it is a little helpful to you. 

    ;with cte as (  
 select emp_id,day_date,umc_sh,umc_vac,umc_bank,umc_over  
 ,'umc_sh_' + CONVERT(nvarchar(30), day_date, 126) umc_sh_name  
 ,'umc_vac_' + CONVERT(nvarchar(30), day_date, 126) umc_vac_name  
 ,'umc_bank_' +CONVERT(nvarchar(30), day_date, 126) umc_bank_name  
 ,'umc_over_' + CONVERT(nvarchar(30), day_date, 126) umc_over_name  
  from [tc_shiftx]  
 where week_id=112 and emp_group='MANF'  
)  
  
select emp_id  
,max([umc_sh_2021-01-24])  [umc_sh_2021-01-24]  
,max([umc_vac_2021-01-24]) [umc_vac_2021-01-24]   
,max([umc_bank_2021-01-24])  [umc_bank_2021-01-24]  
,max([umc_over_2021-01-24]) [umc_over_2021-01-24]  
,max([umc_sh_2021-01-25])  [umc_sh_2021-01-25]  
,max([umc_vac_2021-01-25]) [umc_vac_2021-01-25]   
,max([umc_bank_2021-01-25])  [umc_bank_2021-01-25]  
,max([umc_over_2021-01-25]) [umc_over_2021-01-25]  
,max([umc_sh_2021-01-26])  [umc_sh_2021-01-26]  
,max([umc_vac_2021-01-26]) [umc_vac_2021-01-26]   
,max([umc_bank_2021-01-26])  [umc_bank_2021-01-26]  
,max([umc_over_2021-01-26]) [umc_over_2021-01-26]  
,max([umc_sh_2021-01-27])  [umc_sh_2021-01-27]  
,max([umc_vac_2021-01-27]) [umc_vac_2021-01-27]   
,max([umc_bank_2021-01-27])  [umc_bank_2021-01-27]  
,max([umc_over_2021-01-27]) [umc_over_2021-01-27]  
,max([umc_sh_2021-01-28])  [umc_sh_2021-01-28]  
,max([umc_vac_2021-01-28]) [umc_vac_2021-01-28]   
,max([umc_bank_2021-01-28])  [umc_bank_2021-01-28]  
,max([umc_over_2021-01-28]) [umc_over_2021-01-28]  
,max([umc_sh_2021-01-29])  [umc_sh_2021-01-29]  
,max([umc_vac_2021-01-29]) [umc_vac_2021-01-29]   
,max([umc_bank_2021-01-29])  [umc_bank_2021-01-29]  
,max([umc_over_2021-01-29]) [umc_over_2021-01-29]  
,max([umc_sh_2021-01-30])  [umc_sh_2021-01-30]  
,max([umc_vac_2021-01-30]) [umc_vac_2021-01-30]   
,max([umc_bank_2021-01-30])  [umc_bank_2021-01-30]  
,max([umc_over_2021-01-30]) [umc_over_2021-01-30]  
,max([umc_sh_2021-01-31])  [umc_sh_2021-01-31]  
,max([umc_vac_2021-01-31]) [umc_vac_2021-01-31]   
,max([umc_bank_2021-01-31])  [umc_bank_2021-01-31]  
,max([umc_over_2021-01-31]) [umc_over_2021-01-31]  
,max([umc_sh_2021-02-01])  [umc_sh_2021-02-01]  
,max([umc_vac_2021-02-01]) [umc_vac_2021-02-01]   
,max([umc_bank_2021-02-01])  [umc_bank_2021-02-01]  
,max([umc_over_2021-02-01]) [umc_over_2021-02-01]  
,max([umc_sh_2021-02-02])  [umc_sh_2021-02-02]  
,max([umc_vac_2021-02-02]) [umc_vac_2021-02-02]   
,max([umc_bank_2021-02-02])  [umc_bank_2021-02-02]  
,max([umc_over_2021-02-02]) [umc_over_2021-02-02]  
,max([umc_sh_2021-02-03])  [umc_sh_2021-02-03]  
,max([umc_vac_2021-02-03]) [umc_vac_2021-02-03]   
,max([umc_bank_2021-02-03])  [umc_bank_2021-02-03]  
,max([umc_over_2021-02-03]) [umc_over_2021-02-03]  
,max([umc_sh_2021-02-04])  [umc_sh_2021-02-04]  
,max([umc_vac_2021-02-04]) [umc_vac_2021-02-04]   
,max([umc_bank_2021-02-04])  [umc_bank_2021-02-04]  
,max([umc_over_2021-02-04]) [umc_over_2021-02-04]  
,max([umc_sh_2021-02-05])  [umc_sh_2021-02-05]  
,max([umc_vac_2021-02-05]) [umc_vac_2021-02-05]   
,max([umc_bank_2021-02-05])  [umc_bank_2021-02-05]  
,max([umc_over_2021-02-05]) [umc_over_2021-02-05]  
,max([umc_sh_2021-02-06])  [umc_sh_2021-02-06]  
,max([umc_vac_2021-02-06]) [umc_vac_2021-02-06]   
,max([umc_bank_2021-02-06])  [umc_bank_2021-02-06]  
,max([umc_over_2021-02-06]) [umc_over_2021-02-06]  
from (  
select * from cte s  
 pivot  
 (max(umc_sh)   
 for umc_sh_name in([umc_sh_2021-01-24],[umc_sh_2021-01-25],[umc_sh_2021-01-26],[umc_sh_2021-01-27],[umc_sh_2021-01-28],[umc_sh_2021-01-29],[umc_sh_2021-01-30],[umc_sh_2021-01-31],[umc_sh_2021-02-01],[umc_sh_2021-02-02],[umc_sh_2021-02-03],[umc_sh_2021-02-04],[umc_sh_2021-02-05],[umc_sh_2021-02-06])) p  
 pivot  
 (max(umc_vac)   
 for umc_vac_name in([umc_vac_2021-01-24],[umc_vac_2021-01-25],[umc_vac_2021-01-26],[umc_vac_2021-01-27],[umc_vac_2021-01-28],[umc_vac_2021-01-29],[umc_vac_2021-01-30],[umc_vac_2021-01-31],[umc_vac_2021-02-01],[umc_vac_2021-02-02],[umc_vac_2021-02-03],[umc_vac_2021-02-04],[umc_vac_2021-02-05],[umc_vac_2021-02-06])) p1  
  pivot  
 (max(umc_bank)   
 for umc_bank_name in([umc_bank_2021-01-24],[umc_bank_2021-01-25],[umc_bank_2021-01-26],[umc_bank_2021-01-27],[umc_bank_2021-01-28],[umc_bank_2021-01-29],[umc_bank_2021-01-30],[umc_bank_2021-01-31],[umc_bank_2021-02-01],[umc_bank_2021-02-02],[umc_bank_2021-02-03],[umc_bank_2021-02-04],[umc_bank_2021-02-05],[umc_bank_2021-02-06])) p2  
  pivot  
 (max(umc_over)   
 for umc_over_name in([umc_over_2021-01-24],[umc_over_2021-01-25],[umc_over_2021-01-26],[umc_over_2021-01-27],[umc_over_2021-01-28],[umc_over_2021-01-29],[umc_over_2021-01-30],[umc_over_2021-01-31],[umc_over_2021-02-01],[umc_over_2021-02-02],[umc_over_2021-02-03],[umc_over_2021-02-04],[umc_over_2021-02-05],[umc_over_2021-02-06])) p3  
 ) a  
group by emp_id

    Output:
    67150-output.png

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cholotron 161 Reputation points
    2021-02-12T15:56:24.217+00:00

    Hi Melissa,
    Thank you for answering my post

    It does work

    Thank you!!!!

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.