How to use pivot?

Question

I know its very dumb question, but i really cant get logic of the pivot table.

I have this SQL code

SELECT AddressBase.RegionName,
       ObjectBase.ObjectID,
       YEAR(ObjectBase.CreatedOn) AS Year
FROM ObjectBase INNER JOIN
       AddressBase ON AddressBase.ObjectID = ObjectBase.ObjectID AND 
                      AddressBase.DeleteStateCode = 0 AND 
                      ObjectBase.DeleteStateCode = 0

With this

But i need use pivot to get result ,

How?

I tried something like this, but it, obviously, cant work.

SELECT AddressBase.RegionName, [2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
  SELECT ObjectBase.ObjectID,
         YEAR(ObjectBase.CreatedOn) yr,
         AddressBase.RegionName 
  FROM ObjectBase INNER JOIN 
         AddressBase ON AddressBase.ObjectID = ObjectBase.ObjectID AND 
                        AddressBase.DeleteStateCode = 0 AND 
                        ObjectBase.DeleteStateCode = 0
) d
pivot
(
  COUNT(ObjectBase.ObjectID)
  for yr in ([2011], [2012], [2013], [2014], [2015], [2016], [2017])
) piv

1:

Have you had a look at the [PIVOT](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15) operator? What about it didn't you understand? Though this looks like a dynamic pivot: [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) Honestly, this is something for your presentation/reporting layer, not the RDBMS. I would **strongly** suggest rethinking your requirements if you "must" do it in SQL. — Thom A, Oct 27 '21 at 09:51
What isn't working about your attempt? Please supply sample data and expected output — Charlieface, Oct 27 '21 at 10:04

score 1 · Accepted Answer · answered Oct 27 '21 at 10:55

Dynamically PIVOT use i think its work... I just tried From showing your output Image

 DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));
    
    INSERT INTO @ColumnsTable ([ColumnName])
    SELECT  DISTINCT  '[' + CONVERT(VARCHAR(48),  YEAR(ObjectBase.CreatedOn)) + ']'
    FROM  ObjectBase INNER JOIN 
             AddressBase ON AddressBase.ObjectID = ObjectBase.ObjectID AND 
                            AddressBase.DeleteStateCode = 0 AND 
                            ObjectBase.DeleteStateCode = 0;
    
    DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);
    
    SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR(50), [ColumnName])
                                       FROM   @ColumnsTable
                                       FOR XML PATH('')), 1, 2, ''));
    SET @SQL = 'SELECT RegionName,' +@PivotColumns +'
    FROM   (
    SELECT ObjectBase.ObjectID as ObjectID,
             YEAR(ObjectBase.CreatedOn) yr,
             AddressBase.RegionName as RegionName
      FROM ObjectBase INNER JOIN 
             AddressBase ON AddressBase.ObjectID = ObjectBase.ObjectID AND 
                            AddressBase.DeleteStateCode = 0 AND 
                            ObjectBase.DeleteStateCode = 0) AS t 
           PIVOT (MAX([ObjectID])
                 FOR [yr] IN (' + @PivotColumns + ') ) AS p   order by RegionName ';
    EXEC(@SQL);

Not exactly like this, but this helped me a lot. Thanks for STUFF and FOR XML PATH functions, didnt know about it. — Algrandeth, Nov 03 '21 at 07:05

How to use pivot?

1 Answers1