Struggling with dynamic data

Here is a dynamic SQL script where labels and values are build dynamically using SQL string concatenation method with For XML Path

Please note that following script is missing the bar colors which should be dynamically appended to SQL as the same number of labels

So a similar concatenation method can be executed on a table where bar label colors are stored. After the list is fetched as a single character variable, it can be added to the @SQL at the appropriate places

--create table reportData (year int, type varchar(10), qty int)
--insert into reportData select 2013, 'Type 1',1
--insert into reportData select 2014, 'Type 1',2
--insert into reportData select 2015, 'Type 3',1
--insert into reportData select 2016, 'Type 2',1
--insert into reportData select 2016, 'Type 3',2
--insert into reportData select 2016, 'Type 4',4


declare @sql nvarchar(max)
declare @labels nvarchar(max)
declare @measures nvarchar(max)

SELECT
 @labels = 
  STUFF(
    (
    SELECT distinct
      ',"' + convert(nvarchar(4),[year]) + '"'
    FROM reportData
    FOR XML PATH('')
    ), 1, 1, ''
  )

SELECT
 @measures = 
  STUFF(
    (
    SELECT 
      ',' + convert(nvarchar(4),qty)
    FROM (
        select 
            years.year, data.qty
        from (
            select distinct year from reportData
        ) years
        left join (
            select sum(qty) as qty, year from reportData group by year
        ) data on years.year = data.year
    ) Q
    FOR XML PATH('')
    ), 1, 1, ''
  )

set @sql = N'
var data = {
    labels: ['+ @labels + '],
    datasets: [
        {
            label: "My Graph",
            backgroundColor: [
                ''rgba(255, 99, 132, 0.2)'',
                ''rgba(54, 162, 235, 0.2)'',
                ''rgba(255, 206, 86, 0.2)'',
                ''rgba(75, 192, 192, 0.2)'',
            ],
            borderColor: [
                ''rgba(255,99,132,1)'',
                ''rgba(54, 162, 235, 1)'',
                ''rgba(255, 206, 86, 1)'',
                ''rgba(75, 192, 192, 1)'',
            ],
            borderWidth: 1,
            data: ['+ @measures +'],
        }
    ]
};'

print @sql