SQL UNPIVOT

How would I turn this data into the following format:

select 

cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]

from
cadoc_crm..tClient c 	
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 
left join tTaskXClient txCli 
	on c.nid = txCli.nClientID

Open in new window



THis would need to look like this:

I would need this to be two columns

one column is the column and the next column is the alias
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
While this is possible using T-SQL you may have problems with the mixture of "data types"  across so many columns being pushed into a single column.

Are there numbers? dates? as well as strings (varchar? nvarchar?)
1
PortletPaulEE Topic AdvisorCommented:
UNTESTED!

This achieves an "unpivot" but does not use that word in the code - it does not need to.

I have cast all values to sql_variant in the hope this avoids type conflicts.
select
    t.id, ca.column_name, ca.column_value
from yourtable t
cross apply (
    values
          (cast(ic_lSTaxCCorp as sql_variant), 'Tax C Corp')
        , (cast(ic_lSTaxSCorp as sql_variant), 'Tax S Corp')
        , (cast(ic_lSTaxFiduciary as sql_variant), 'Tax Fiduciary')
        , (cast(ic_lSTaxForm as sql_variant), 'Tax-Form') 
        , (cast(ic_lSTaxEstate as sql_variant), 'Tax-Estate') 
        , (cast(ic_lSTaxNonProfit as sql_variant), 'Tax-Non-Profit') 
        , (cast(ic_lSTaxPartnership as sql_variant), 'Tax Partnership') 
        , (cast(ic_lSTaxMassBusTrusts as sql_variant), 'Tax-Max Bus Trusts') 
        , (cast(ic_lSTaxPensions as sql_variant), 'Tax-Pensions') 
        , (cast(ic_lSTaxSalesPersPropTax as sql_variant), 'Tax-S/P Prop Tax') 
        , (cast(ic_lSTaxPayroll1099andW2 as sql_variant), 'Tax-Payroll 1099/W-2') 
        , (cast(ic_lSTaxInternational as sql_variant), 'Tax-International') 
        , (cast(ic_lSTaxIndividual as sql_variant), 'Tax-Individual') 
        , (cast(ic_lSTaxScheduleC as sql_variant), 'Tax-Schedule C') 
        , (cast(ic_lSTaxScheduleE as sql_variant), 'Tax-Schedule E') 
        , (cast(ic_lSTaxEstatePlanIndiv as sql_variant), 'Tax-Estate Plan - Indiv') 
        , (cast(ic_lSTaxGiftTaxRelated as sql_variant), 'Tax-Gift Tax/Related') 
        , (cast(ic_lSTaxInternationalIndiv as sql_variant), 'Tax-InternationalIndiv') 
        , (cast(ic_lSBKPWriteup as sql_variant), 'BKP-Writeup') 
        , (cast(ic_lSBKPWriteupMonthly as sql_variant), 'BKP-Writeup-Monthly') 
        , (cast(ic_lSBKPWriteupQuarterly as sql_variant), 'BKP-Writeup-Quarterly') 
        , (cast(ic_lSBKPGrayPay as sql_variant), 'BKP-GrayPay') 
        , (cast(ic_lSBKPOutsourcedAccounting as sql_variant), 'BKP-Outsourced Accounting') 
        , (cast(ic_lSAandAAOP as sql_variant), 'A&A-Agreed Upon Proc') 
        , (cast(ic_lSAandAAudit as sql_variant), 'A&A-Audit') 
        , (cast(ic_lSAandAEBPA as sql_variant), 'A&A-Emp Ben Plan Audit') 
        , (cast(ic_lSAandACompilationMonthly as sql_variant), 'A&A-Compil Monthly') 
        , (cast(ic_lSAandACompilation as sql_variant), 'A&A-Compilation') 
        , (cast(ic_lSAandAOverheadAudit as sql_variant), 'A&A-Overhead Audit') 
        , (cast(ic_lSAandAReview as sql_variant), 'A&A-Review') 
        , (cast(ic_lSAandAInternalAuditing as sql_variant), 'A&A-Internal Auditing')
        , (cast(ic_lSMASAccountingConsultation as sql_variant), 'MAS-Acctng Consult') 
        , (cast(ic_lSMASFuelExchange as sql_variant), 'MAS-FuelExchange') 
        , (cast(ic_lSMASFuelTrack as sql_variant), 'MAS-Fuel Track') 
        , (cast(ic_lSMASMgmtConsultation as sql_variant), 'MAS-Mgmt Consult') 
        , (cast(ic_lSMASLitigationSupport as sql_variant), 'MAS-Litig Support') 
        , (cast(ic_lSMASPersonalFinancialPlanning as sql_variant), 'MAS-Pers Fin Plan') 
        , (cast(ic_lSMASMandAConsulting as sql_variant), 'MAS-M&A Consult') 
        , (cast(ic_cSMcDonaldsServices as sql_variant), 'McDonalds-Compil') -- removed apostrophe
        , (cast(ic_lCSValuationAppraisals as sql_variant), 'CS-Valuation Appraisals') 
        , (cast(ic_cSGEMFinancialPlan as sql_variant), 'GEM Financial Plan') 
        , (cast(ic_cSOtherServices as sql_variant), 'Other Services')
      ) ca (column_value, column_name)
 

Open in new window


If you get errors please don't come back with each one, then the next then the next.

START SMALL e.g.

select
    t.id, ca.column_name, ca.column_value
from yourtable t
cross apply (
    values
          (cast(ic_lSTaxCCorp as sql_variant), 'Tax C Corp')
        , (cast(ic_lSTaxSCorp as sql_variant), 'Tax S Corp')
        , (cast(ic_lSTaxFiduciary as sql_variant), 'Tax Fiduciary')
        , (cast(ic_lSTaxForm as sql_variant), 'Tax-Form') 
        , (cast(ic_lSTaxEstate as sql_variant), 'Tax-Estate') 
      ) ca (column_value, column_name)
 

Open in new window


Does that work?
yes: add a few more into the query, try again
no: remove a couple of lines, try again
repeat
this will help identify any problem columns
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robb HillSenior .Net DeveloperAuthor Commented:
Thank you for the detailed answer!!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Robb HillSenior .Net DeveloperAuthor Commented:
Paul:

i am getting a horrific cartesian on this query.

Any suggestions on how I can eliminate this....what can I provide to you to help me with that....


When you mention "your table t" and apply an id there...

my joins to get this data in a select are this:

from
cadoc_crm..tClient c       
left join cadoc_crm..tClientCustomFields cf
      on c.nid = cf.Clientnid
left join tTaskXClient txCli
      on c.nid = txCli.nClientID



Is the are particular type of value I should pull here....as this is not unique..if I put a where clause on just one of the columns its about 20,000 rows...

and at the point I let the entire thing run..im near 900k.


Here is the query so far:
There where clause can be ignored..I just did that so I could see how bad the cartesian was.

select
     ca.column_name, ca.column_value
from
cadoc_crm..tClient c 	
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 
left join tTaskXClient txCli 
	on c.nid = txCli.nClientID
