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
I think you will need multiple PIVOT's. It's made slightly more complicated by a few things
CodeX,CodeY,...Salary1,Salary2,..."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