pivot with two columns combine ms sql

Question

I want to come-up a results that display the values in tables in pivot mode. However the column name of other rows is a combination of Person_Id and Author_Number, and the value for that column should be Project_Id. To make it easier to understand, Please see sample results below:

Client_Id  12345_1   12345_2  12346_1  12346_2 12346_1
Client1     Test1      Test1   
Client2                       Test2    Test2  
Client3                                        Test3   

Note that from second column is the combination of Person_Id and Author_Number and so on and so forth.

Here is the sample table

CREATE TABLE [dbo].[Authors](
        [Client_Id] [nvarchar](50) NOT NULL,
        [Project_Id] [nvarchar](50) NOT NULL,
        [Person_Id] [int] NOT NULL,
        [Author_Number] [int] NOT NULL,
        [Family_Name] [nvarchar](50) NULL,
        [First_Name] [nvarchar](50) NULL,
    )

    INSERT INTO Authors (Client_Id, Project_Id, Person_Id, Author_Number, Family_Name, First_Name)
    VALUES ('Client1','TEST1',12345,1,'Giust','Fede')
    INSERT INTO Authors (Client_Id, Project_Id, Person_Id, Author_Number, Family_Name, First_Name)
    VALUES ('Client1','TEST1',12345,2,'Ma','Ke')
    INSERT INTO Authors (Client_Id, Project_Id, Person_Id, Author_Number, Family_Name, First_Name)
    VALUES ('Client2','TEST2',12346,1,'Jones','Peter')
    INSERT INTO Authors (Client_Id, Project_Id, Person_Id, Author_Number, Family_Name, First_Name)
    VALUES ('Client2','TEST2',12346,2,'Davies','Bob')
    INSERT INTO Authors (Client_Id, Project_Id, Person_Id, Author_Number, Family_Name, First_Name)
    VALUES ('Client3','TEST3',12346,3,'Richards','Craig')

Here is the scripts

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

select @colsPivot = STUFF((SELECT ',' + QUOTENAME(c.col + '_'+cast(Author_Number as varchar(10))) 
                    from Authors
                    cross apply
                    (
                       select CAST(Person_Id AS VARCHAR(10)) col 
                    ) c

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select Client_id,  '+@colsPivot+' 
      from
      (
        select Client_id,   
          col+''_''+cast(Author_Number as varchar(10)) col, val
        from
        (
          select Client_id, Project_id,
            Person_Id, 
            Author_Number, 
            Family_Name, 
            First_Name
          from Authors
        ) s
        unpivot
        (
          val
          for col in (Project_id)
        ) u
      ) x1
      pivot
      (
        max(val)
        for col in ('+ @colspivot +')
      ) p'

PRINT @query
exec(@query)

I can't figure out what the problem is with the scripts. The values that display after Client Id columns are null

Answer 1

try this query. i updated it.

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

select @colsPivot = STUFF((SELECT ',' + QUOTENAME(c.col + '_'+cast(Author_Number as varchar(10))) 
                from Authors
                cross apply
                (
                   select CAST(Person_Id AS VARCHAR(10)) col 
                ) c

        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


set @query   = 'select Client_id,  '+@colsPivot+' 
  from
  (
    select Client_id,   
      cast(Person_id as varchar(10))+''_''+cast(Author_Number as varchar(10)) col, val
    from
    (
      select Client_id, Project_id,
        Person_Id, 
        Author_Number, 
        Family_Name, 
        First_Name
      from Authors
    ) s
    unpivot
    (
      val
      for col in (Project_id)
    ) u
  ) x1
  pivot
  (
    max(val)
    for col in ('+ @colspivot +')
  ) p'

PRINT @query
exec(@query)

and the output of above query is :

Client_id   12345_1 12345_2 12346_1 12346_2 12346_3
----------------------------------------------------------
Client1          TEST1  TEST1   NULL    NULL    NULL
Client2          NULL   NULL    TEST2   TEST2   NULL
Client3          NULL   NULL    NULL    NULL    TEST3