Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

avoid self join Expand / Collapse
Author
Message
Posted Saturday, March 8, 2014 1:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 53, Visits: 1,097
i would like to do display results as below. I can achieve results by self joining back, is there any other way we can achieve
o/p should be
batchid empsal paiddate empsal1 paiddate1
2 100 7/7/2013 600 7/7/2013
3 400 7/8/2013 500 7/7/2013
1 100 7/9/2013
5 100 7/10/2013


Sample code

IF OBJECT_ID('tempdb..#tempemp') IS NOT NULL
DROP TABLE #tempemp

CREATE TABLE #tempemp (
employeeid INT identity(1, 1)
,batchid INT
,empsal FLOAT
,paiddate DATETIME
)

INSERT INTO #tempemp
VALUES (
2
,100
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
3
,400
,'2013-06-07'
)

INSERT INTO #tempemp
VALUES (
2
,600
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
3
,500
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
1
,100
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
5
,100
,'2013-07-07'
)

SELECT *
FROM #tempemp


select employeeid
,batchid
,empsal
,paiddate,
ROW_NUMBER()

OVER(PARTITION BY batchid ORDER BY paiddate) as row_num
from #tempemp

Post #1548994
Posted Saturday, March 8, 2014 2:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
mxy (3/8/2014)
i would like to do display results as below. I can achieve results by self joining back, is there any other way we can achieve
o/p should be
batchid empsal paiddate empsal1 paiddate1
2 100 7/7/2013 600 7/7/2013
3 400 7/8/2013 500 7/7/2013
1 100 7/9/2013
5 100 7/10/2013


Sample code

IF OBJECT_ID('tempdb..#tempemp') IS NOT NULL
DROP TABLE #tempemp

CREATE TABLE #tempemp (
employeeid INT identity(1, 1)
,batchid INT
,empsal FLOAT
,paiddate DATETIME
)

INSERT INTO #tempemp
VALUES (
2
,100
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
3
,400
,'2013-06-07'
)

INSERT INTO #tempemp
VALUES (
2
,600
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
3
,500
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
1
,100
,'2013-07-07'
)

INSERT INTO #tempemp
VALUES (
5
,100
,'2013-07-07'
)

SELECT *
FROM #tempemp


select employeeid
,batchid
,empsal
,paiddate,
ROW_NUMBER()

OVER(PARTITION BY batchid ORDER BY paiddate) as row_num
from #tempemp



It would appear that you want 2 columns per employee per batch. Is there a maximum number of employees per batch that you know of? Even SQL Server has it's limits as to the number of columns it can display.

I'd also like to know what this is for, please. I can't see it being useful for anything to have it in this particular format.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1549001
Posted Saturday, March 8, 2014 3:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 53, Visits: 1,097
max would be two column per employee. its a report they want to view payments and dates of each employee in that format.

Post #1549003
Posted Saturday, March 8, 2014 11:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
mxy (3/8/2014)
max would be two column per employee. its a report they want to view payments and dates of each employee in that format.



I know but, how may employees per batch?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1549026
Posted Sunday, March 9, 2014 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 53, Visits: 1,097
not more than 50
Post #1549052
Posted Sunday, March 9, 2014 6:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
No self JOIN required if you do it like this:

SELECT batchid
,empsal=MAX(CASE WHEN rn=1 THEN empsal END)
,paiddate=MAX(CASE WHEN rn=1 THEN paiddate END)
,empsal=MAX(CASE WHEN rn=0 THEN empsal END)
,paiddate=MAX(CASE WHEN rn=0 THEN paiddate END)
FROM
(
SELECT employeeid, batchid, empsal, paiddate
,rn=ROW_NUMBER() OVER (PARTITION BY batchid ORDER BY paiddate)%2
,grp=(ROW_NUMBER() OVER (PARTITION BY batchid ORDER BY paiddate)-1)/2
FROM #tempemp
) a
GROUP BY batchid, grp;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1549085
Posted Wednesday, March 12, 2014 11:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 53, Visits: 1,097
Thanks for you reply that works perfect!

in case if we have 10 payments (empsal, empsal1..empsal9) and dates paiddate1 through 10 what we need to do?
Post #1550357
Posted Wednesday, March 12, 2014 6:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
mxy (3/12/2014)
Thanks for you reply that works perfect!

in case if we have 10 payments (empsal, empsal1..empsal9) and dates paiddate1 through 10 what we need to do?


Did you try my solution on that case? I believe it works, assuming you always want to group the multiple salaries/payments into 2 columns.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1550480
Posted Wednesday, March 12, 2014 6:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
dwain.c (3/12/2014)
mxy (3/12/2014)
Thanks for you reply that works perfect!

in case if we have 10 payments (empsal, empsal1..empsal9) and dates paiddate1 through 10 what we need to do?


Did you try my solution on that case? I believe it works, assuming you always want to group the multiple salaries/payments into 2 columns.


I believe the OP is looking for a dynamic CROSS TAB of the column pairs.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1550488
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse