CODEWITHSUNDEEP
sql-serverpivot
Pradeep
Pradeep

Reputation: 189

Pivot Table With Same Column Name in Sql Server

I Have Table Called TaxMaster,I Need to convert Rows into columns so I am using pivot concept in sql server 2008.

I Need sum of column percentage for Column Name VAT with TaxId=1 and for Column Name VAT with TaxId=3 distinctly but I am getting error as The column VAT was specified multiple times for pvt

TaxId          Name          Code      Percentage
  1            VAT            VAT         10   
  2           Exempted        EXE         20
  3             VAT           VAT          5

My Query:

select  Name,VAT,Exempted,VAT 
from 
(
  select distinct Name, Percentage, TaxId, Code 
  from Masters.TaxMaster
) up 
pivot 
(
   sum(percentage) 
   for Code in(VAT,Exempted,Zero,NonVAT,VAT)
) as pvt

Expected Output:

Name      Code      VAT      Exempted      VAT1
VAT        VAT      10         0              0
Exempted   EXE       0         20             0
VAT        VAT       0          0             5

Upvotes: 0

Views: 4171

Answers (2)

Pradeep
Pradeep

Reputation: 189

I Got the Solution for This Pivot Concept

My Query:

select Name
      ,[VAT] as 'VAT'
      ,[EXE] as 'Exempted'
      ,[VAT1] as 'VAT1'
  from (SELECT distinct name
              ,percentage
              ,code
              ,TaxId
          FROM Masters.TaxMaster) x
 pivot (sum(percentage) for code in ([EXE],[VAT1],[VAT])) pp

Upvotes: 0

Taryn
Taryn

Reputation: 247810

Since you want to pivot create columns from Name that have the same name it might be easier to use an aggregate function with a CASE expression instead of the PIVOT function:

select
  Name,
  Code,
  sum(case when name = 'VAT' and TaxId = 1 then Percentage else 0 end) Vat1,
  sum(case when name = 'Exempted' then Percentage else 0 end) Exempted,
  sum(case when name = 'Zero' then Percentage else 0 end) Zero,
  sum(case when name = 'NonVat' then Percentage else 0 end) NonVat,
  sum(case when name = 'VAT' and TaxId = 3 then Percentage else 0 end) Vat3
from TaxMaster
group by name, code;

See SQL Fiddle with Demo.

If you want to use the PIVOT function, then I would concatenate the TaxId for each Name= 'VAT" first so you have different column names:

select name = nm,
  code, 
  vat1, 
  exempted,
  zero,
  nonvat,
  vat3
from
(
  select nm = name, 
    name = case 
              when name = 'VAT' then name+cast(taxid as varchar(10))
              else name 
          end,
    code, percentage
  from TaxMaster
) d
pivot
(
  sum(percentage)
  for name in (Vat1, exempted, zero, nonvat, vat3)
) p;

See SQL Fiddle with Demo

Both generate a result of:

|     NAME | CODE | VAT1 | EXEMPTED | ZERO | NONVAT | VAT3 |
|----------|------|------|----------|------|--------|------|
| Exempted |  EXE |    0 |       20 |    0 |      0 |    0 |
|      VAT |  VAT |   10 |        0 |    0 |      0 |    5 |

Upvotes: 2

Related Questions