SQL turning one row into multiple rows

Question

I have a SQL table with many columns, and in particular a set of columns containing codes. For each of the code columns there are also corresponding information columns pertaining to each of the code columns. I need to be able to turn one record into multiple returned records (i.e. one record for each code). This is an old table with legacy data along with software code, and it would be very difficult to normalize the table (even though it really needs to be).

Here is the table:

CO_ComapnyInformation
                (
                 ProjectID
                ,SubContractor_OrgID
                ,BudGet
                ,ContractDays
                ,ContractCode
                ,ContractCode1
                ,ContractCode2
                ,ContractCode3
                ,ContractCode4
                ,ContractCode5
                ,ContractCodeAmt
                ,ContractCode1Amt
                ,ContractCode2Amt
                ,ContractCode3Amt
                ,ContractCode4Amt
                ,ContractCode5Amt
                ,ContractStartDate
                ,ContractCodeBudget
                ,ContractCodeBudget1
                ,ContractCodeBudget2
                ,ContractCodeBudget3
                ,ContractCodeBudget4
                ,ContractCodeBudget5
                ,ContractCodeAdjust
                ,ContractCodeAdjust1
                ,ContractCodeAdjust2
                ,ContractCodeAdjust3
                ,ContractCodeAdjust4
                ,ContractCodeAdjust5
                ,ContractCodeUncommit
                ,ContractCodeUncommit1
                ,ContractCodeUncommit2
                ,ContractCodeUncommit3
                ,ContractCodeUncommit4
                ,ContractCodeUncommit5
                ,ContractCodeSaveOR
                ,ContractCodeSaveOR1
                ,ContractCodeSaveOR2
                ,ContractCodeSaveOR3
                ,ContractCodeSaveOR4
                ,ContractCodeSaveOR5
                )

basically I need the returned recordset to look like this:

Contract Code | Contract Budget | Contract Adjust | Contract Uncommit | Contract SaveOR | Pro_ID | ...
ContractCode    10.00             xxxx
ContractCode1
ContractCode2

etc...

Only if the COntractCode columns have data in them.

How do I do this?

Answer 1

You should be able to do this with a single select from your base table by making use of SQLServer's pivot/unpivot functionality - something like:

select ProjectID, 
       Contract_Code, 
       max(Budget) Budget, 
       max(Adjust) Adjust, 
       max(Uncommit) Uncommit, 
       max(SaveOR) SaveOR
from
(select ProjectID, vals, left(cols,1,13) Contract_Code, right(cols,len(cols)-13) col_head from
 (select ProjectID,
         ContractCodeBudget  ContractCode_Budget,
         ContractCodeBudget1 ContractCode1Budget,
         ContractCodeBudget2 ContractCode2Budget,
         ContractCodeBudget3 ContractCode3Budget,
         ContractCodeBudget4 ContractCode4Budget,
         ContractCodeBudget5 ContractCode5Budget,
         ContractCodeAdjust  ContractCode_Adjust,
         ContractCodeAdjust1 ContractCode1Adjust,
         ContractCodeAdjust2 ContractCode2Adjust,
         ContractCodeAdjust3 ContractCode3Adjust,
         ContractCodeAdjust4 ContractCode4Adjust,
         ContractCodeAdjust5 ContractCode5Adjust,
         ContractCodeUncommit  ContractCode_Uncommit,
         ContractCodeUncommit1 ContractCode1Uncommit,
         ContractCodeUncommit2 ContractCode2Uncommit,
         ContractCodeUncommit3 ContractCode3Uncommit,
         ContractCodeUncommit4 ContractCode4Uncommit,
         ContractCodeUncommit5 ContractCode5Uncommit,
         ContractCodeSaveOR  ContractCode_SaveOR,
         ContractCodeSaveOR1 ContractCode1SaveOR,
         ContractCodeSaveOR2 ContractCode2SaveOR,
         ContractCodeSaveOR3 ContractCode3SaveOR,
         ContractCodeSaveOR4 ContractCode4SaveOR,
         ContractCodeSaveOR5 ContractCode5SaveOR
  from CO_ComapnyInformation) t
 unpivot (vals for cols in (ContractCode_Budget,ContractCode1Budget,ContractCode2Budget,ContractCode3Budget,ContractCode4Budget,ContractCode5Budget,
                            ContractCode_Adjust,ContractCode1Adjust,ContractCode2Adjust,ContractCode3Adjust,ContractCode4Adjust,ContractCode5Adjust,
                            ContractCode_Uncommit,ContractCode1Uncommit,ContractCode2Uncommit,ContractCode3Uncommit,ContractCode4Uncommit,ContractCode5Uncommit,
                            ContractCode_SaveOR,ContractCode1SaveOR,ContractCode2SaveOR,ContractCode3SaveOR,ContractCode4SaveOR,ContractCode5SaveOR
                            )
          ) as t1
) as t2
pivot (max(vals) for col_head in ([Budget],[Adjust],[Uncommit],[SaveOR])) as t3
group by ProjectID, Contract_Code

Answer 2

You can use union all to do it.

select 'ContractCode' as ContractCodeType, ContractCode, ContractCodeBudget, ...
from CO_ComapnyInformation

union all

select 'ContractCode1' as ContractCodeType, ContractCode1, ContractCodeBudget1, ...
from CO_ComapnyInformation

...

Maybe you don't need that first column (ContractCodeType), it's just so you can know from which set of values the row came from.

Ideally though, if those fields store the same type of information, it's better to have a simpler table only with one set of fields. You can start with a view on top of it (that's basically the union code above), and refactor your application in small steps.