0

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?

1

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           |
  • Hello Scott, thanks for your reply it's working fine.. but can we modify your query or in short without creating dynamic. – Kiran K Dec 28 '18 at 12:35
  • @KiranK - if your RDBMS is SQL Server, you could update the tag in your question to reflect that as it might help others. – Scott Hodgin Dec 28 '18 at 12:38
  • 1
    @KiranK - as far as I know, the only way to address your problem is by using dynamic SQL. There is not a fixed number of parameter_id's to pivot on. – Scott Hodgin Dec 28 '18 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.