Unpivot column to rows dynamic sql

• seismicbeat

  Ten Centuries

  Points: 1155

  More actions

  April 24, 2017 at 2:27 pm

  #337488

  Hello,

  I was tasked to move the following data to our database and visualize through Cognos, so the data is currently shared in Excel files. The base data is as follows.

  Hope the sample data serves well to explain the current circumstances.

  CREATE TABLE FinanceExpenses (
  DocumentName nvarchar(255),
  ProductName nvarchar(255),
  Category nvarchar(255),
  MainAccount nvarchar(255),
  SubAccount nvarchar(255),
  JAN_2017 float,
  FEB_2017 float,
  MAR_2017 float,
  APR_2017 float,
  MAY_2017 float,
  JUN_2017 float,
  JUL_2017 float,
  AUG_2017 float,
  SEP_2017 float,
  OCT_2017 float,
  NOV_2017 float,
  DEC_2017 float,
  JAN_2018 float,
  FEB_2018 float,
  MAR_2018 float,
  APR_2018 float,
  MAY_2018 float,
  JUN_2018 float,
  JUL_2018 float,
  AUG_2018 float,
  SEP_2018 float,
  OCT_2018 float,
  NOV_2018 float,
  DEC_2018 float,
);

  INSERT INTO FinanceExpenses (DocumentName, ProductName, Category, MainAccount, SubAccount, JAN_2017, FEB_2017, MAR_2017, APR_2017, MAY_2017, JUN_2017, JUL_2017, AUG_2017, SEP_2017, OCT_2017, NOV_2017, DEC_2017, JAN_2018, FEB_2018, MAR_2018, APR_2018, MAY_2018, JUN_2018, JUL_2018, AUG_2018, SEP_2018, OCT_2018, NOV_2018, DEC_2018)
VALUES ('2017MAYFC', 'PRODUCT A', 'PROMOTION', 'PR Activity', 'PR Activity', 3.770, 7.775, NULL, -5.559, NULL, NULL, NULL, 9.832, 64.663, NULL, NULL, 64.354, 39.932, NULL, 89.058, NULL, 94.030, 83.245, NULL, 485.114, NULL, 75.055, NULL, NULL),
     ('2017MAYFC', 'PRODUCT A', 'PROMOTION', 'Professional Promotion', 'Printed Materials', NULL, 10.250, 10.000, 5.000, 5.000, 15.000, 5.000, 15.000, NULL, -55.279, 89.471, 88.123, NULL, NULL, 54.530, NULL, 8.754, 91.263, NULL, NULL, 332.141, NULL, NULL, NULL),
     ('2017MAYFC', 'PRODUCT A', 'LCMR', 'Education', 'Advisory Board', 13.255, 30.050, 5.500, 5.700, 15.050, NULL, 5.000, 15.000, NULL, -75.279, 99.471, 85.123, NULL, NULL, 50.530, NULL, 6.754, 93.263, NULL, NULL, NULL, 352.141, NULL, NULL);

  I've transposed the data with the following query;

  --Create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
 select @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
       FROM (
        select table_name, column_name, ordinal_position, data_type
        from INFORMATION_SCHEMA.COLUMNS
        where table_name = 'FinanceExpenses'
        and  ordinal_position not in (1,2,3,4,5)
        ) cols
       ORDER BY ordinal_position     
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''       
       )

  --Unpivot using dynamic sql
 declare @sqlStr nvarchar(max)
 set @sqlStr = N'
    select DocumentName, ProductName, Category, MainAccount, SubAccount, Col as Date, Expenses
    from(
     select DocumentName, ProductName, Category, MainAccount, SubAccount, ' + @cols + '
     from FinanceTest
    ) as cp
    unpivot
    (
     Expenses for Col in (' + @cols + ')
    ) as up'

   exec sp_executesql @sqlStr

  I don't say my solution is ideal, I'm always eager to learn the techniques that fits in such circumstances, but I thought that the dynamic sql might be a thing to pull this off. Actually, things went good untill I noticed the NULL records, is there any way to change the NULL values with 0 and include to output? Most likely I would need to create a column list wrapped in ISNULL but I couldn't type it in proper way.

  The second part of my problem is, the "DocumentName" column represents the financial period. In this example, "2017MAYFC" indicates the 2017/2018 May Forecast. There are three financial periods during a year that the Finance Department takes into account while monitoring expenses, which means that I will be provided the August and November Forecasts as well. Each documents have the current year data along with next year, and I need to store the entire data in one place and insert the new one when I'm provided. On the other hand, I will be provided the actual data at the beginning of each month, which means that the previous months values should be updated with the actual data. For example, when I'm provided the April actual data, the base table should be updated. Please see the attached screenshot, I've encircled the first 4 months as "Actuals", supposing the actual expenses of Jan, Feb, Mar and Apr are provided. This is the first time that I'm dealing with such data, so any help/suggestion would be greatly appreciated.

  Thanks

• DesNorton

  SSC-Insane

  Points: 24285

  More actions

  April 24, 2017 at 9:56 pm

  #1939701

  You need to create an additional variable with forced values for the NULLS.
  
--Create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
declare @vals AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
   FROM (
   select table_name, column_name, ordinal_position, data_type
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'FinanceExpenses'
   and ordinal_position not in (1,2,3,4,5)
   ) cols
   ORDER BY ordinal_position 
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''  
   )

  select @vals = STUFF((SELECT ',ISNULL(' + QUOTENAME(column_name) + ', 0) AS ' + QUOTENAME(column_name)
   FROM (
   select table_name, column_name, ordinal_position, data_type
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'FinanceExpenses'
   and ordinal_position not in (1,2,3,4,5)
   ) cols
   ORDER BY ordinal_position 
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''  
   )

  --Unpivot using dynamic sql
declare @sqlStr nvarchar(max)
set @sqlStr = N'
  select DocumentName, ProductName, Category, MainAccount, SubAccount, Col as Date, Expenses
  from(
  select DocumentName, ProductName, Category, MainAccount, SubAccount, ' + @vals + '
  from FinanceExpenses
  ) as cp
  unpivot
  (
  Expenses for Col in (' + @cols + ')
  ) as up'

  exec sp_executesql @sqlStr


  ---

  How to post data/code on a forum to get the best help.

• seismicbeat

  Ten Centuries

  Points: 1155

  More actions

  April 27, 2017 at 12:50 pm

  #1940303

  @desnorton, that's exactly what I want to do, I really appreciate for your help. 🙂

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 27, 2017 at 1:29 pm

  #1940308

  Here's another alternative. IMO it's more flexible as it would allow multiple columns in a single unpivot and work with the column names.
  It's explained on the following article: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

  
--Create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ', (' + REPLACE( QUOTENAME(column_name, ''''), '_', ' ') + ', ' + QUOTENAME(column_name) + ')'
   FROM (
   select table_name, column_name, ordinal_position, data_type
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'FinanceExpenses'
   and ordinal_position not in (1,2,3,4,5)
   ) cols
   ORDER BY ordinal_position 
   FOR XML PATH(''), TYPE).value('./text()[1]', 'NVARCHAR(MAX)'),1,1,''  
   )
  
--Unpivot using dynamic sql
declare @sqlStr nvarchar(max)
set @sqlStr = N'
  SELECT DocumentName, ProductName, Category, MainAccount, SubAccount, up.cDate as Date, up.Expenses
  FROM FinanceExpenses
  CROSS APPLY( VALUES' + @cols + N')up(cDate, Expenses);'

  exec sp_executesql @sqlStr;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jason A. Long

  SSC-Insane

  Points: 23781

  More actions

  April 28, 2017 at 7:50 am

  #1940380

  Here's a slightly different option that concatenates the the column name in a variable rather than using the FOR XML method
  
DECLARE @UnpivotList NVARCHAR(MAX) = N'';
SELECT
    @UnpivotList = CONCAT(@UnpivotList, ',(''', c.name, ''', fe.', c.name, ')')
FROM
    sys.columns c
WHERE
    c.object_id = OBJECT_ID('tempdb.dbo.FinanceExpenses')
    AND c.column_id > 5;

  DECLARE @sql NVARCHAR(MAX) = CONCAT(N'
SELECT
    fe.DocumentName,
    fe.ProductName,
    fe.Category,
    fe.MainAccount,
    fe.SubAccount,
    ul.Category,
    ul.Value
FROM
    dbo.FinanceExpenses fe
    CROSS APPLY ( VALUES ', STUFF(@UnpivotList, 1, 1, ''), N') ul (Category, Value)');

  EXEC sys.sp_executesql @sql;


  Results:
  

  DocumentName	ProductName	Category	MainAccount	SubAccount	Category	Value
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	JAN_2017	3.77
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	FEB_2017	7.775
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	MAR_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	APR_2017	-5.559
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	MAY_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	JUN_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	JUL_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	AUG_2017	9.832
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	SEP_2017	64.663
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	OCT_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	NOV_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	DEC_2017	64.354
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	JAN_2018	39.932
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	FEB_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	MAR_2018	89.058
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	APR_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	MAY_2018	94.03
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	JUN_2018	83.245
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	JUL_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	AUG_2018	485.114
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	SEP_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	OCT_2018	75.055
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	NOV_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	PR Activity	PR Activity	DEC_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	JAN_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	FEB_2017	10.25
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	MAR_2017	10
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	APR_2017	5
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	MAY_2017	5
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	JUN_2017	15
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	JUL_2017	5
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	AUG_2017	15
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	SEP_2017	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	OCT_2017	-55.279
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	NOV_2017	89.471
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	DEC_2017	88.123
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	JAN_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	FEB_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	MAR_2018	54.53
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	APR_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	MAY_2018	8.754
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	JUN_2018	91.263
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	JUL_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	AUG_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	SEP_2018	332.141
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	OCT_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	NOV_2018	NULL
  2017MAYFC	PRODUCT A	PROMOTION	Professional Promotion	Printed Materials	DEC_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	JAN_2017	13.255
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	FEB_2017	30.05
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	MAR_2017	5.5
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	APR_2017	5.7
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	MAY_2017	15.05
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	JUN_2017	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	JUL_2017	5
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	AUG_2017	15
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	SEP_2017	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	OCT_2017	-75.279
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	NOV_2017	99.471
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	DEC_2017	85.123
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	JAN_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	FEB_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	MAR_2018	50.53
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	APR_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	MAY_2018	6.754
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	JUN_2018	93.263
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	JUL_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	AUG_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	SEP_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	OCT_2018	352.141
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	NOV_2018	NULL
  2017MAYFC	PRODUCT A	LCMR	Education	Advisory Board	DEC_2018	NULL

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 28, 2017 at 7:57 am

  #1940381

  Jason A. Long - Friday, April 28, 2017 7:50 AM

  Here's a slightly different option that concatenates the the column name in a variable rather than using the FOR XML method
  
  DECLARE @UnpivotList NVARCHAR(MAX) = N'';
  SELECT
      @UnpivotList = CONCAT(@UnpivotList, ',(''', c.name, ''', fe.', c.name, ')')
  FROM
      sys.columns c
  WHERE
      c.object_id = OBJECT_ID('tempdb.dbo.FinanceExpenses')
      AND c.column_id > 5;
  

  Be careful Jason. This method does not guarantee an order when used. This is not a problem when unpivoting, but might generate columns in incorrect order when pivoting. I'm not sure how to prove this, but I can assure you that this happened to me before.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jason A. Long

  SSC-Insane

  Points: 23781

  More actions

  April 28, 2017 at 9:34 am

  #1940398

  Luis Cazares - Friday, April 28, 2017 7:57 AM

  Be careful Jason. This method does not guarantee an order when used. This is not a problem when unpivoting, but might generate columns in incorrect order when pivoting. I'm not sure how to prove this, but I can assure you that this happened to me before.

  The same method wouldn't be conducive to unpivoting regardless of order... The equivalent PIVOT (with guaranteed order) would look like the following...

  DECLARE @PivotList NVARCHAR(MAX) = N'';
SELECT
    @PivotList = CONCAT(@PivotList, ',', CHAR(13), CHAR(9), ud.DateCategory, ' = MAX(CASE WHEN ud.DateCategory = ''', ud.DateCategory, ''' THEN ud.Value END)')
FROM
    dbo.UnpivotData ud
    CROSS APPLY (VALUES (
                        RIGHT(ud.DateCategory, 4),
                        CASE LEFT(ud.DateCategory, 3)
                            WHEN 'JAN' THEN 1
                            WHEN 'FEB' THEN 2
                            WHEN 'MAR' THEN 3
                            WHEN 'APR' THEN 4
                            WHEN 'MAY' THEN 5
                            WHEN 'JUN' THEN 6
                            WHEN 'JUL' THEN 7
                            WHEN 'AUG' THEN 8
                            WHEN 'SEP' THEN 9
                            WHEN 'OCT' THEN 10
                            WHEN 'NOV' THEN 11
                            WHEN 'DEC' THEN 12
                        END)
                    ) ym ([Year], [Month])
GROUP BY
    ym.[Year],
    ym.[Month],
    ud.DateCategory
ORDER BY
    ym.[Year],
    ym.[Month];

  DECLARE @sql NVARCHAR(4000) = CONCAT(N'
SELECT
    ud.DocumentName,
    ud.ProductName,
    ud.Category,
    ud.MainAccount,
    ud.SubAccount,',
    STUFF(@PivotList, 1, 1, ''), N'
FROM
    dbo.UnpivotData ud
GROUP BY
    ud.DocumentName,
    ud.ProductName,
    ud.Category,
    ud.MainAccount,
    ud.SubAccount;')

  --EXEC sys.sp_executesql @sql
PRINT @sql


  edit: fixed the ordering...
  

• seismicbeat

  Ten Centuries

  Points: 1155

  More actions

  May 1, 2017 at 2:20 pm

  #1940608

  @desnorton, @luis Cazares, @jason A. Long, Thank you so much for your answers in great details to my question. You are kind enough to share your experience with great examples, and these alternative methods helped me lot in understanding how I could achieve this in a variety of ways.