March 8, 2014 at 1:12 pm
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
batchidempsalpaiddateempsal1paiddate1
2 100 7/7/2013 6007/7/2013
3 400 7/8/2013 5007/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
March 8, 2014 at 2:50 pm
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 achieveo/p should be
batchidempsalpaiddateempsal1paiddate1
2 100 7/7/2013 6007/7/2013
3 400 7/8/2013 5007/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
Change is inevitable... Change for the better is not.
March 8, 2014 at 3:24 pm
max would be two column per employee. its a report they want to view payments and dates of each employee in that format.
March 8, 2014 at 11:31 pm
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
Change is inevitable... Change for the better is not.
March 9, 2014 at 7:26 am
not more than 50
March 9, 2014 at 6:15 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2014 at 11:18 am
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?
March 12, 2014 at 6:06 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2014 at 6:36 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply