SQL Server Dynamic PIVOT Query

SQL Server dynamic PIVOT query?

Dynamic SQL PIVOT:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

Results:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

SQL Server Dynamic pivot for an unknow number of columns

Try this, It follows the same example mentioned here:Convert Rows to columns using 'Pivot' in SQL Server

--Drop Sample temp Table     

    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    BEGIN
        DROP TABLE #temp2
    END

--create Sample temp Table 

    create Table #temp2
    (
    [name] varchar(255),
    Item varchar(255),
    note varchar(255)
    )

--Insert Sample Data

    insert into #temp2
    values( 'George','Paperclip','Two boxes'),
    ('George','Stapler','blue one'),
    ('George','Stapler','red one'),
    ('George','Desk lamp','No light bulb'),
    ('Mark','Paperclip','One box 2'),
    ('Mark','Paperclip','One box 4'),
    ('Mark','Block Notes','a blue one')

DECLARE @cols AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

--Generate Columns from Data
--Generate Columns from Data

select @cols = STUFF((SELECT ', isnull(' + QUOTENAME(Item)  + ',0) as' +  QUOTENAME(Item)
                    from #temp2
                    group by Item
                    order by Item
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols2 = STUFF((SELECT ', ' + QUOTENAME(Item)  
                    from #temp2
                    group by Item
                    order by Item
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


--Pivot Query
    set @query = 'SELECT [name],' + @cols + ' from 
                 (
                      select [Name], Item, count(*) as xcount
                   from #temp2
                   group by  Name, Item
                ) x
                pivot 
                (
                    sum(xCount)
                    for Item in (' + @cols2+ ')
                ) p '

    execute(@query);

--Drop Sample Temp Table

    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    BEGIN
        DROP TABLE #temp2
    END

Dynamic Pivot Sql Query display all from one table

You can reconstruct the query

SELECT *  
  FROM
  (
     SELECT A.[Phone], A.[CustNo], A.[Name], C.[BrandName], B.[qty]
       FROM [Table-A] AS A 
       LEFT JOIN [Table-B] AS B
         ON A.[CustNo] = B.[CustNo] 
        AND B.[odate] = '2021-04-22'
       LEFT JOIN [Table-C] AS C on C.productid = B.Productid  
      WHERE A.[Route] = 1  
      ) t
    PIVOT 
    (
     MIN([qty]) FOR [BrandName] IN ([Brand-1],[Brand-2],[Brand-3])
    ) AS piv

which contains LEFT JOIN rather than INNER JOIN, and STRING_AGG() function in order to generate the pivoted columns dynamically as in the following code block

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SET @cols = ( SELECT STRING_AGG(QUOTENAME([BrandName]),',') 
                FROM (SELECT DISTINCT [BrandName] 
                        FROM [Table-C] ) C );

SET @query = 
  N'SELECT *  
      FROM
      (
         SELECT A.[Phone], A.[CustNo], A.[Name], C.[BrandName], B.[qty]
           FROM [Table-A] AS A 
           LEFT JOIN [Table-B] AS B
             ON A.[CustNo] = B.[CustNo] 
            AND B.[odate] = ''2021-04-22''
           LEFT JOIN [Table-C] AS C on C.productid = B.Productid  
          WHERE A.[Route] = 1  
          ) t
        PIVOT 
        (
         MIN([qty]) FOR [BrandName] IN (' + @cols + N')
        ) AS piv'

EXEC sp_executesql @query;

Demo

How to add conditions to SQL dynamic pivot query?

Add the where clause like below:

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT DateAndTime, ' + @ColumnName + '
    FROM dbo.FloatTable1
    PIVOT(SUM(Val)
          FOR TagIndex IN (' + @ColumnName + ')) AS PVTTable
WHERE DateAndTime BETWEEN ''VAL1'' AND ''VAL2'' ORDER BY DateAndTime'

HTH!

SQL Query with Dynamic Columns Using Pivot

Using Dynamic Sql

IF OBJECT_ID('tempdb..#TempData', 'U') IS NOT NULL 
DROP TABLE #TempData;

CREATE TABLE #TempData (
    [Site] CHAR(3) NOT NULL,
    Model VARCHAR(30) NOT NULL,
    SomeCount INT NOT NULL DEFAULT(0)
    );
INSERT #TempData (Site, Model, SomeCount) VALUES
    ('AAA', 'ProLiant DL380 G7', 1), 
    ('AAA', 'OptiPlex 790', 500), 
    ('BBB', 'OptiPlex 780', 80),
    ('CCC', 'OptiPlex 790', 23);

Declare @DynamicCol nvarchar(max),@DynamicColNull nvarchar(max)
        ,@Sql nvarchar(max)

SELECT @DynamicColNull=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME(Model),','+'''0'''+') As '+QUOTENAME(Model)
                        FROM #TempData FOR XML PATH ('')),1,2,'')

SELECT @DynamicCol=STUFF((SELECT DISTINCT ', '+QUOTENAME(Model) FROM #TempData FOR XML PATH ('')),1,2,'')

SET @Sql='SELECT [Site], '+@DynamicColNull+' From
            (   
            SELECT * from #TempData
            )
            AS Src
            PIVOT
            (
            MAX(SomeCount) FOR [Model] IN ('+@DynamicCol+')
            )AS Pvt'
PRINT @Sql
EXEC(@Sql)

Result

Site    OptiPlex 780    OptiPlex 790    ProLiant DL380 G7
AAA         0               500             1
BBB         80              0               0
CCC         0               23              0

Combination of dynamic pivot and static pivot in SQL Server

I have used your static pivot part of the query as the source of dynamic pivot. Create two sets of dynamic pivot column list. One for pivoting and the another with Coalesce() to select pivoted columns (to convert null into 0). If there is no categcount for any category then that category has been replaced with null (case when). Two more aliases for Category and SumCatCount have been created since those were used in pivot condition.

Here goes your answer:

 create table #temp
 (
     Place nvarchar(20),
     State nvarchar(20),
     Category nvarchar(20) null,
     CategCount int null,
     MCount int null,
     Buys int,
     Cost int
 )
     
 insert into #temp values ('London', 'UK', 'Old', 3, NULL, 22, 4.50)
 insert into #temp values ('London', 'UK', 'Old', 6, 5, 3, 22.00)
 insert into #temp values ('Brussels', 'BE', 'Young', 2, NULL, 4, 3.50)
 insert into #temp values ('Brussels', 'BE', 'M', NULL, 5, 12, 1.20)
 insert into #temp values ('Brussels', 'BE', 'M', NULL, 2, 1, 1.20)
 
 
 DECLARE @cols  AS NVARCHAR(MAX)='';
 DECLARE @query AS NVARCHAR(MAX)='';
 DECLARE @colsForSelect  AS NVARCHAR(MAX)='';
 
 SET @cols = STUFF((SELECT distinct ',' + quotename(category)
             FROM #temp  where CategCount is not null 
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,'')
 
        
 SET @colsForSelect = STUFF((SELECT distinct ',' + ' Coalesce('+quotename(category)+',0) '+ quotename(category)
             FROM #temp  where CategCount is not null 
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 
 
 --select (@cols) as bm
 
 set @query = 
 'SELECT count,place,state,(case when OldSumCatCount >0 then OldCategory else null end)Category,SumMCount, ' + @colsForSelect + ',SumCost,SumBuys from 
 (
     select count(*) as count, place, state,category OldCategory, category,    
     sum(ISNULL(MCount, 0)) as SumMCount,
    sum(ISNULL(CategCount, 0)) as OldSumCatCount,
    sum(ISNULL(CategCount, 0)) as SumCatCount,
     sum(Cost) as SumCost,
    sum(ISNULL(buys, 0)) as SumBuys
     
 from #temp
 group by place , state, category
 ) src
 pivot 
 (
     max(SumCatCount) for Category in (' + @cols + ')
 ) piv
 order by place desc,count'
 
 execute(@query)
 GO

exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd

create table ExternalPersonRelationTable
(
  PersonId int,
  SubjectCode int
);

insert into ExternalPersonRelationTable (PersonId, SubjectCode) values
(4187, 3),
(4187, 278),
(4429, 3),
(4429, 4),
(4463, 99),
(4464, 174),
(4464, 175);

select piv.SubjectCode as Code,
       isnull(convert(bit, piv.[4187]), 0) as [4187],
       isnull(convert(bit, piv.[4429]), 0) as [4429],
       isnull(convert(bit, piv.[4463]), 0) as [4463],
       isnull(convert(bit, piv.[4464]), 0) as [4464]
from ExternalPersonRelationTable epr
pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;

-- constuct lists
declare @fieldList nvarchar(1000);
declare @pivotList nvarchar(1000);

with cte as
(
  select epr.PersonId
  from ExternalPersonRelationTable epr
  group by epr.PersonId
)
select @fieldList = string_agg('isnull(convert(bit, piv.['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']), 0) as ['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ', ') within group (order by cte.PersonId),
       @pivotList = string_agg('['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ',') within group (order by cte.PersonId)
from cte;

-- validate lists
select @fieldList as FieldList;
select @pivotList as PivotList;

-- construct query
declare @query nvarchar(3000) = 'select piv.SubjectCode as Code, '
                              + @fieldList
                              + 'from ExternalPersonRelationTable epr '
                              + 'pivot (max(epr.PersonId) for epr.PersonId in ('
                              + @pivotList
                              + ')) piv;';
      
-- validate query
select @query as Query;

-- run query
exec sp_executesql @query;

Code  4187   4429   4463   4464
----  -----  -----  -----  -----
  3   True   True   False  False
  4   False  True   False  False
 99   False  False  True   False
174   False  False  False  True
175   False  False  False  True
278   True   False  False  False