July 13, 2017 at 11:52 am
Let's say my data is being retrieve from the same table but I want it to display it as such
NY Joe 75 CodeA 100 NBC
Chi Kelly 50 CodeD 50 ABC
NO Sue 25 CodeB 30 FOX
LA John 75 CodeC 15 HBO
TableA has the 4 columns, Place(which is the first column), Name (2nd column), Values( which is column 3 & 5) And CodeType which is column( 4 & 6)
My question is, do I need to used the case statement in order to get theCodeType column to break out into two columns .
How can I go about achieving this?
July 13, 2017 at 12:23 pm
Doesn't seem to me like you need a CASE statement but rather use some string functions. If you want a specific example please post test data but I'll give you a sample on how you can split the one column into two. Again this is assuming a very specific format since I don't know what your actual data looks like.
DECLARE @myTable TABLE (CodeType VARCHAR(10))
INSERT INTO @myTable
VALUES ('CodeA NBC'), ('CodeD ABC'), ('CodeB FOX'), ('CodeC HBO')
SELECT
CodeType,
SUBSTRING(CodeType, 1, (CHARINDEX(' ', CodeType))) AS Code,
SUBSTRING(CodeType, CHARINDEX(' ', CodeType), LEN(CodeType)) AS Network
FROM
@myTable
Cheers,
July 13, 2017 at 1:14 pm
I see were you going but the problem with your example it's basically repeating the code, Network will be the same as llcode but with one character missing.
attach is some sample data. I'm trying to get llcode to have "CCOST" & "CGS" and another column to have just "CFEES".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply