SQLTeam.com | Weblogs | Forums

Pivot Table (Change Values as Column in Excel)


#1

Dear Friends,

I have the following tables;

  1. Tbl_Employee - Store the details of Employees

    CREATE TABLE [dbo].[Tbl_Employee](
    [Emp_Code] nvarchar NULL,
    [Emp_Name] nvarchar NULL,
    [PAN] nvarchar NULL,
    [Place_Office] nvarchar NULL,
    [Bank_Name] nvarchar NULL,
    [IFSC] nvarchar NULL,
    [Acc_No] nvarchar NULL,
    [Sal_Cat] nvarchar NULL,
    [Pay_Level] [int] NULL
    ) ON [PRIMARY]

  2. Tbl_Head - Master Table for Salary Heads

CREATE TABLE [dbo].[Tbl_Head](
[Head_ID] [int] NULL,
[Head_Name] nvarchar NULL,
[Description] nvarchar NULL
) ON [PRIMARY]

  1. Tbl_FSE - Store the earnings against each head

    CREATE TABLE [dbo].[Tbl_FSE](
    [sle_id] [int] NULL,
    [Emp_Code] nvarchar NULL,
    [Head_ID] [int] NULL,
    [FSE_Amount] [numeric](18, 2) NULL,
    [ETerm] [int] NULL,
    [ESeparator] varchar NULL,
    [ESettlement] [int] NULL,
    [Sal_Month] [date] NULL
    ) ON [PRIMARY]

  2. Tbl_FSD - Store the deductions against each head

    CREATE TABLE [dbo].[Tbl_FSD](
    [sld_id] [int] NULL,
    [Emp_Code] nvarchar NULL,
    [Head_ID] [int] NULL,
    [FSD_Amount] [numeric](18, 2) NULL,
    [Term] [int] NULL,
    [Separator] varchar NULL,
    [Settlement] [int] NULL,
    [Sal_Month] [date] NULL
    ) ON [PRIMARY]

  3. Tbl_FSED - Store Netpay against Net Payable Head

    CREATE TABLE [dbo].[Tbl_FSED](
    [sled_id] [int] NULL,
    [Emp_Code] nvarchar NULL,
    [Head_ID] [int] NULL,
    [FSED_Amount] [numeric](18, 2) NULL,
    [Sal_Month] [date] NULL
    ) ON [PRIMARY]

I had store the values in tables as follows;

Query for extracting the above values;

select EMPCODE Emp_Code,EMP_NAME,SALMONTH,FSEHN,FSE_Amount,FSDHN,FSD_Amount,FSEDHN,FSED_Amount from 
(select coalesce(E.Sal_Month, D.Sal_Month, ED.Sal_Month) As SALMONTH,
Coalesce(E.emp_code, D.emp_code, ED.emp_code) As EMPCODE,h1.Head_Name FSEHN,E.FSE_Amount,
h2.Head_Name FSDHN,D.fsd_amount,h3.Head_Name FSEDHN,
ED.fsed_amount From tbl_fse E left join Tbl_Head h1 on E.Head_ID=h1.Head_ID Full Join tbl_fsd D On E.sle_id = D.sld_id 
and E.Emp_Code=D.Emp_Code and E.Sal_Month=D.Sal_Month left join Tbl_Head h2 on D.Head_ID=h2.Head_ID Full Join tbl_fsed ED 
On E.sle_id = ED.sled_id And D.sld_id = ED.sled_id and ED.Emp_Code=D.Emp_Code and ED.Sal_Month=D.Sal_Month 
left join Tbl_Head h3 on ED.Head_ID=h3.Head_ID) fd join Tbl_Employee te on fd.EMPCODE=te.Emp_Code

In the tables FSEHN, FSDHN,FSEDHN columns storing the Head Names. The heads need to come in Excel as title.

FSE_Amount,FSD_Amount,FSED_Amount storing the head values. The amount need to be come under the head name.

I want the output like as follows;



Problem is Tbl_Employee have one row value but Tbl_FSE,Tbl_FSD,Tbl_FSED tables having multiple rows. The rows value need to be title of the column in excel file.

I hope that i had clarified.

Thanks & Regards,


#2

Can you provide data inserts for each of the tables as well as your expected outcome?