MS SQL Server/Parsing out a field into 3 columns in sql server 2008
Afternoon David, Hope your day is going well. I am using sql server 2008 and I have a query that is pulling deduction information for three deductions and I am trying to get each deduction in a separate column if possible. Currently the deductions come out in one column. I have attached a sample of the current results. Here is the sql that I am using.
eplastname+', '+epfirstname as Name,
DedCode as DedCd, --each deduction needs to be in a separate column
DeeAmount as DedAmount
INNER JOIN eperson as eperson ON eperson.ePflxideb = ebase.Ebflxid and eperson.epflxid =
(Select TOP 1 EP1.epflxid from eperson as ep1 where ep1.epflxideb = ebase.ebflxid order by epdatebeg desc)
Left JOIN EDeduct as EDeduct ON ebase.Ebflxid = ededuct.DeeFlxIDEb and deeDateEnd IS NULL and ededuct.DeeFlxIDEb =
(Select TOP 1 dee1.DeeFlxIDEb from EDeduct as dee1 where dee1.DeeFlxIDEb = ebase.ebflxid order by DeeDateBeg desc)
Left JOIN Deduction as Deduction ON EDeduct.DeeFlxIDDed = deduction.dedflxid and deduction.dedflxid =
(Select TOP 1 ded1.DedFlxID from Deduction as ded1 where ded1.DedFlxID = EDeduct.DeeFlxIDDed order by DedDateBeg desc)
Where DedCode IN ('401K','401KA','ROTH')
Any suggestions would be greatly appreciated
Sorry for the delay... this one's tricky. At first I was thinking PIVOT, but that's not quite what you're going for.
One thing you left un-stated in your example is what to how to show "unfilled" spaces. So, for instance, assume Billy Joel also had a ROTH deduction. Obviously that would require a 3rd pair of columns for him, but since this is tabular that means John Smith would also have a 3rd pair... and what would display there?
The best I've got, so far, is the following, which isn't QUITE what you described, but perhaps you can use it, or use it as a starting point. (Note I didn't have your full table structure, so I just made a temp table #x with 3 columns... I assume you can extrapolate from there into your environment):
SELECT COALESCE(D401K.nm, D401KA.nm, DROTH.nm),
ISNULL(D401K.DedCD, ''), D401K.DedAmount,
ISNULL(D401KA.DedCD, ''), D401KA.DedAmount,
ISNULL(DROTH.DedCD, ''), DROTH.DedAmount
(Select nm, dedcd, dedamount from #x where dedcd = '401K') AS D401K
FULL JOIN (Select nm, dedcd, dedamount from #x where dedcd = '401KA') AS D401KA
ON D401K.nm = D401KA.nm
FULL JOIN (Select nm, dedcd, dedamount from #x where dedcd = 'ROTH') AS DROTH
ON D401K.nm = DROTH.nm
The biggest issue when comparing it to your desired results is that everybody's 401K will be in the 1st pair, and the 401KA in the 2nd pair, and the ROTH in the 3rd pair.
Also note that I've converted NULL (missing) DedCd values to an empty string. For consistency, you probably want to do the same to the NULL DedAmounts, but that would require converting each DedAmount using CAST, and I didn't want to get to far down this rabbit hole without knowing if this is even a viable approach.
I fully anticipate a followup question, or two.