SQL Server Pivot Dynamic Sql with two columns-CodePudding

Update 2022-03-04

Here is my fiddle, showing the tables and sample values

CREATE TABLE code (
  id int primary key 
  , labVal varchar(50)
  , Label varchar(50)
)
INSERT INTO code 
VALUES
(1,'Code1','Important 1')
,(2,'Code2','Important 2')
,(3,'Code3','Important 3')

CREATE TABLE client (
  id int primary key
  , Salary decimal
  , fkCode int foreign key references code (id)
)
INSERT INTO client 
VALUES (1,120,3)
,(2,1220,2)
,(3,120, 1)
;

These are the expected results:

id	Important 1	Salary 1	Important 2	Salary 2	Important 3	Salary 3	...
1	code1	120	NULL	NULL	NULL	NULL	...
2	NULL	NULL	code2	1220	NULL	NULL	...
3	NULL	NULL	NULL	NULL	code3	120	...

I am able to get the right data for "Important 1,2,3..." using the query below. But am unable to insert data for "Salary1,2,3,..." since pivot only allows one series of columns.

SELECT * FROM  
(
    SELECT 
    cd.id,
        cd.label, 
        cd.labVal,
        c.salary
     FROM 
        code  cd
  inner join client c on c.fkcode = cd.id 
     
) t 
PIVOT(
    max(labVal) 
    FOR label IN (
        [Important 1]
        ,[salary1]
        ,[Important 2]
        ,[salary2]
        ,[Important 3]
        ,[salary3])
) AS pivot_table;

Results:

id | salary | Important 1 | salary1 | Important 2 | salary2 | Important 3 | salary3
-: | -----: | :---------- | :------ | :---------- | :------ | :---------- | :------
 1 |    120 | Code1       | null    | null        | null    | null        | null   
 3 |    120 | null        | null    | null        | null    | Code3       | null   
 2 |   1220 | null        | null    | Code2       | null    | null        | null

Original Post:

I want to be able to generate this example with the correct data

 ------------------------------------------------------------------------------------------------
| id  | whatever | important 1 | custom 1 | important 2 | custom 2 | important 3 | custom 3 |...|
|-----------------------------------------------------------------------------------------------
|x1    |    a    |     NULL    |   NULL   |     code1   |   120      |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------
|x2    |     b   |     code2   |   450    |     NULL    |   NULL     |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------
|x2    |     b   |     NULL   |   NULL    |     code3    |   250     |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------

I am able to get the right data for "important1,2,3..." but unable to insert data for custom" since pivot only allows one series of columns, I can't create two pivot because they would look like this

---------------------------------------------------------------------------------------------
| id  | whatever | important 1 | important2| important3 | custom 1 | custom 2    |custom 3  |  
---------------------------------------------------------------------------------------------
|x    |          |              |         |             |          |             |          |   
---------------------------------------------------------------------------------------------
|x    |          |              |         |             |          |             |          |   
---------------------------------------------------------------------------------------------

the "custom" column is a tempfield which must have decimal values in it and get repeated each time "important" column shows.

The "important" column header is from a table which hold names which i turned from rows into columns using pivot and assigned the desired values from a different column. but custom column doesn't exist in any table whatsoever but the values that I would like to put inside it do exist, the problem is, I am using dynamic sql to generate the "custom" column name with row_number to avoid duplicate column error.

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


SET @cols = STUFF((SELECT  ','   QUOTENAME(c.important)  ','      QUOTENAME( 'custom'   CAST(ROW_NUMBER() OVER (ORDER BY  c.id) as VARCHAR))  as tempfield 
               FROM tableName rp
                    inner join tblsecond c on rp.sTbId = c.id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') 
  
        print @cols


    ----- this @cols
---- will print [important first row to column],[custom1],[important second row to column],[custom2]..


        set @query = 'SELECT  * into #temp from  
            (
                select c.id, c.whatever, c.name, c.customColumnValue  
               FROM tableName rp
                    inner join tblsecond c on rp.sTbId = c.id
         

           ) x
            pivot 
            (
                 max(name)
                for important in ('   @cols  ')
            ) p             select * from #temp

                                  
'

execute(@query)

The sample data is here

http://sqlfiddle.com/#!18/ee935/1

CodePudding user response：

I think you will need multiple PIVOT's. It's made slightly more complicated by a few things

Code/Label columns are not sequentially named CodeX,CodeY,...
Salary columns are sequentially named Salary1,Salary2,...
SELECT list columns must be grouped together "CodeX,Salary1,CodeY,Salary2....*

SET @cols = STUFF((SELECT ',' QUOTENAME(c.important) ',' QUOTENAME( 'custom' CAST(ROW_NUMBER() OVER (ORDER BY c.id) as VARCHAR))

You had the right idea there, building a concatenated list of both columns. But you'll actually need 3 variables: 1) For code columns 2) for salary columns and 3) for select list.

Note: Must use the same ORDER BY in all queries

 DECLARE @LabelCols AS VARCHAR(MAX),
         @SalaryCols AS VARCHAR(MAX),
         @SelectCols AS VARCHAR(MAX),
         @Query  AS VARCHAR(MAX);
 
-- ** SQL Server 2012 doesn't support STRING_AGG()
 SET @LabelCols = STUFF((    SELECT  ','   QUOTENAME(cd.label) 
                             FROM code  cd INNER JOIN client c ON c.fkcode = cd.id 
                             ORDER BY cd.id
                             FOR XML PATH('')
                          ),1,1,'') 
                          
 SET @SalaryCols = STUFF(( SELECT  ','   QUOTENAME('Salary'  
                                            CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20)) 
                                      ) 
                              FROM code cd INNER JOIN client c ON c.fkcode = cd.id 
                              FOR XML PATH('')
                          ),1,1,'') 
 
 SET @SelectCols = STUFF(( SELECT  ','   QUOTENAME(cd.label) 
                                     ','    QUOTENAME('Salary'  
                                            CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20))) 
                             FROM code  cd INNER JOIN client c ON c.fkcode = cd.id 
                             FOR XML PATH('')
                          ),1,1,'')

Then use the three variable in your SELECT:

SET @Query = '
       SELECT  ClientId
              , CodeId 
              , '  @SelectCols  '
       FROM
       (
              SELECT cd.id AS CodeId
                     , cd.label
                     , cd.labVal
                     , c.salary
                     , ''Salary''  CAST(ROW_NUMBER() OVER (ORDER BY  c.id) AS VARCHAR(20)) AS salaryLabel
              FROM code cd 
                      INNER JOIN client c ON c.fkcode = cd.id 
       ) x
       PIVOT
       (
             MAX(labVal)
             FOR label IN ('   @LabelCols  ')
       ) p1
       PIVOT
       (
             MAX(salary)
             FOR salaryLabel IN ('   @SalaryCols  ')
       ) p2
'
                    
EXECUTE (@Query)

Results:

CodeId | Important 1 | Salary1 | Important 2 | Salary2 | Important 3 | Salary3
-----: | :---------- | ------: | :---------- | ------: | :---------- | ------:
     1 | Code1       |     120 | null        |    null | null        |    null
     2 | null        |    null | Code2       |    1220 | null        |    null
     3 | null        |    null | null        |    null | Code3       |     120

db<>fiddle here