Sql query help

ashu · December 5, 2019, 6:11am

Pleas Help me...
My table is

i want output!

I try query like this but output is not proper

DECLARE @DynamicColumns AS VARCHAR(max)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')
+ Quotename(salarymonth)
FROM (SELECT DISTINCT salarymonth
FROM tblSalary) AS FieldList

	select @DynamicColumns


	DECLARE @FinalTableStruct AS NVARCHAR(max)

SET @FinalTableStruct = 'SELECT EmployeeName,EmployeeCode, ' + @DynamicColumns
+
' from tblSalary x pivot ( max( Basic ) for salarymonth in ('
+ @DynamicColumns + ') ) p '

EXECUTE(@FinalTableStruct)

Lincoln · December 5, 2019, 7:43am

Data definition language (DDL) DDL adjustments the shape of the desk like creating a table, deleting a table, altering a table, etc. ...
Data Manipulation Language. DML commands are used to adjust the database. ...
Data Control Language. ...
Transaction Control Language. ...
Data Query Language.

harishgg1 · December 5, 2019, 8:55am

hi

i have created script for the data ...

please click arrow to the left for drop create data

drop table tblSalary 
go 

create table tblSalary 
(
EmployeeCode varchar(10),
EmployeeName varchar(100),
SalaryMonth varchar(10),
Basic int,
GradePay int
)
go

insert into tblSalary select 'E1000','NESHA PRASAD DESAI',
'Jun-19',18990,4200
insert into tblSalary select 'E1000','NESHA PRASAD DESAI',
'May-19',16990,4200
---
insert into tblSalary select 'E1001','RAJASHRI RAVINDRA PATIL',
'Jun-19',16000,4200
insert into tblSalary select 'E1001','RAJASHRI RAVINDRA PATIL',
'May-19',16140,4200
--
insert into tblSalary select 'E1002','RAJENDRA MANOHAR ROKDE',
'Jun-19',16140,4200
insert into tblSalary select 'E1002','RAJENDRA MANOHAR ROKDE',
'May-19',16140,4200
--
insert into tblSalary select 'E1003','RAGHUNATH SHRIRANG MANE',
'Jun-19',11750,4200
insert into tblSalary select 'E1003','RAGHUNATH SHRIRANG MANE',
'May-19',11750,4200

insert into tblSalary select 'E1000','NESHA PRASAD DESAI',
'Jul-19',18990,4200
insert into tblSalary select 'E1000','NESHA PRASAD DESAI',
'Aug-19',16000,4200

insert into tblSalary select 'E1001','RAJASHRI RAVINDRA PATIL',
'Jul-19',16140,4200
insert into tblSalary select 'E1001','RAJASHRI RAVINDRA PATIL',
'Aug-19',15000,4200

insert into tblSalary select 'E1002','RAJENDRA MANOHAR ROKDE',
'Jul-19',16140,4200
insert into tblSalary select 'E1002','RAJENDRA MANOHAR ROKDE',
'Aug-19',16140,4200

insert into tblSalary select 'E1003','RAGHUNATH SHRIRANG MANE',
'Jul-19',11750,4200
insert into tblSalary select 'E1003','RAGHUNATH SHRIRANG MANE',
'Aug-19',11750,4200
go 

select 'data',* from tblSalary
go

ashu · December 5, 2019, 9:31am

Thanks u....
But my table is already created tblSalary... By using tblSalary i want output just like following image

ashu · December 5, 2019, 9:32am

thank u ... i already create tblSalary just like first Image

by using this i want output just like

ashu · December 5, 2019, 9:36am

like SalaryMonth converted into Rows ... and below salarymonth basic and gradepay...

harishgg1 · December 5, 2019, 9:38am

hi i tried to do this

got till here !!!

please click arrow to the left for SQL

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max); 

; 
WITH cte 
     AS (SELECT DISTINCT salarymonth 
         FROM   tblsalary) 
SELECT @cols = Stuff((SELECT N', ' 
                             + Quotename(Cast(salarymonth AS VARCHAR(20))) 
                      FROM   cte 
                      ORDER  BY salarymonth DESC 
                      FOR xml path('')), 1, 2, N'') 

PRINT @cols 

SET @query = 'SELECT EmployeeCode,EmployeeName, ' 
             + @cols 
             + ' from              (                 select EmployeeCode                     , EmployeeName                     , Basic                     , GradePay                     , SalaryMonth                                                         from tblSalary            ) x             pivot              (                  max(Basic)                  for SalaryMonth in (' 
             + @cols + ')             ) p ' 

EXECUTE(@query)

harishgg1 · December 5, 2019, 9:40am

hi ashu

people here ... have to work on the SQL

they need the data like i created data script

its very irritating to create the data script !!!!
people here help on the SQL part ..

all new people who come to this forum ... dont know ( most of them )
that they have to provide the data in data script
in ready script

ashu · December 5, 2019, 10:44am

Sorry ..
Thank u... so much.
I try this query but using this we can convert only basic into month...
by using this is it possible at one time convert basic and grade pay...

harishgg1 · December 5, 2019, 10:52am

hi

i got till here now ... i added grade pay also

please check

please click arrow tothe left for SQL script

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max); 

; 
WITH cte 
     AS (SELECT DISTINCT salarymonth 
         FROM   tblsalary) 
SELECT @cols = Stuff((SELECT N', ' 
                             + Quotename(Cast(salarymonth AS VARCHAR(20))) 
                      FROM   cte 
                      ORDER  BY salarymonth DESC 
                      FOR xml path('')), 1, 2, N'') 

PRINT @cols 


drop table ##abc 

SET @query = 'SELECT EmployeeCode,EmployeeName, ' 
             + @cols 
             + ' into ##abc from              (                 select EmployeeCode                     , EmployeeName                     , Basic                     , GradePay                     , SalaryMonth                                                         from tblSalary            ) x             pivot              (                  max(Basic)                  for SalaryMonth in (' 
             + @cols + ')             ) p ' 

EXECUTE(@query) 

DECLARE @query1 AS NVARCHAR(max); 


drop table ##def 

SET @query1 = 'SELECT EmployeeCode,EmployeeName, ' 
             + @cols 
             + ' into ##def from              
             (                 select EmployeeCode                     , 
             EmployeeName                     ,  GradePay                     , SalaryMonth                                                         from tblSalary            ) o             pivot              (                  max(GradePay)                  for SalaryMonth in (' 
             + @cols + ')             ) z ' 

print @query1
EXECUTE(@query1) 
GO 


select * from  ##abc a join ##def b on a.employeecode = b.employeecode and a.employeename = b.employeename

ashu · December 5, 2019, 11:09am

Thank u so much..

ashu · December 5, 2019, 11:42am

Hi...
Please help me how to minimize query execution time.
My query salary Generate of employee is just like 500 to 600 line. All calculation done in this query. But this query require 40 to 50 second execution. So can u give me suggestion to optimize time.

harishgg1 · December 5, 2019, 12:18pm

Add indexes ..

harishgg1 · December 5, 2019, 2:38pm

or split the query into smaller bits using temp tables !!!!

example

select * from ( select * from table a join table b on a.id = b.id ) x
join ( select * from table x join table c on a.id = b.id join table c on a.id = c.id ) y
on x.id= y.id

select * into #temp1 from table a join table b on a.id = b.id
select * into #temp2 from table x join table c on a.id = b.id join table c on a.id = c.id

select * from #temp1 x
join #temp2 y on x.id= y.id

yosiasz · December 5, 2019, 5:17pm

basically, you cannot get output just like following image. SQL does not do merged column headers.

harishgg1 · December 5, 2019, 5:34pm

You cannot get it directly

But by some massaging
You can .
It will be a bit of work

yosiasz · December 5, 2019, 5:35pm

select EmployeeCode, EmployeeName,GradePay, [May-19], [Jun-19], [Jul-19], [Aug-19] 
from
(
select EmployeeCode, EmployeeName, SalaryMonth, GradePay, Basic
  From tblSalary
) p
PIVOT 
(
	SUM (Basic)
	FOR SalaryMonth in ([May-19], [Jun-19], [Jul-19], [Aug-19])
) as pvt

ashu · December 6, 2019, 3:57am

Thank u.

But my Month row is not fix like may-19 , june-19, If we select 4 Month then basic and DA show like 4 Month if we select 6 month then 6 Month .....

EX : Acadamic year=2019-20 and select Month as per choice (12 Month in list box)
Then Report show as per selection of Month

ashu · December 6, 2019, 4:20am

Thank u ...

Yes i used temp table ... just like..
if OBJECT_ID('tempdb..#Emp')is not null
begin
drop table #Emp
end
Select * into #Emp from (
SELECT EmployeeCode, EmployeeId,DesignationId,DepartmentId,EmployeeCategoryId,DateofConfirmation,DateofBirth,
DateOfJoining,DateOfReteriment,Gender,WorkingStatus
FROM tblEmployeeMaster ) as a

if OBJECT_ID('tempdb..#EmpPay1')is not null
				begin
					drop table #EmpPay1
				end	 
				Select * into #EmpPay1  from (
				SELECT EmployeeCode, EmployeePayId,PaycommissionId,ApplicableFrom,IncrementFlag,Basic,GradePay,
				row_number() OVER(PARTITION BY  EmployeeCode ORDER BY EmployeePayId desc) AS [rn]
				FROM tblEmployeePay1 
				)as  a where rn=1

Summary

This text will be hidden