Solved

T-SQL :  "Collapsing"

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

803 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