April 28, 2009 at 11:52 am
I have 3 tables. First table contains employee information called Employees. Second table contains the earning codes called Erncd. Third table has the actual earnings called PayData. I want to join all 3 tables but display all the earning codes on one row. For example...
name check_dt reg_ern ot_ern
John 1/1/2009 200.00 100.00
Is there a way to loop through the Erncd table, fetch the earnings code, and pass it to the PayData table? The reason I want to loop is that the earning codes get added/inactivated every so often so I dont want to hard-code them in the script.
April 28, 2009 at 12:49 pm
If you can provide the create scripts for the tables, I'm sure we can help you on this. Just need to see the table structures.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 1:09 pm
select 'John' as name ,'1234' as emplid
into #employees
select 'REG' as erncd, 'A' as status into #erncd
union
select 'OT' as erncd, 'A' as status
union
select 'OTH' as erncd, 'I' as status
select '1234' as emplid, 'REG' as erncd, 200 as earnings into #paydata
union
select '1234' as emplid, 'OT' as erncd, 100 as earnings
union
select '1234' as emplid, 'OTH' as erncd, 50 as earnings
What I want to do is join #employees to #paydata and return all the earnings in one row per employee, but I only want to show where #erncd.status = 'A' for active. The result would be...
NAME EMPLID OT REG
John 1234 100 200
April 28, 2009 at 1:24 pm
To do that, you're going to have to use dynamic SQL. It's going to be relatively complex to do.
http://www.Simple-Talk.com has a good article on dynamic pivots in T-SQL (that's what you're doing here, a dynamic pivot).
What I recommend every time this comes up, is query the data with T-SQL, and have the front-end application do the pivoting. Excel or Reporting Services or Crystal Reports, all of those pivot better and more usefully than T-SQL does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 1:39 pm
Thanks, I've already built it in SRS as a matrix report and it works beautifully, its just the other limitations of SRS that frustrate me and eventually the end user. Main example is if you have multiple row groupings, it wont allow to un-merge the header so we can have actual column headers. The workaround is place a table within that text box, but when you export to excel, you get the dreaded message "Data Regions within table/matrix cells are ignored." in that textbox.
I was hoping there would be an easy way to do this via SQL so all the column headers will carry over into SRS.
April 28, 2009 at 1:42 pm
Read the article on Simple-Talk. It's not horribly complex, but it's not "only a child can do it"-simple, either. See if it makes sense to you.
It can be done in SQL Server. It's just not as flexible and so on as doing it in something designed for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 10:33 pm
is250sp (4/28/2009)
I have 3 tables. First table contains employee information called Employees. Second table contains the earning codes called Erncd. Third table has the actual earnings called PayData. I want to join all 3 tables but display all the earning codes on one row. For example...name check_dt reg_ern ot_ern
John 1/1/2009 200.00 100.00
Is there a way to loop through the Erncd table, fetch the earnings code, and pass it to the PayData table? The reason I want to loop is that the earning codes get added/inactivated every so often so I dont want to hard-code them in the script.
It sounds like some report to be sent out of database.
I don't believe the output of this will be used inside of the database for further processing.
Am I right?
_____________
Code for TallyGenerator
April 29, 2009 at 8:14 am
Sergiy (4/28/2009)
is250sp (4/28/2009)
I have 3 tables. First table contains employee information called Employees. Second table contains the earning codes called Erncd. Third table has the actual earnings called PayData. I want to join all 3 tables but display all the earning codes on one row. For example...name check_dt reg_ern ot_ern
John 1/1/2009 200.00 100.00
Is there a way to loop through the Erncd table, fetch the earnings code, and pass it to the PayData table? The reason I want to loop is that the earning codes get added/inactivated every so often so I dont want to hard-code them in the script.
It sounds like some report to be sent out of database.
I don't believe the output of this will be used inside of the database for further processing.
Am I right?
correct, do you have any other ideas?
April 29, 2009 at 5:43 pm
is250sp (4/29/2009)
correct, do you have any other ideas?
The idea is simple.
For each user you build a report string using one of "concatenation functions" posted on this forum.
It would be a nice coincidence if you'd be using for concatenation the same delimiter which is meant to be used for your report.
Then when you post those 2 columns (User and ReportString) in your report it will apeear as "dynamically allocated columns".
Nobody could tell the difference. 😉
And no hardcoding or dynamic SQL required.
😎
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply