split the table

Question

split the table

gururajan

SSCrazy

Points: 2875
More actions
December 2, 2009 at 10:48 pm

#156464

Hi
I have one Employees Details Database
One table named Emp_Dtl
Components Component_flg Amount
------------------------------------------
BASIC Earnings 5000
DA Earnings 5000
HRA Earnings 5000
SPA Earnings 10000
LTA Earnings 5000
PF Deduction 2000
ESI Deduction 100
LOAN Deduction 4000
want to show that table for the following format
Components Component_flg Amt components Component_flg Amt
-----------------------------------------------------------------------
BASIC Earnings 5000 PF Deduction 2000
DA Earnings 5000 ESI Deduction 100
HRA Earnings 5000 LOAN Deduction 4000
SPA Earnings 10000 Null Null Null
LTA Earnings 5000 Null Null Null
Data types
-----------
Component varchar
Component_flg varchar
Amount numeric
Three columns Table Split into 6 Columns Table
How it is possible to split
Regards
Guru:-)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Arshad Kalam SSC Enthusiast Points: 109 More actions · Answer 1

You can use Set the Row Number for each component type by using Row_Number function and these use Full Join to arrange them. Here is the solution.

Declare @Emp_Dtl Table (Component varchar(10), Component_flg varchar(25), Amount numeric(18,2))

Insert Into @Emp_Dtl

Select Component = 'BASIC', Component_flg = 'Earnings', Amount = 5000

Union All

Select 'DA', 'Earnings', 5000

Union All

Select 'HRA', 'Earnings', 5000

Union All

Select 'SPA', 'Earnings', 10000

Union All

Select 'LTA', 'Earnings', 5000

Union All

Select 'PF', 'Deduction', 2000

Union All

Select 'ESI', 'Deduction', 100

Union All

Select 'LOAN', 'Deduction', 4000

;With SpltTable (Component, Component_flg, Amount, RowNum)

As

(

Select Component, Component_flg, Amount, Row_Number() Over (Partition By Component_flg ORder by Component_flg)

From @Emp_Dtl

)

Select E.Component, E.Component_flg, E.Amount, D.Component, D.Component_flg, D.Amount

From

(

Select Component, Component_flg, Amount, RowNum

From SpltTable

Where Component_flg = 'Earnings'

) AS E

Full Join

(

Select Component, Component_flg, Amount, RowNum

From SpltTable

Where Component_flg = 'Deduction'

) AS D

On E.RowNum = D.RowNum

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Where are the rules which dictate what earnings match to what deduction?

BASIC Earnings 5000 PF Deduction 2000

How do these two entities relate?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden