SQL Server Pivot Dynamic Sql with two columns

Question

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

Consumable sample data, which we can use your above attempt against, and meaningful expecting results will help us help you. — Thom A, Mar 04 '22 at 15:57
@Larnu I want the c.customColumnValue to be inserted into custom columns, that is all I need. — XKZ, Mar 04 '22 at 16:03
To pivot 2 sets of values (important X, custom X ) you can use a conditional aggregation. — Serg, Mar 04 '22 at 16:03
@Serg and how am I supposed to repeat it each time? I don't know how many columns there will be! I'm doing something wrong here and I can't figure it out. — XKZ, Mar 04 '22 at 16:06
Yes, you stiil need a dynamic sql. Just a different form of the query. — Serg, Mar 04 '22 at 16:09
Where? I only see 2 expected result sets in your question, and the latter has no data. — Thom A, Mar 04 '22 at 16:13
@Larnu ... I hope that you'll get the idea and help guide me Like — XKZ, Mar 04 '22 at 16:15
I can't "get the idea" when you won't share sample data, @TarikGen . — Thom A, Mar 04 '22 at 16:21
@Tarik - "Describing" data takes a lot more time than just showing it to us. I'm sure your question could be answered very quickly if you take a minute to put together a [Minimal Example](https://stackoverflow.com/help/minimal-reproducible-example), for example, using https://dbfiddle.uk/ . Just CREATE the tables and INSERT a few rows of sample data, then post what the expected output should be for that sample data. — SOS, Mar 04 '22 at 18:27
@larnu http://sqlfiddle.com/#!18/ee935/1 Here you'll find the sample data. The salary column values must be inside salary1 or salary2 or salary3 based on the inner join condition. Keep in mind that I am using dynamic sql, I tried to make it as simple as possible — XKZ, Mar 04 '22 at 22:17
@SOS Thank you for reminding me! sqlfiddle.com/#!18/ee935/1 Here you'll find the sample data. The salary column values must be inside salary1 or salary2 or salary3 based on the inner join condition. Keep in mind that I am using dynamic sql, I tried to make it as simple as possible — XKZ, Mar 04 '22 at 22:17
@XKZ - Thanks for posting the fiddle. All we need now is the expected output, using those sample values (the fiddle values are different than the OP). **(++Edit++)** I updated the question with what I *think* is your expected output. Please correct it if it's wrong. — SOS, Mar 05 '22 at 01:13
@SOS Wow, thank you so much for taking the time to fix my question and yes, the expected result is correct! I hope someone will help me soon! — XKZ, Mar 05 '22 at 18:46

score 1 · Accepted Answer · answered Mar 05 '22 at 20:20

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

Looking at what you did, it looks so freaking simple! You have just made my weekend much better! Thank you so much, I appreciate you. — XKZ, Mar 05 '22 at 22:57
You're welcome :-) .. and thanks for taking the time to improve your question so we better understood the result you needed! It made it much easier to help. — SOS, Mar 05 '22 at 23:27
This is so wholesome, humans being bros! Once again, thank you! — XKZ, Mar 05 '22 at 23:34

SQL Server Pivot Dynamic Sql with two columns

1 Answers1