Pivot data type error

pivot

I have a table which needs to do pivot as column to row, but getting data type error,
I have below table data

I have below data

Need to pivot and output should be like

Need to pivot and output should be like

I tried with query:

select prod As product_id , para As parameter_id
from
(
  select product_family_id,product_id,parameter_id 
  from product_family_exclusion
) d
pivot
(
  max(product_id) 
  for parameter_id in (prod,para)
) piv;

But getting error:

Msg 8114, Level 16, State 1, Line 15 Error converting data type
nvarchar to int. Msg 473, Level 16, State 1, Line 15 The incorrect
value "prod" is supplied in the PIVOT operator.

What I am missing about PIVOT table?

Best Answer

I don't think your sample output is correct for product_id 10211. You seem to be missing parameter_id 416.

Here is an example of using dynamic SQL (in SQL Server) to solve the problem. You could probably adapt it to other RDBMS's. I took the liberty of generating unique column names for parameter_id because using the same column name might be problematic for the PIVOT.

--Demo setup
set nocount on
DECLARE @cols AS NVARCHAR(MAX),@colsCoalesce AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
drop table if exists Table1
create table Table1 (product_family_id int, product_id int, parameter_id int)

insert into table1(product_family_id,product_id,parameter_id) values
(111,3227,334),
(111,3227,335),
(1112,10211,414),
(1112,10211,415),
(1112,10211,416),
(1112,10211,417),
(1112,11784,514),
(1112,11784,515),
(1112,11784,516),
(1112,11784,519)

--The solution
drop table if exists PivotTemp
select * into PivotTemp from 
(
select product_family_id, product_id, parameter_id, 'parameter_id' + convert(varchar(5),ROW_NUMBER() over(partition by product_family_id, product_id order by parameter_id)) as GeneratedParameterHeading from table1
)a
--select * from PivotTemp
SElect @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GeneratedParameterHeading) 
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SElect @colsCoalesce = STUFF((SELECT distinct ',' + 'coalesce(convert(varchar(100),' + QUOTENAME(c.GeneratedParameterHeading) + '),'' '') as ' + QUOTENAME(c.GeneratedParameterHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

print @cols
print @colsCoalesce
set @query = 'SELECT product_family_id, product_id, ' + @colsCoalesce + ' from 
            (
                select product_family_id
                    , product_id
                    , parameter_id
                    , GeneratedParameterHeading
                from PivotTemp
           ) x
            pivot 
            (
                 max(parameter_id)
                for GeneratedParameterHeading in (' + @cols + ')
            ) p '

print @query
execute(@query)

| product_family_id | product_id | parameter_id1 | parameter_id2 | parameter_id3 | parameter_id4 |
|-------------------|------------|---------------|---------------|---------------|---------------|
| 111               | 3227       | 334           | 335           |               |               |
| 1112              | 10211      | 414           | 415           | 416           | 417           |
| 1112              | 11784      | 514           | 515           | 516           | 519           |