cross apply (
    values
          (cast(ic_lSTaxCCorp as sql_variant), 'Tax C Corp')
        , (cast(ic_lSTaxSCorp as sql_variant), 'Tax S Corp')
        , (cast(ic_lSTaxFiduciary as sql_variant), 'Tax Fiduciary')
        , (cast(ic_lSTaxForm as sql_variant), 'Tax-Form') 
        , (cast(ic_lSTaxEstate as sql_variant), 'Tax-Estate') 
        , (cast(ic_lSTaxNonProfit as sql_variant), 'Tax-Non-Profit') 
        , (cast(ic_lSTaxPartnership as sql_variant), 'Tax Partnership') 
        , (cast(ic_lSTaxMassBusTrusts as sql_variant), 'Tax-Max Bus Trusts') 
        , (cast(ic_lSTaxPensions as sql_variant), 'Tax-Pensions') 
        , (cast(ic_lSTaxSalesPersPropTax as sql_variant), 'Tax-S/P Prop Tax') 
        , (cast(ic_lSTaxPayroll1099andW2 as sql_variant), 'Tax-Payroll 1099/W-2') 
        , (cast(ic_lSTaxInternational as sql_variant), 'Tax-International') 
        , (cast(ic_lSTaxIndividual as sql_variant), 'Tax-Individual') 
        , (cast(ic_lSTaxScheduleC as sql_variant), 'Tax-Schedule C') 
        , (cast(ic_lSTaxScheduleE as sql_variant), 'Tax-Schedule E') 
        , (cast(ic_lSTaxEstatePlanIndiv as sql_variant), 'Tax-Estate Plan - Indiv') 
        , (cast(ic_lSTaxGiftTaxRelated as sql_variant), 'Tax-Gift Tax/Related') 
        , (cast(ic_lSTaxInternationalIndiv as sql_variant), 'Tax-InternationalIndiv') 
        , (cast(ic_lSBKPWriteup as sql_variant), 'BKP-Writeup') 
        , (cast(ic_lSBKPWriteupMonthly as sql_variant), 'BKP-Writeup-Monthly') 
        , (cast(ic_lSBKPWriteupQuarterly as sql_variant), 'BKP-Writeup-Quarterly') 
        , (cast(ic_lSBKPGrayPay as sql_variant), 'BKP-GrayPay') 
        , (cast(ic_lSBKPOutsourcedAccounting as sql_variant), 'BKP-Outsourced Accounting') 
        , (cast(ic_lSAandAAOP as sql_variant), 'A&A-Agreed Upon Proc') 
        , (cast(ic_lSAandAAudit as sql_variant), 'A&A-Audit') 
        , (cast(ic_lSAandAEBPA as sql_variant), 'A&A-Emp Ben Plan Audit') 
        , (cast(ic_lSAandACompilationMonthly as sql_variant), 'A&A-Compil Monthly') 
        , (cast(ic_lSAandACompilation as sql_variant), 'A&A-Compilation') 
        , (cast(ic_lSAandAOverheadAudit as sql_variant), 'A&A-Overhead Audit') 
        , (cast(ic_lSAandAReview as sql_variant), 'A&A-Review') 
        , (cast(ic_lSAandAInternalAuditing as sql_variant), 'A&A-Internal Auditing')
        , (cast(ic_lSMASAccountingConsultation as sql_variant), 'MAS-Acctng Consult') 
        , (cast(ic_lSMASFuelExchange as sql_variant), 'MAS-FuelExchange') 
        , (cast(ic_lSMASFuelTrack as sql_variant), 'MAS-Fuel Track') 
        , (cast(ic_lSMASMgmtConsultation as sql_variant), 'MAS-Mgmt Consult') 
        , (cast(ic_lSMASLitigationSupport as sql_variant), 'MAS-Litig Support') 
        , (cast(ic_lSMASPersonalFinancialPlanning as sql_variant), 'MAS-Pers Fin Plan') 
        , (cast(ic_lSMASMandAConsulting as sql_variant), 'MAS-M&A Consult') 
        , (cast(ic_cSMcDonaldsServices as sql_variant), 'McDonalds-Compil') -- removed apostrophe
        , (cast(ic_lCSValuationAppraisals as sql_variant), 'CS-Valuation Appraisals') 
        , (cast(ic_cSGEMFinancialPlan as sql_variant), 'GEM Financial Plan') 
        , (cast(ic_cSOtherServices as sql_variant), 'Other Services')
      ) ca (column_value, column_name)

	  where column_name = 'Tax-Form'

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
Robb

take this number:
select count(*)
from cadoc_crm..tClient c       
left join cadoc_crm..tClientCustomFields cf  on c.nid = cf.Clientnid
left join tTaskXClient txCli      on c.nid = txCli.nClientID
and multiply by the number of rows under the values section. ~40

That's what you asked for.

You will notice that in my first query I included     t.id    , you really need something from those tables to help keep the rows "together" otherwise your output will probably be meaningless.

as always: with problems: START SMALL

is this number smaller?
select count(*) from (select 1
from cadoc_crm..tClient c       
left join cadoc_crm..tClientCustomFields cf  on c.nid = cf.Clientnid
left join tTaskXClient txCli      on c.nid = txCli.nClientID ) d

what can you do to reduce the source rows?
0
Robb HillSenior .Net DeveloperAuthor Commented:
well this is bad un normalized table ....so primary keys to not produce me a unique id...I understand your point of small..

A small value yields a small cartesian.

The values field will result in 1, 0,..or bad data
0
Robb HillSenior .Net DeveloperAuthor Commented:
I think uniqueness would have to be made up if thats possible....
0
PortletPaulEE Topic AdvisorCommented:
When unpivoting, even what was unique will become repeated, but that is usually fine because it may be used as a foreign key.

Let's say cadoc_crm..tClient.id = 1
and we have 40 lines to produce for each of those id, so we would see id = 1 40 times in the result. But we now know where each row came from and can link back to that source.

If we push the unpivoted data into a table we could just auto-increment the id of that table, or we ca use row_number() over the result to get a number per row.

I believe you have some thinking to do.

Just because it can be done, does not mean it should be done. I think you may agree with this now.
0
Robb HillSenior .Net DeveloperAuthor Commented:
im doing this just to create a unique list of values ....that will be used in a dropdown list dataset
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.