asked on

SQL SERVER PIVOT Data Rows do not line up

I created a simple Pivot but i'm having trouble lining up my rows to display just one row instead of 2 rows. the Pivot Sum seems to alternate into a different row. Some of the information lines up into one row but I'm having a hard time understanding the cause of the anomalies. In my test data, for some reason company num 92322 will display what i'm trying to explain. I tried using SUM, MAX, MIN and it causes the same issue.

IF Object_Id('TempDb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp
(
location    Varchar(20),
locName     Varchar(50),
companynum   int,
TaxTable    Varchar(50),
Exemption Decimal(18,4)
)

INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B07','EWR',4959,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B07','EWR',55791,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B07','EWR',74113,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B10','DCA',92322,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B10','DCA',93322,'CountySalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B10','DCA',54994,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B10','DCA',54994,'CountySalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B10','DCA',55710,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('B10','DCA',55710,'CountySalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('I70','ISM',74113,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('I70','ISM',74113,'CitySalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('I70','ISM',74113,'CountySalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('I70','ISM',4892,'StateSalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('I70','ISM',4892,'CitySalesTax',0.0000)
INSERT INTO #Temp (location,locName,companynum,TaxTable,Exemption) VALUES ('I70','ISM',4892,'CountySalesTax',0.0000)

SELECT DISTINCT location,locName,companynum,
    ISNULL(Cast([StateSalesTax] AS Varchar(10)),'') AS [StateSalesTax],
    ISNULL(Cast([CountySalesTax] AS Varchar(10)),'') AS [StateSalesTax],
    ISNULL(Cast([CitySalesTax] AS Varchar(10)),'') AS [StateSalesTax]
FROM #Temp    
Pivot(SUM(Exemption)
    FOR TaxTable IN ([StateSalesTax],[CountySalesTax],[CitySalesTax])) as PivotTable

Open in new window

ASKER CERTIFIED SOLUTION