Solved

T-SQL :  "Collapsing"

Posted on 2013-10-30
3
467 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

763 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