Solved

T-SQL :  "Collapsing"

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in SSIS while executing  - Potential data loss 4 31
export sql results to csv 6 39
Present Absent from working date rage 11 36
Re-appearing SQL Server Agent jobs 7 28
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

791 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