SQLTeam.com | Weblogs | Forums

Sql query help

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)

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.

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

1 Like

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

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

by using this i want output just like

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

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)

1 Like

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

1 Like

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...

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

1 Like

Thank u so much..

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.

Add indexes ..

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

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

You cannot get it directly

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

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

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

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