September 17, 2011 at 8:38 am
Greetings Experts,
I need a miracle of some sort.
I have the following query:
SELECT a.deptcode, c.deptname, em.check, em.cash,a.empl_first,a.empl_last, e.[e_mail],a.employee_id
FROM ctable c,[e_mail] e,Aempl a,EMamts em WHERE c.divisioncode = em.divisioncode
AND a.empid = e.empid
AND e.empid = em.empid
AND em.empid = '"&empid&"'
AND (isnull(em.check, 0)<> 0
OR isnull(em.cash, 0)<> 0)
which returns these data:
DeptCode DeptName Check Cash FirstName LastName EmpId
1540 IT 0 12 John Doe 19765507
1540 HR 0 13 John Doe 19765507
1540 Communications 1 0 John Doe 19765507
1540 Mental Health 11 0 John Doe 19765507
That is the correct resutl. There are a maximum of 5 rows that could be returned.
What we would like to have, if possible, is to make each row a column so that the final result looks like this:
Name: John Doe
DeptCode: 1540
EmpId: 19765507
__________________________________________________________________________________________________
IT $12.00
HR $13.00
Communications $1.00
Mental Health $11.00
__________________________________________________________________________________________________
Total $$37.00
I tried using some front end tool but could not figure it out.
Thanks for your assistance
September 17, 2011 at 8:58 am
Hi simflex
to help us help you...please post table creation / insert data scripts that will provide a representative sample of the data...and allow us to use the query you have already posted.
getting rows to columns can be done, (search for PIVOT/Crosstab) though this can often be done in the "front end"....what "front end tool" are you using?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2011 at 9:02 am
deleted....think I may have misunderstood your requirements :ermm:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2011 at 1:17 pm
Hi J,
Do you still what you asked for?
Thanks for your response.
September 20, 2011 at 8:09 am
That looks more like a report than a query. Presentation should be done at the client and not when pulling the data. This would be a pretty simple report in SSRS. Just need a section header and detail sections. If you just want it on screen I would suggest parsing your data at the presentation layer. You already have the query to get your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply