T-SQL Tricky grouping when creating dynamic SQL

Question

I creating dynamic sql using multiple CTEs and temp tables. One of table looks like folloing:

id   datapoint_no load_timestamp practice
----------------- -------------- --------
1    1            2013-12-04     CSS
2    1            2013-12-04     TS
3    1            2013-12-04     MC
4    2            2013-11-25     CSS
5    2            2013-11-25     TS
6    2            2013-11-25     MC
7    3            2013-11-18     CSS
8    3            2013-11-18     TS
9    3            2013-11-18     MC
10   4            NULL           CSS
11   4            NULL           TS
12   4            NULL           MC
13   5            NULL           CSS
14   5            NULL           TS
15   5            NULL           MC

Also I have couple of CTEs which creates script with another cte =) based on table above like:

data_1 AS (
SELECT amount AS TCV,
    SUM(amount * probability/100) AS WeightedTCV,
    SUM(abr) AS ABR,
    SUM(abr * project_margin_c/100) AS ABP,
    SUM(NULL) AS ClosedDeals
FROM  dbo.opportunities
WHERE load_timestamp='2013-12-04' AND practice_c='CSS'
),
data_2 AS (
SELECT amount AS TCV,
    SUM(amount * probability/100) AS WeightedTCV,
    SUM(abr) AS ABR,
    SUM(abr * project_margin_c/100) AS ABP,
    SUM(NULL) AS ClosedDeals
FROM  dbo.opportunities
WHERE load_timestamp='2013-12-04' AND practice_c='TS'
),
..... about 15 other data_XXX

Now I want to complete this script with select and from and here I've been stopped. I need to union data which belong to same load_timestamp and left join all other data from from other timestamps).

Maybe I am using not the best way of initial script preparation but I do not know how to do it better way.

Finally something like following table should be result of script exection:

practice  load_timestamp_1  TCV_1 WeightedTCV_2 ABR_1 ABR_1 load_timestamp_2  TCV_2  WeightedTCV_2 ABR_2 ABR_2
CSS       2013-12-04        xxx   yyy           zzz   www   2013-11-25        xxx    yyy           zzz   www
TS        2013-12-04        xxx   yyy           zzz   www   2013-11-25        xxx    yyy           zzz   www
MC        2013-12-04        xxx   yyy           zzz   www   2013-11-25        xxx    yyy           zzz   www
...................

question about your final data grid: you have the "WeightedTCV_2" field in there twice. Is that by design? Or should the first one be "WeightedTCV_1"? Also wondering if you repeat these fields by design in the final data grid: "ABR_1", "ABR_1". Seems like based on your CTE, maybe that should be "ABR_1", "ABP_1"? Thanks — Jon Jaussi, Dec 21 '13 at 14:43

score 0 · Answer 1 · edited May 23 '17 at 12:19

If I have not understood your data structure correctly, please add some details to your question and I can revise. This is a variation of TSQL PIVOT MULTIPLE COLUMNS.

It is technically feasible to derive your field list in the PIVOT using dynamic SQL. Click here for more details on that. But before implementing a solution that leverages dynamic SQL, be sure you understand the risks.

Itzik Ben-Gan's example on how to build a dynamic PIVOT is excellent. I highly recommend his book: Inside Microsoft SQL Server 2008: T-SQL and Microsoft SQL Server 2012 T-SQL Fundamentals

with MyTable
as
(
select 1 as id, 1 as datapoint_no,  '2013-12-04' as load_timestamp, 'CSS' as practice
union
select 2 as id, 1 as datapoint_no,  '2013-12-04' as load_timestamp, 'TS' as practice
union
select 3 as id, 1 as datapoint_no,  '2013-12-04' as load_timestamp, 'MC' as practice
union
select 4 as id, 2 as datapoint_no,  '2013-11-25' as load_timestamp, 'CSS' as practice
union
select 5 as id, 2 as datapoint_no,  '2013-11-25' as load_timestamp, 'TS' as practice
union
select 6 as id, 2 as datapoint_no,  '2013-11-25' as load_timestamp, 'MC' as practice
union
select 7 as id, 3 as datapoint_no,  '2013-11-18' as load_timestamp, 'CSS' as practice
union
select 8 as id, 3 as datapoint_no,  '2013-11-18' as load_timestamp, 'TS' as practice
union
select 9 as id, 3 as datapoint_no,  '2013-11-18' as load_timestamp, 'MC' as practice
),
opportunities
as
(
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-12-04' as load_timestamp, 'CSS' as practice_c
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-12-04' as load_timestamp, 'TS' as practice_c
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-12-04' as load_timestamp, 'MC' as practice_c   
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-11-25' as load_timestamp, 'CSS' as practice_c
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-11-25' as load_timestamp, 'TS' as practice_c
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-11-25' as load_timestamp, 'MC' as practice_c            
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-11-18' as load_timestamp, 'CSS' as practice_c
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-11-18' as load_timestamp, 'TS' as practice_c
union
select 123 as amount, .123 as probability, 456 as abr, .456 as project_margin_c
     , '2013-11-18' as load_timestamp, 'MC' as practice_c
), Step1
as
(
select t.practice
      ,t.load_timestamp
      ,t.datapoint_no
      ,sum(o.amount) as TCV
      ,sum(amount * probability/100) as WeightedTCV
      ,sum(abr) as ABR
      ,sum(abr * project_margin_c/100) as ABP
      ,0 AS ClosedDeals
  from MyTable t
  left join opportunities o
    on t.practice = o.practice_c
   and t.load_timestamp = o.load_timestamp
 group by t.practice
         ,t.load_timestamp
         ,t.datapoint_no
)
select practice, 
  [load_timestamp_1], [TCV_1], [WeightedTCV_1], [ABR_1], [ABP_1], [ClosedDeals_1],
  [load_timestamp_2], [TCV_2], [WeightedTCV_2], [ABR_2], [ABP_2], [ClosedDeals_2],
  [load_timestamp_3], [TCV_3], [WeightedTCV_3], [ABR_3], [ABP_3], [ClosedDeals_3]
from
(
select practice, col, value
  from Step1
  cross apply
  (
  select 'load_timestamp_' + cast(datapoint_no as varchar), load_timestamp union all
  select 'TCV_' + cast(datapoint_no as varchar), cast(TCV as varchar) union all
  select 'WeightedTCV_' + cast(datapoint_no as varchar), cast(WeightedTCV as varchar) union all
  select 'ABR_' + cast(datapoint_no as varchar), cast(ABR as varchar)  union all
  select 'ABP_' + cast(datapoint_no as varchar), cast(ABR as varchar)  union all
  select 'ClosedDeals_' + cast(datapoint_no as varchar), cast(ClosedDeals as varchar)
  ) c(col, value)
) d
pivot
(
  max(value)
  for col in ([load_timestamp_1], [TCV_1], [WeightedTCV_1], [ABR_1], [ABP_1], [ClosedDeals_1],
  [load_timestamp_2], [TCV_2], [WeightedTCV_2], [ABR_2], [ABP_2], [ClosedDeals_2],
  [load_timestamp_3], [TCV_3], [WeightedTCV_3], [ABR_3], [ABP_3], [ClosedDeals_3])
) piv;

T-SQL Tricky grouping when creating dynamic SQL

1 Answers1