Solved

T-SQL :  "Collapsing"

Posted on 2013-10-30
3
471 Views
Last Modified: 2013-11-01
Hello:

Below is my code and attached is a screenshot showing the results of running my code.  I need the results "collapsed" such that the fields "SUPERLAST" and "SUPERFIRST" are not blank and that the other 12 fields of data are not blank.

How can I do that?

Thanks!

TBSupport


select EMPID_I, DATEOFLASTREVIEW_I, REVIEWSETUPNAME_I, REVIEWEDBY_I, APPROVEDBY_I,
DATEOFNEXTREVIEW_I, LASTNAME, FRSTNAME, DEPRTMNT, SUPERVISORCODE_I, DSCRIPTN, SUPERVISOR,
SUPERLAST, SUPERFIRST
from (
select TOP 100 PERCENT * FROM
(
select ZACCU..RV010121.EMPID_I, ZACCU..RV010121.DATEOFLASTREVIEW_I, ZACCU..RV010121.REVIEWSETUPNAME_I, ZACCU..RV010121.REVIEWEDBY_I, ZACCU..RV010121.APPROVEDBY_I, ZACCU..RV010121.DATEOFNEXTREVIEW_I,
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME, ZACCU..UPR00100.DEPRTMNT, ZACCU..UPR00100.SUPERVISORCODE_I,
ZACCU..UPR40300.DSCRIPTN, ZACCU..UPR41700.SUPERVISOR, '' as [SUPERLAST], '' AS [SUPERFIRST]
from ZACCU..RV010121  
 INNER JOIN ZACCU..UPR00100
 on
ZACCU..UPR00100.EMPLOYID = ZACCU..RV010121.EMPID_I
 INNER JOIN ZACCU..UPR40300
 on
 ZACCU..UPR40300.DEPRTMNT = ZACCU..UPR00100.DEPRTMNT
 INNER JOIN ZACCU..UPR41700
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
UNION ALL
SELECT ZACCU..RV010121.EMPID_I, '', '', '', '', '', '', '', '', '', '', '',
 ZACCU..UPR00100.LASTNAME as [SUPERLAST], ZACCU..UPR00100.FRSTNAME AS [SUPERFIRST]
 from ZACCU..RV010121  
 INNER JOIN ZACCU..UPR00100
 on
ZACCU..UPR00100.EMPLOYID = ZACCU..RV010121.EMPID_I
 INNER JOIN ZACCU..UPR41700
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
)
as test1
GROUP BY EMPID_I, DATEOFLASTREVIEW_I, REVIEWSETUPNAME_I, REVIEWEDBY_I, APPROVEDBY_I,
DATEOFNEXTREVIEW_I, LASTNAME, FRSTNAME, DEPRTMNT, SUPERVISORCODE_I, DSCRIPTN, SUPERVISOR,
SUPERLAST, SUPERFIRST)
as test2
Collapse.docx
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39612741
The SUPERLAST and SUPERFIRST names are nothing but your LASTNAME and FRSTNAME.
Do you want to display SUPERLAST and SUPERFIRST in the same row of the data record and delete the default (all NULL) record which has SUPERLAST and SUPERFIRST?
Not sure if you are looking for this. let me know.
SELECT DISTINCT ZACCU..RV010121.EMPID_I, 
                ZACCU..RV010121.DATEOFLASTREVIEW_I, 
                ZACCU..RV010121.REVIEWSETUPNAME_I, 
                ZACCU..RV010121.REVIEWEDBY_I, 
                ZACCU..RV010121.APPROVEDBY_I, 
                ZACCU..RV010121.DATEOFNEXTREVIEW_I, 
                ZACCU..UPR00100.LASTNAME, 
                ZACCU..UPR00100.FRSTNAME, 
                ZACCU..UPR00100.DEPRTMNT, 
                ZACCU..UPR00100.SUPERVISORCODE_I, 
                ZACCU..UPR40300.DSCRIPTN, 
                ZACCU..UPR41700.SUPERVISOR, 
                ZACCU..UPR00100.LASTNAME AS [SUPERLAST], 
                ZACCU..UPR00100.FRSTNAME AS [SUPERFIRST] 
  FROM ZACCU..RV010121 
       INNER JOIN ZACCU..UPR00100 
               ON ZACCU..UPR00100.EMPLOYID = ZACCU..RV010121.EMPID_I 
       INNER JOIN ZACCU..UPR40300 
               ON ZACCU..UPR40300.DEPRTMNT = ZACCU..UPR00100.DEPRTMNT 
       INNER JOIN ZACCU..UPR41700 
               ON ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I 

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
ID: 39612770
Hi Sharath_123:

That's exactly what I want to do.  So, if you could let me know how, that would be great!  :)

By the way, the SUPERLAST and SUPERFIRST fields are not the same "exactly" as the UPR00100.LASTNAME and UPR00100.FRSTNAME fields.  

SUPERLAST and SUPERFIRST are the names of the supervisors and for the supervisor IDs found in the UPR41700 table.  The reason I need those names is because UPR41700 does not provide them.  UPR00100 does.

Update:  It appears as though, per the result of my previous case from today, that I found what I wanted through a different script that I composed.

But, for my future reference, I would like to know how to "collapse" data like this through T-SQL.  I have always had a hard time with this.

Anyway, below is the code that I composed and concluded my other case with:

select ZACCU..RV010121.EMPID_I, ZACCU..RV010121.DATEOFLASTREVIEW_I, ZACCU..RV010121.REVIEWSETUPNAME_I, ZACCU..RV010121.REVIEWEDBY_I, ZACCU..RV010121.APPROVEDBY_I, ZACCU..RV010121.DATEOFNEXTREVIEW_I,
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME, ZACCU..UPR00100.DEPRTMNT, ZACCU..UPR00100.SUPERVISORCODE_I,
ZACCU..UPR40300.DSCRIPTN, ZACCU..UPR41700.SUPERVISOR,
--(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700
-- INNER JOIN ZACCU..UPR00100
-- on
-- ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
--WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
b.LASTNAME AS [SUPERLAST],  
-- (SELECT ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
-- INNER JOIN ZACCU..UPR00100
-- on
-- ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
--WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
b.FRSTNAME AS [SUPERFIRST]
from ZACCU..RV010121  
 INNER JOIN ZACCU..UPR00100
 on
ZACCU..UPR00100.EMPLOYID = ZACCU..RV010121.EMPID_I
 INNER JOIN ZACCU..UPR40300
 on
 ZACCU..UPR40300.DEPRTMNT = ZACCU..UPR00100.DEPRTMNT
 INNER JOIN ZACCU..UPR41700
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
 INNER JOIN ZACCU..UPR00100 b
 on ZACCU..UPR41700.EMPLOYID = b.EMPLOYID


TBSupport
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39612880
I think you figured out your problem. But if you really want further help, sample data is much needed.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question