SQL = Pivot a set of columns

I need to pivot this:


select ca.column_name,ca.friendly_name
   
from cadoc_crm..tClientCustomFields t
cross apply (
    values
         (cast(ic_lSTaxCCorp as sql_variant), 'Tax C Corp')
                    ) ca (friendly_name, column_name)


Basically the output would be

ic_ISTaxCCorp    Tax C Corp

Where column 1 is the actual column name and column 2 is the friendlyname aka 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.

Pawan KumarDatabase ExpertCommented:
Hi Robb
Can u pls provide few input rows and expected output
0
Robb HillSenior .Net DeveloperAuthor Commented:
Sure:

There are about 20 columns...but this is just a shorter example.  So here are 4 column names.

I would want to return a distinct list of these names along with a friendly name as well.   This will be used in a dropdownlist as  key, value pair where the Label is the friendly name and the value will be the column name.

        ic_lSTaxCCorp
        ic_lSTaxSCorp
        ic_lSTaxFiduciary
        ic_lSTaxForm
0
Robb HillSenior .Net DeveloperAuthor Commented:
i dont want any of hte values from the row...technically just a manipulation of column headers and then being able to alias them as they are pivoted.
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:
I need to create this into a dropdown...please keep in mind the 1st column here were individual columns.   After pivot it should look like this..and the 2nd column is the alias.

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

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
maybe I just do a select into a temp table..then select from temp?
0
Robb HillSenior .Net DeveloperAuthor Commented:
I solved it with this approach...if you have a better way Im all ears....otherwise I will just close the ticket since this part is working for me.




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


  
 
 
)
SELECT * 
FROM   userData -- 
order by friendlyName

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Ok, Few pointers which will help us ..

1. Use UNION All instead of UNION, UNION is slow as it will distinct the data. In this case it is not required. UNION All just clubs the data from all the selects. Here you have used so many union's that's not good.
2. There is no need to use the column names at the time of CTE declaration.
3. Do not use * instead use column names.
4. Use semicolon before starting the CTE
5. Updated code for you-

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

Open in new window


Output

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

(42 row(s) affected)

Open in new window

0

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:
Thanks so much Pawan.  Your explanations are always so helpful!
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.