SQL - Dynamic Pivot - How to fix?

Question

So I found a dynamic pivot code and was trying to apply it for my needs, but I get the error:

Msg 8156, Level 16, State 1, Line 52 The column ':Id' was specified multiple times for 'PVTTable'. Msg 4104, Level 16, State 1, Line 45 The multi-part identifier "T2.PartNumber" could not be bound.

Here is my code:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Designation <en>])
FROM (SELECT DISTINCT [Designation <en>] FROM [HellaPim].[dbo].[MasterData.Countries]) AS Country

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT T2.[PartNumber] as ArtNr, ' + @ColumnName + '
    FROM [HellaPim].[dbo].[Article.ArticleCarPark] T1 WITH (NOLOCK)
            LEFT OUTER JOIN [HellaPim].[dbo].[Article.Articles] T2 WITH (NOLOCK)
                ON T2.[ArticleID]=T1.[Article:Link]
            LEFT OUTER JOIN [HellaPim].[dbo].[MasterData.Countries] T3 WITH (NOLOCK)
                ON T3.[CountryCode]=T1.[Country:Link]
    PIVOT(SUM(T1.[PassengerCars]) 
          FOR T3.[Designation <en>] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

The pivot code works OK on a static table, the problem occurred when I tried to link the table to others. It is true [:Id] is present in all my 3 tables but I don't use them.

Any hints on what I am missing?

Thx

Can you provide some sample data (csv format is ok with a header row) from each of your tables and a desired result. Your use of pivot doesn't look correct at first glance as you haven't aliased a source query to run the pivot on (see learn.microsoft.com/en-us/sql/t-sql/queries/…), but hard to tell without seeing more of the data. — TJB
– TJB, Commented Mar 22, 2019 at 11:30

Sreenu131 · Accepted Answer · 2019-03-26 07:18:06Z

1

i just made some changes to your Dynamic sql query ,it might helps You

   DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
   DECLARE @ColumnName AS NVARCHAR(MAX)


    SET @DynamicPivotQuery = 
              N'SELECT ArtNr, ' + @ColumnName + ' 
                FROM
                (
                SELECT T2.[PartNumber] as ArtNr,
                       T3.[Designation <en>]
                FROM [HellaPim].[dbo].[Article.ArticleCarPark] T1 WITH (NOLOCK)
                    LEFT OUTER JOIN [HellaPim].[dbo].[Article.Articles] T2 WITH (NOLOCK)
                        ON T2.[ArticleID]=T1.[Article:Link]
                    LEFT OUTER JOIN [HellaPim].[dbo].[MasterData.Countries] T3 WITH (NOLOCK)
                        ON T3.[CountryCode]=T1.[Country:Link]
                ) AS Src
                PIVOT
                (
                SUM(Src.[PassengerCars]) FOR Src.[Designation <en>] IN (' + @ColumnName + ')
                ) AS PVTTable'
EXEC sp_executesql @DynamicPivotQuery

edited Mar 26, 2019 at 7:18

answered Mar 22, 2019 at 13:14

Sreenu131

2,5361 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Isar Over a year ago

Hi, unfortunately: Msg 107, Level 15, State 1, Line 14 The column prefix 'T1' does not match with a table name or alias name used in the query.

Alex Isar Over a year ago

Hi again, new msg: Msg 207, Level 16, State 1, Line 14 Invalid column name 'PassengerCars'. I posted below sample data of all 3 tables, maybe this helps. THX!

Alex Isar · Accepted Answer · 2019-03-25 07:56:30Z

Sorry for the late reply, here is the print on my original syntax:

Msg 8156, Level 16, State 1, Line 8
The column ':Id' was specified multiple times for 'PVTTable'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T2.PartNumber" could not be bound.
SELECT T2.[PartNumber] as ArtNr, [(Democratic Republic of the) Congo],[(Republic of) Côte d'Ivoire],[Afghanistan],[Aland Islands],[Albania],[Algeria],[American Samoa],[Andorra],[Angola],[Anguilla],[Antarctica],[Antigua],[Argentina],[Armenia],[Aruba],[Aserbaidjan],[Australia],[Austria],[Bahamas],[Bahrain],[Bangladesh],[Barbados],[Belgium],[Belize],[Belorussia],[Benin],[Bermuda],[Bhutan],[Bolivia],[Bosnia-Herzegovina],[Botswana],[Bouvet Islands],[Brazil],[British Indian Ocean Territory],[British Virgin Islands],[Brunei],[Bulgaria],[Burkina Faso],[Burma],[Burundi],[California],[Cambodia],[Cameroon],[Canada],[Cape Verde],[Casakhstan],[Cayman Islands],[Central African Republic],[Chad],[Chile],[China],[Christmas Island],[Cocos (Keeling) Islands],[Columbia],[Comores],[Congo],[Cook Islands],[Costa Rica],[Croatia],[Cuba],[Cyprus],[Czech Republic],[Czechoslovakia],[Democratic People's Republic of Korea],[Denmark],[Djibouti],[Dominica],[Dominican Republic],[Ecuador],[Egypt],[El Salvador],[Equatorial Guinea],[Eritrea],[Estonia],[Ethiopia],[Faeroes],[Falkland Islands],[Fidji],[Finland],[France],[French Guyana],[French Polynesia],[French Southern and Antarctic Territories],[Gabon],[Gambia],[Georgia],[Germany],[Ghana],[Gibraltar],[Great Britain],[Greece],[Greenland],[Grenada],[Guadeloupe],[Guam],[Guatemala],[Guernsey],[Guinea],[Guinea-Bissau],[Guyana],[Haiti],[Heard and McDonald Islands],[Honduras],[Hong Kong],[Hungary],[Iceland],[India],[Indonesia],[Iran],[Iraq],[Ireland],[Isle of Man],[Israel],[Italy],[Jamaica],[Japan],[Jersey],[Jordan],[Kenya],[Kirgistan],[Kiribati],[Korea (Republic)],[Kuwait],[Laos],[Latvia],[Lebanon],[Lesotho],[Liberia],[Libya],[Liechtenstein],[Lithuania],[Luxemburg],[Macau],[Macedonia],[Madagascar],[Malawi],[Malaysia],[Maldives],[Mali],[Malta],[Marshall Islands],[Martinique],[Mauritania],[Mauritius],[Mayotte],[Mexico],[Micronesia],[Monaco],[Mongolia],[Montenegro],[Montserrat],[Morocco],[Mozambique],[Namibia],[Nauru],[Nepal],[Netherlands],[Netherlands Antilles],[New Caledonia],[New Zealand],[Nicaragua],[Niger],[Nigeria],[Niue],[Norfolk Island],[Northern Mariana Islands],[Norway],[Oman],[Pakistan],[Palau],[Palestine],[Panama],[Papua New Guinea],[Paraguay],[Peru],[Philippines],[Pitcairn Islands],[Poland],[Portugal],[Puerto Rico],[Qatar],[Republic of Moldova],[Reunion],[Rumania],[Russia],[Rwanda],[Saint Barthélemy],[Saint Helena],[Saint Kitts and Nevis],[Saint Martin],[Saint Pierre and Miquelon],[Salomon Islands],[Samoa Islands],[San Marino],[Santa Lucia],[Sao Tome and Principe],[Saudi Arabia],[Senegal],[Serbia],[Serbia and Montenegro],[Seychelles],[Sierra Leone],[Singapore],[Slovenia],[Slowakian Republic],[Somalia],[South Africa],[South Georgia and the South Sandwich Islands],[South Sudan],[Sowjet Union (former)],[Spain],[Sri Lanka],[St. Vincent and Grenadines],[Sudan],[Surinam],[Svalbard and Jan Mayen],[Swaziland],[Sweden],[Switzerland],[Syria],[Taiwan],[Tajikistan],[Tanzania],[Thailand],[Timor-Leste],[Togo],[Tokelau],[Tonga],[Trinidad and Tobago],[Tunisia],[Turkey],[Turkmenistan],[Turks and Caicos Islands],[Tuvalu],[Uganda],[Ukraine],[United Arab Emirates],[United States Minor Outlying Islands],[United States of America],[United States Virgin Islands],[Uruguay],[Usbekistan],[Vanuatu],[Vatican City],[Venezuela],[Vietnam],[Wallis and Futuna],[Western Sahara],[Yemen (Democratic Peoples' Republic)],[Yugoslawia],[Zambia],[Zimbabwe]
    FROM [HellaPim].[dbo].[Article.ArticleCarPark] T1 WITH (NOLOCK)
            LEFT OUTER JOIN [HellaPim].[dbo].[Article.Articles] T2 WITH (NOLOCK)
                ON T2.[ArticleID]=T1.[Article:Link]
            LEFT OUTER JOIN [HellaPim].[dbo].[MasterData.Countries] T3 WITH (NOLOCK)
                ON T3.[CountryCode]=T1.[Country:Link]
    PIVOT(SUM(T1.[PassengerCars]) 
          FOR T3.[Designation <en>] IN ([(Democratic Republic of the) Congo],[(Republic of) Côte d'Ivoire],[Afghanistan],[Aland Islands],[Albania],[Algeria],[American Samoa],[Andorra],[Angola],[Anguilla],[Antarctica],[Anti

Also here is the sample data of each tab in CSV: data sample from the 3 tables

Thanks!

Collectives™ on Stack Overflow

SQL - Dynamic Pivot - How to fix?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related