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?
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Robb HillSenior .Net DeveloperAuthor Commented:
Thank you for the detailed answer!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.