SQL Pivot Table Dynamic

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

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

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

SQL pivot table1 into table2 without using dynamic SQL pivot or hardcode query

This is not as efficient (and not as easy to code) as a dynamic pivot. However, it is doable.

It does all need to be dynamic e.g., creating each SQL statement as a string and executing that.

The process involves

• Determine the column names (store in a temporary table)
• Creating the table with the first column only
• Populating that first column
• For each additional column name
  
  • Adding a column to the table (dynamically)
  • Populating that column with data

You haven't specified the database - I'll illustrate the following below using SQL Server/T-SQL.

The following are in this db<>fiddle so you can see what's going on.

CREATE TABLE #ColNames (ColNum int IDENTITY(1,1), ColName nvarchar(100), ColNametxt nvarchar(100));

INSERT INTO #ColNames (ColName, ColNametxt)
    SELECT DISTINCT QUOTENAME(Col1), Col1
    FROM table1;

This will populate the #ColNames table with the values 1, [ABCD], ABCD, 2, [PQST], PQST.

The next step is to create your output table - I'll call it #pvttable

CREATE TABLE #pvttable (col1 nvarchar(100) PRIMARY KEY);

INSERT INTO #pvttable (col1)
    SELECT DISTINCT Col3
    FROM table1;

This creates your table with 1 column (col1) with values XY123 and RT789).

The write your favorite loop (e.g., cursor, while loop). In each step

• Get the next column name
• Add the column to the table
• Update that column with appropriate data

e.g., the following is an illustrative example with your data.

DECLARE @CustomSQL nvarchar(4000);
DECLARE @n int = 1;
DECLARE @ColName nvarchar(100);
DECLARE @ColNametxt nvarchar(100);

SELECT      @ColName = ColName,
            @ColNameTxt = ColNameTxt
    FROM    #ColNames 
    WHERE   ColNum = @n;

WHILE @ColName IS NOT NULL
    BEGIN

    SET @CustomSQL = N'ALTER TABLE #pvttable ADD ' + @ColName + N' nvarchar(100);';
    EXEC (@CustomSQL);

    SET @CustomSQL = 
        N'UPDATE #pvttable SET ' + @Colname + N' = table1.col2'
        + N' FROM #pvttable INNER JOIN table1 ON #pvttable.col1 = table1.col3'
        + N' WHERE table1.col1 = N''' + @ColNametxt + N''';';
    EXEC (@CustomSQL);

    SET @n += 1;

    SET @ColName = NULL;
    SET @ColNametxt = NULL;

    SELECT      @ColName = ColName,
                @ColNameTxt = ColNameTxt
        FROM    #ColNames 
        WHERE   ColNum = @n;

    END;

SELECT * FROM #pvttable;

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

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)

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

--#SET TERMINATOR @
create or replace procedure pivot
(
  in  sel_stmt varchar(4000)
, in  row_cols varchar(200)
, in  col_col  varchar(128)
, in  agg_col  varchar(128)
, in  agg_fn   varchar(10)
, in  tmp_tbl  varchar(128)
, in  null_ind varchar(10)
, out rc       int
, out msg      varchar(128)
, out stmt     varchar(4000)
)
LANGUAGE SQL
DYNAMIC RESULT sets 1
BEGIN
 declare QUOT1        char(1) default '''';
 declare QUOT2        char(1) default '"';
 declare SQLCODE      int default 0;
 declare SQLTYPE_ID   int;
 declare SQLTYPE      varchar(128);
 declare SQLLENGTH    int;
 declare SQLSCALE     int;
 declare SQLNAME_DATA varchar(128);
 declare SQLTYPEF     varchar(128);
 declare col_val      varchar(4000);
 declare apo          varchar(1);
 declare l1 RESULT_set_LOCATOR VARYING;

 declare c2 cursor for s2;
 declare c_out cursor with return for s_out;

 declare EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
   GET DIAGNOSTICS EXCEPTION 1 MSG = MESSAGE_TEXT;
   set RC = SQLCODE;
 END;

 set col_col=upper(col_col);
 set agg_col=upper(agg_col);
 -- insert result of select statement into temp table
 set stmt = 'describe '||sel_stmt;
 call SYSPROC.ADMIN_CMD(stmt);
 set stmt = '';
 associate result set locator (l1)
 with procedure SYSPROC.ADMIN_CMD;
 allocate c1 cursor for result set l1;
 --open c1;
 fetch c1 into SQLTYPE_ID, SQLTYPE, SQLLENGTH, SQLSCALE, SQLNAME_DATA;
 while (SQLCODE!=100) do
   set SQLTYPEF = SQLTYPE
   ||case 
      when SQLTYPE IN ('DECIMAL', 'DECFLOAT', 'CHARACTER', 'VARCHAR') then 
         '('||RTRIM(CHAR(SQLLENGTH))
       ||case when SQLTYPE='DECIMAL' then ','||RTRIM(CHAR(SQLSCALE)) else '' end
       ||')'                                
      else ''
     end;
   if (col_col=SQLNAME_DATA) then
     set apo = 
      case 
       when SQLTYPE in ('DECIMAL', 'DECFLOAT', 'INTEGER', 'SMALLINT', 'BIGINT', 'REAL', 'DOUBLE') then '' 
       else QUOT1 
      end;
   end if;
   set stmt = stmt||', '||SQLNAME_DATA||' '||SQLTYPEF;
   fetch c1 into SQLTYPE_ID, SQLTYPE, SQLLENGTH, SQLSCALE, SQLNAME_DATA;
 end while;
 close c1;
 set stmt = 
   'declare global temporary table '||tmp_tbl||'('||substr(stmt, 3)
 ||') with replace on commit preserve rows not logged';
 execute immediate stmt;
 set stmt = 'insert into '||tmp_tbl||' '||sel_stmt;
 execute immediate stmt;

 -- construct select statement
 set stmt = 'select distinct rtrim(char('||col_col||')) from '||tmp_tbl||' order by 1';
 prepare s2 from stmt;
 set stmt='';
 open c2;
 fetch c2 into col_val;
 while (SQLCODE!=100) do
   set stmt = 
     stmt||', '||agg_fn||'('
   ||'case when '||col_col||' '
   ||case when col_val is null then 'IS NULL' else ('='||apo||replace(col_val, QUOT1, QUOT1||QUOT1)||apo) end
   ||' then '||agg_col||' end) as '||QUOT2||coalesce(replace(col_val, QUOT2, QUOT2||QUOT2), null_ind)||QUOT2;
   fetch c2 into col_val;
 end while;
 close c2;
 -- add to the select statement groups
 set row_cols = nullif(row_cols, '');
 set stmt = 
   'select '||case when row_cols is not null then row_cols||',' else coalesce(row_cols, '') end
 ||substr(stmt, 2)||' from '||tmp_tbl||' '
 ||case when row_cols is not null then ('group by '||row_cols||' order by '||row_cols) else '' end;
 -- execute this statement
 prepare s_out from stmt;
 open c_out;
END@

from 
( 
    -- your original query
    select E_ID, Full_name, Dept, fy_week, fy_rev -- you missed the fy_week & fy_rev here
    from   my_sample_table 

    -- add the following few lines : union all & select query
    union all

    select E_ID, Full_name, Dept, fy_week = ''Total'', fy_rev = sum(fy_rev)
    from   my_sample_table 
    group by E_ID, Full_name, Dept
) x

select  @cols = '[Total],' + @cols

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(fy_week) y
            FROM my_sample_table z
            ORDER BY y asc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

SET @cols = '[Total],' + @cols           -- added this line

-- added cte query
SET @query  = '
    ; with cte as
    (
            select  E_ID, Full_name, Dept, 
                    fy_week = convert(varchar(10), fy_week), fy_rev 
            from    my_sample_table 

            union all

            select  E_ID, Full_name, Dept, 
                    fy_week = ''Total'', fy_rev = sum(fy_rev) 
            from    my_sample_table 
            group by E_ID, Full_name, Dept
    )
    SELECT E_ID, Full_name, Dept, ' 
        + @cols + ' 
    from 
    ( 
        select  E_ID, Full_name, Dept, fy_week, fy_rev 
        from    cte

        -- the following is for row wise total
        union all

        select  E_ID = 99, Full_name = ''Total'', Dept = '''', fy_week, sum(fy_rev) 
        from    cte 
        group by fy_week
    ) x
    pivot 
    ( 
        Sum(fy_rev) 
        for fy_week in (' + @cols + ')
    ) p '

-- print out to validate
print   @query
execute(@query)