Len

Question

Len

adisql

SSCrazy

Points: 2893
More actions
March 30, 2021 at 3:27 pm

#3863650

Hi,
current table result is below.
select FirstName,LastName,DOB,ID from emptable.
Example sample result:
I would like to display the result in single row like below based on len(id) of DepID\OfficeID column.

Any suggestions.
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Instead of writing a query to do this crosstab would it be possible to go back to whatever code landed you at those 2 rows with different ID's in the same column? That's the correct place to start imo

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

[Edit] This is pretty much the same as Phil's code in the other thread with same name.

with improper_cte(FirstName, LastName, DOB, ID) as (
    select 'Sam', 'Dup', cast('1980-03-12' as date), 34567
    union all
    select 'Sam', 'Dup', cast('1980-03-12' as date), 99995042033804)
select FirstName, LastName, DOB,
       max(case when len(ID)=5 then ID else null end) DeptID,
       max(case when len(ID)>5 then ID else null end) OfficeID
from improper_cte
group by FirstName, LastName, DOB;

FirstNameLastNameDOBDeptIDOfficeID
SamDup1980-03-123456799995042033804

This reply was modified 3 years, 7 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Can you provide some DDL and a script we can run to reproduce your original results?

If not, one approach would be to do a self join on firstname, lastname, and DOB (as those are the only columns we can see) and on LEN(ID)>5 and then ID of that would be the OfficeID. Then you alias ID as DeptID and put a WHERE clause on of having LEN(ID)<=5.

The above assumes that a DeptID will always have 5 characters or less and an OfficeID will always have more than 5 characters.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Why did you repost this? You got at least two answers to your earlier "LEN" post that would work. All you had to do was change the column names and add the DOB column. Did you need help to do that? If so, in the future, please continue it in the same thread rather than starting all over from scratch.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills