我的建议是，每当您使用 PIVOT 时，始终首先使用硬编码的值编写查询，然后您可以轻松地将查询转换为动态解决方案.

My suggestion whenever you are working with PIVOT is to alway write the query first with the values hard-coded, then you can easily convert the query to a dynamic solution.

由于您将有多个 columnC 值将被转换为列，那么您需要查看使用 row_number() 窗口函数来生成一个基于 columnA 和 columnB 的值，每个 columnc 的唯一序列.

Since you are going to have multiple values of columnC that will be converted to columns, then you need to look at using the row_number() windowing function to generate a unique sequence for each columnc based on the values of columnA and columnB.

查询的起点是:

select [ColumnA],
  [ColumnB],
  [ColumnC],
  'SampleTitle'+
  cast(row_number() over(partition by columna, columnb
                          order by columnc) as varchar(10)) seq
from DataSource;

参见演示.此查询将生成新列名称SampleTitle1 等的列表:

See Demo. This query will generate the list of new columns names SampleTitle1, etc:

| COLUMNA | COLUMNB | COLUMNC |          SEQ |
|---------|---------|---------|--------------|
|    5060 |    1006 |  100118 | SampleTitle1 |
|    5060 |    1006 |  100119 | SampleTitle2 |
|    5060 |    1006 |  100120 | SampleTitle3 |

然后您可以使用 seq 中列出的新列名在 columnC 上应用数据透视:

You can then apply the pivot on columnC with the new column names listed in seq:

select columnA, columnB, 
  SampleTitle1, SampleTitle2, SampleTitle3
from
(
   select [ColumnA],
    [ColumnB],
    [ColumnC],
    'SampleTitle'+
      cast(row_number() over(partition by columna, columnb
                              order by columnc) as varchar(10)) seq
   from DataSource
) d
pivot
(
  max(columnc)
  for seq in (SampleTitle1, SampleTitle2, SampleTitle3)
) piv;

参见SQL Fiddle with Demo.

一旦有了正确的逻辑，就可以将数据转换为动态 SQL.这里的关键是生成新列名的列表.我通常使用 FOR XML PATH 类似于:

Once you have the correct logic, you can convert the data to dynamic SQL. The key here is generating the list of new column names. I typically use FOR XML PATH for this similar to:

select STUFF((SELECT distinct ',' + QUOTENAME(seq) 
                from
                (
                  select 'SampleTitle'+
                    cast(row_number() over(partition by columna, columnb
                                            order by columnc) as varchar(10)) seq
                  from DataSource
                ) d
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

参见演示.获得列名列表后，您将生成要执行的 sql 字符串，完整代码为:

See Demo. Once you have the list of column names, then you will generate your sql string to execute, the full code will be:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(seq) 
                    from
                    (
                      select 'SampleTitle'+
                        cast(row_number() over(partition by columna, columnb
                                                order by columnc) as varchar(10)) seq
                      from DataSource
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT columnA, ColumnB,' + @cols + ' 
             from 
             (
               select [ColumnA],
                [ColumnB],
                [ColumnC],
                ''SampleTitle''+
                  cast(row_number() over(partition by columna, columnb
                                          order by columnc) as varchar(10)) seq
               from DataSource
            ) x
            pivot 
            (
                max(columnc)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

参见SQL Fiddle with Demo.这些给出了一个结果:

See SQL Fiddle with Demo. These give a result:

| COLUMNA | COLUMNB | SAMPLETITLE1 | SAMPLETITLE2 | SAMPLETITLE3 |
|---------|---------|--------------|--------------|--------------|
|    5060 |    1006 |       100118 |       100119 |       100120 |
|    5060 |    1007 |       100121 |       100122 |       (null) |
|    5060 |    1012 |       100123 |       (null) |       (null) |