Solved

T-SQL :  "Collapsing"

Posted on 2013-10-30
3
459 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
  • 2
3 Comments
 
LVL 40

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 40

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
In or Between 2 44
tempdb latch contention 12 50
Removing SQL Replication from Microsoft SQL Server 2008 R2 2 19
MS SQL Inner Join - Multiple Join Parameters 2 23
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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now