为了在图表中创建多条线，
数据表中需要多个系列或列。
换句话说，每个用户都需要一列...

所以而不是单列...

IF OBJECT_ID('spend_v1') IS NOT NULL 
DROP TABLE spend_v1; 

select distinct A.Primary_Brand_Key,A.Year,A.Month,A.Category,Medium,A.Market,b.Sub_Category,b.Segment as Segment,b.Sub_Segment as Sub_Segment,b.Big_C,b.Small_C,case T_Type when 'TRAE' then 'FA'  end as T_Type,--T_Type,concat(Month,' ',Year) as Period_Type,CASE WHEN A.Market='AP / Telangana' THEN 'Cluster'
 WHEN A.Market='Assam / North East / Sikkim' THEN 'Cluster' 
 WHEN A.Market='Bihar/Jharkhand' THEN 'Cluster'
 WHEN A.Market='Guj / D&D / DNH' THEN 'Cluster'
 WHEN A.Market='Karnataka' THEN 'Cluster'
 WHEN A.Market='Kerala' THEN 'Cluster'
 WHEN A.Market='Mah / Goa' THEN 'Cluster'
 WHEN A.Market='MP/Chhattisgarh' THEN 'Cluster'
 WHEN A.Market='Odisha' THEN 'Cluster'
 WHEN A.Market='Pun / Har / Cha / HP / J&K' THEN 'Cluster'
 WHEN A.Market='Rajasthan' THEN 'Cluster' 
 WHEN A.Market='TN/Pondicherry' THEN 'Cluster'
 WHEN A.Market='UP/Uttarakhand' THEN 'Cluster' 
 WHEN A.Market='West Bengal' THEN 'Cluster'
 WHEN A.Market='Delhi' THEN 'Cluster'
 WHEN A.Market='Bangalore' THEN 'Metro'
 WHEN A.Market='Chennai' THEN 'Metror' 
 WHEN A.Market='Hyderabad' THEN 'Metro'
 WHEN A.Market='Kolkata' THEN 'Metro'
 WHEN A.Market='Mumbai' THEN 'Metro'
 WHEN A.Market='All India' THEN 'Geo' END as Geography_Type,A.LSM as Target_Group
into spend_v1 
from dtr.UL_MEDIA_IN_PBRT_SPENDS a
left join
dtr.UL_MEDIA_IN_PBRT_BRAND_HIERARCHY_MASTER b
on a.Category = b.Small_C and a.Primary_Brand_Key = b.PBRT_KEY
group by A.Primary_Brand_Key,b.Segment,b.Sub_Segment,T_Type,LSM
--select * from spend_v1
--where T_Type is not NULL


IF OBJECT_ID('spend_v2') IS NOT NULL 
DROP TABLE spend_v2; 

select distinct Primary_Brand_Key,year,month,category,Market,sum(case when (Medium in ('TV') and Market is not NULL ) then Amount_Spent else 0 end) as TV_Spends,sum(case when (Medium in ('Print') and Market is not NULL ) then Amount_Spent else 0 end) as Print_Spends,sum(case when (Medium in ('Radio') and Market is not NULL ) then Amount_Spent else 0 end) as Radio_Spends,sum(case when (Medium in ('Cinema') and Market is not NULL ) then Amount_Spent else 0 end) as Cinema_Spends,sum(case when (Medium in ('Mobile') and Market is not NULL ) then Amount_Spent else 0 end) as Mobile_Spends,sum(case when (Medium in ('YT') and Market is not NULL ) then Amount_Spent else 0 end) as YouTube_Spends,sum(case when (Medium in ('OTT') and Market is not NULL ) then Amount_Spent else 0 end) as OTT_Spends,sum(case when (Medium in ('FB') and Market is not NULL ) then Amount_Spent else 0 end) as Facebook_Spends,sum(case when (Medium in ('OOH') and Market is not NULL ) then Amount_Spent else 0 end) as OOH_Spends,sum(case when (Medium in ('Others') and Market is not NULL ) then Amount_Spent else 0 end) as Others_Spends,sum(case when (Medium in ('YT','OTT','FB') and Market is not NULL ) then Amount_Spent else 0 end) as Digital_Spends,sum(Amount_Spent) as Total_Spends
into spend_v2 
from dtr.UL_MEDIA_IN_PBRT_SPENDS
group by year,Primary_Brand_Key,Market
order by Primary_Brand_Key,Market;

--select * from spend_v2
--order by Primary_Brand_Key,Market

select distinct A.Primary_Brand_Key as Primary_Brand,IsNull(A.Sub_Category,'')as Sub_Category,IsNull(A.Segment,'') as Segment,IsNull(A.Sub_Segment,'') as Sub_Segment,IsNull(A.Big_C,'') as Big_C,IsNull(A.Small_C,'') as Small_C,A.T_Type,A.Period_Type,IsNull(A.Geography_Type,'')as Geography_Type,IsNull(A.Target_Group,'') as Target_Group,--A.Sub_Category,-- A.new_segment as new_segment,A.Sub_Segment,B.TV_Spends,B.Print_Spends,B.Radio_Spends,B.Cinema_Spends,B.Mobile_Spends,B.Youtube_Spends,B.OTT_Spends,B.Facebook_Spends,B.OOH_Spends,B.Others_Spends,B.Digital_Spends,Total_Spends

        --into dtr.UL_MEDIA_IN_PBRT_SPENDS_OUTPUT

        from spend_v1 a
        inner join spend_v2 b
        on (a.Primary_Brand_Key=b.Primary_Brand_Key
        and a.Market=b.Market)
        or a.T_Type is NOT NULL
        --and a.Category=b.Category
        --and a.T_Type is not NULL
        --or a.Market!=b.Market
        --and T_Type is NULL
        --where T_Type is NOT NULL

        group by A.Primary_Brand_Key,Sub_Category,Segment,Sub_Segment,Big_C,Small_C,Period_Type,Geography_Type,Target_Group,TV_spends,Print_Spends,Radio_Spends,Cinema_Spends,Mobile_Spends,Youtube_Spends,OTT_Spends,Facebook_Spends,OOH_Spends,Others_Spends,Digital_Spends,Total_Spends
        order by A.Primary_Brand_Key,Market

您需要多个，每个用户一个...

data.addColumn('date','Date');
data.addColumn('number','Score');

我们可以根据提供的数据动态构建列，
但我们的方法会有所不同。

我们需要在循环数据时添加列，
然后保存我们创建的列的列索引。
这意味着我们需要在每一行上单独设置每个单元格的值。

比如先创建带有日期列的数据表...

data.addColumn('date','User 1');
data.addColumn('number','User 2');
data.addColumn('number','User 3');

然后当我们遍历数据时，添加用户列...

var data = new google.visualization.DataTable();
data.addColumn('date','Date');

请参阅以下工作示例...

datas.forEach(function (row) {
  // create the data table column for the user and user annotation
  var person_name = row['user'];
  var column_index = data.addColumn('number',person_name);
  var column_index_ann = data.addColumn({type: 'string',role: 'annotation'});

  // next,loop the scores for the user
  var daily_score_datas = row['daily_scores'];
  daily_score_datas.forEach(function (daily_score) {
   // get score values
    var daily_score_date = new Date(daily_score['date']);
    var daily_score_score = daily_score['score'];

    // add a new blank row and save row index
    var row_index = data.addRow();

    // set values for the date,user,and user annotation
    data.setValue(row_index,daily_score_date);
    data.setValue(row_index,column_index,daily_score_score);
    data.setValue(row_index,column_index_ann,person_name);
  });
});

google.charts.load('current',{
  packages: ['corechart']
}).then(function drawCurveTypes() {
  var datas = [
    {"user": "test1","daily_scores": [{"date":"12/11/2021","score": 0},{"date":"12/12/2021","score": 40}]},{"user": "test2","score": 10},"score": 20}]},{"user": "test3","score": 20},"score": 30}]},];

  var data = new google.visualization.DataTable();
  data.addColumn('date','Date');

  datas.forEach(function (row) {
    // create the data table column for the user and user annotation
    var person_name = row['user'];
    var column_index = data.addColumn('number',person_name);
    var column_index_ann = data.addColumn({type: 'string',role: 'annotation'});

    // next,loop the scores for the user
    var daily_score_datas = row['daily_scores'];
    daily_score_datas.forEach(function (daily_score) {
     // get score values
      var daily_score_date = new Date(daily_score['date']);
      var daily_score_score = daily_score['score'];

      // add a new blank row and save row index
      var row_index = data.addRow();

      // set values for the date,and user annotation
      data.setValue(row_index,daily_score_date);
      data.setValue(row_index,daily_score_score);
      data.setValue(row_index,person_name);
    });
  });

  var options = {
    //chartArea: {width:"100%",height:"100%"},title: "Person Performance over Time",height: 500,width: 1300,hAxis: {
      title: 'Time',textPosition: 'none'
    },vAxis: {
      title: 'Score'
    },series: {
      1: {curveType: 'function'}
    },legend: {position: 'none'}
  };

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
  chart.draw(data,options);
});