Issue Using PIVOT in SQL

Question

I am trying to use POVIT while looking at this example This Here is the Code:

    CREATE DATABASE DEMO
GO
 
USE DEMO
GO
 
-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot
 
CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)
GO
 
-- Inerting some sample records
INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
GO

And here is the PIVOT selection

        -- Getting table data
    SELECT
    ItemCode, 
    ItemName, 
    ItemColour
    from TblPivot
    GO
     
    -- Getting agreegated data using Pivot and converted rows to column
    SELECT
    *
     FROM
     (
        SELECT
     ItemCode, 
     ItemName, 
     ItemColour
        FROM TblPivot
     ) AS P
    PIVOT
    (
      Count(ItemName) FOR ItemColour IN (Red, Blue, Green)// Here is the Issue 
where it knows what words to give it like Red,Blue,Green what I want is to use 
what ever the ItemColur it could be 100s What ever you get
 from the data base use that for the `IN(ItemColur)`  
    ) AS pv
    GO

It keeps saying that The column name "ItemColur" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. How can I get this working with that I even tried to make a Temp Table didn't work

Answer 1

This is the code for dynamic PIVOT:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(ItemColour)
  FROM (SELECT distinct p.ItemColour FROM dbo.TblPivot AS p
  ) AS x;
SET @sql = N'
SELECT itemcode, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.itemcode, p.ItemName, p.ItemColour
   FROM dbo.TblPivot AS p
) AS j
PIVOT
(
  COUNT(ItemName) FOR ItemColour IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

The output for above query looks like this: