Pivot Function on Multiple Tables and Dynamic Columns in SQL

Question

I have 3 tables in Table 2 we have columns with columnName Field they can grow Dynamically at that time we have just 5 columns for Each CTypeId they can be 6 or 10 etc. In Table3 we have the column values.

For example AccountManager From Table 2 have value in Table 3 Jack / Kate similarly other columns and their values are

ColumnName |  Values
Channel    |  PS
StartDate  |  06/03/2017

I want Result Like this

I have tried using Pivot Function with the following query:

Declare @Columns nvarchar(max) 
Declare @a nvarchar(max)
Set @Columns = (select STUFF((select ',' + '[' + Convert(varchar(200), ColumnName) + ']' from CharityTypeInformationDynamicFields FOR XML PATH('')), 1,1, ''))

    Declare @sql nvarchar(max) 
       = 'Select * 
          from
          (select cd.Id, cd.Value, ci.ColumnName 
           from Table3 cd 
           Inner Join Table2         ci 
               on ci.Id = cd.DynamicFieldID
          ) as s 
          Pivot(MAX(Value) ForColumnName IN ('+@columns+')) as pvt'
    Select @sql

But the query gives the result:

What do I need to change to achieve my desired output?

Answer 1

There are a few issues that you need to solve in order to get the result you desire. But before trying a dynamic sql version of a query I'd always recommend that you try get your final result by writing a hard-coded or static version first. This allows you to get the desired result without bugs and then convert it to dynamic sql as your final query.

First, let's get your table structures and sample data into a reusable script. It appears that you only need table2 and table3 to get your end result:

create table #table2
(
  id int,
  ctypeid int,
  columnname varchar(50)
)

insert into #table2
values
(1, 20, 'Account Manager'), (2, 20, 'Channel'),
(3, 20, 'Start Date'), (4, 20, 'End Date'),
(5, 20, 'Gross Annual'), (6, 6, 'Account Manager'),
(7, 6, 'Channel'), (8, 6, 'Start Date'),
(9, 6, 'End Date'), (10, 6, 'Gross Annual');

create table #table3
(
  id int,
  table2id int,
  value varchar(50)
)

insert into #table3
values
(1, 1, 'Jack / Kate'), (2, 2, 'PS'), (3, 3, '06/03/2017'), 
(4, 4, '07/03/2017'), (5, 5, '2500'), (6, 6, 'Ollie'), 
(7, 7, 'D2D'), (8, 8, '06/03/2017'), (9, 9, '06/03/2017'), 
(10, 10, '5232'), (11, 1, 'Jack'), (12, 2, 'PSP'), 
(13, 3, '06/03/2017'), (14, 4, '07/03/2017'), (15, 5, '7000'),
(16, 1, 'Jack Sparrow'), (17, 2, 'PS Sparrow'), (1, 3, '06/03/2017'), 
(19, 4, '07/03/2017'), (20, 5, '3000'), (21, 6, 'John'), 
(22, 7, 'JEDF'), (23, 8, '06/03/2017'), (24, 9, '06/03/2017'), 
(25, 10, '5232');

Next, you need to write your PIVOT query. Your final result only includes the values from 3 columns CTypeId, Value, and ColumnName, so the start of your query PIVOT would be:

select 
  CTypeId,
  [Account Manager], [Channel], [Start Date], 
  [End Date], [Gross Annual]
from 
(
   select ci.CTypeId, cd.Value, ci.ColumnName
   from #Table3 cd 
   Inner Join #Table2 ci 
     on ci.Id = cd.Table2Id
) d
pivot
(
  max(Value)
  for ColumnName in ([Account Manager], [Channel], [Start Date], 
                      [End Date], [Gross Annual])
) piv

Demo. But since you're aggregating string values in the Value column, you will only return one row for each CTypeId:

+---------+-----------------+---------+------------+------------+---------------+
| CTypeId | Account Manager | Channel | Start Date |  End Date  | Gross Annual  |
+---------+-----------------+---------+------------+------------+---------------+
|       6 | Ollie           | JEDF    | 06/03/2017 | 06/03/2017 |          5232 |
|      20 | Jack Sparrow    | PSP     | 06/03/2017 | 07/03/2017 |          7000 |
+---------+-----------------+---------+------------+------------+---------------+

which is not what you want, so you need to do something to allow for multiple rows. If you look at a sample of the data that is returned by the subquery:

+---------+-------------+------------------+
| CTypeId |    Value    | ColumnName       |
+---------+-------------+------------------+
|      20 | Jack / Kate | Account Manager  |
|      20 | PS          | Channel          |
|      20 | 06/03/2017  | Start Date       |
|      20 | 07/03/2017  | End Date         |
|      20 | 2500        | Gross Annual     |
|       6 | Ollie       | Account Manager  |
|       6 | D2D         | Channel          |
|       6 | 06/03/2017  | Start Date       |
|       6 | 06/03/2017  | End Date         |
|       6 | 5232        | Gross Annual     |
+---------+-------------+------------------+

You'll see that you have unique data over a combination of CTypeId and ColumnName values, so you can create a unique row number using the windowing function row_number in your subquery which can be used to uniquely group the data for a pivot. By changing the above PIVOT code to:

select 
  CTypeId,
  [Account Manager], [Channel], [Start Date], 
  [End Date], [Gross Annual]
from 
(
   select ci.CTypeId, cd.Value, ci.ColumnName,
     rn = row_number() over(partition by ci.CTypeId, ci.ColumnName order by cd.Value)
   from #Table3 cd 
   Inner Join #Table2 ci 
     on ci.Id = cd.Table2Id
) d
pivot
(
  max(Value)
  for ColumnName in ([Account Manager], [Channel], [Start Date], 
                      [End Date], [Gross Annual])
) piv
order by CTypeId

See demo, you get the desired result:

+---------+-----------------+------------+------------+------------+---------------+
| CTypeId | Account Manager |  Channel   | Start Date |  End Date  | Gross Annual  |
+---------+-----------------+------------+------------+------------+---------------+
|       6 | John            | D2D        | 06/03/2017 | 06/03/2017 |          5232 |
|       6 | Ollie           | JEDF       | 06/03/2017 | 06/03/2017 |          5232 |
|      20 | Jack            | PS         | 06/03/2017 | 07/03/2017 |          2500 |
|      20 | Jack / Kate     | PS Sparrow | 06/03/2017 | 07/03/2017 |          3000 |
|      20 | Jack Sparrow    | PSP        | 06/03/2017 | 07/03/2017 |          7000 |
+---------+-----------------+------------+------------+------------+---------------+

Once you've got your final result you want, it's easy to convert the query to dynamic SQL:

Declare @Columns nvarchar(max) 
Declare @a nvarchar(max)
Set @Columns = stuff((select distinct ',' + quotename(ColumnName) 
                   from #table2
                   for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

Declare @sql nvarchar(max) 
 = 'Select CTypeId, '+@Columns+'
    from
    (
      select ci.CTypeId, cd.Value, ci.ColumnName,
        rn = row_number() over(partition by  ci.CTypeId, ci.ColumnName order by cd.Value)
      from #Table3 cd 
      Inner Join #Table2         ci 
        on ci.Id = cd.Table2Id
    ) as s 
    Pivot(MAX(Value) For ColumnName IN ('+@columns+')) as pvt
    order by CTypeId'


execute(@sql);

See Demo. This gives the same result as the hard-coded version with the flexibility of dynamic sql.