Column type conflict in Unpivot list

  • hi All,

    I am having problem with the unpivot. Below is the sample table and the sample data, I have also listed the desired output and the T-sql being used.

    CREATE TABLE [dbo].[ProductTest](

    [Co_idx] [int] NOT NULL,

    [co_shortname] [varchar](100) COLLATE Latin1_General_CI_AS NULL,

    [prod_short] [varchar](100) COLLATE Latin1_General_CI_AS NULL,

    [int_min] [decimal](4, 2) NULL,

    [int_roll] [decimal](4, 2) NULL,

    [solicit_fee] [money] NULL

    Insert INTO dbo.ProductTest(Co_Idx, co_shortname, prod_short, int_min, int_roll, solicit_fee)

    Select 1,'Test1','Product1',8.75, 9.76, 100

    UNION

    Select 1,'Test1','Product2',8.75, 9.76, 100

    UNION

    Select 1,'Test1','Product3',8.75, 9.76, 100

    UNION

    Select 2,'Test2','Product1',6.75, 8.76, 200

    UNION

    Select 3,'Test3','Product1',4.75, 6.76, 250

    T-SQL

    Select co_idx,Heading, [1] as value1, [2] as value2, [3] as value3

    FROM

    ( SELECT co_idx, Heading, RowNum, [Value]

    FROM

    (

    Select Cast(Co_idx as varchar(200)) as co_idx,

    Cast (ISNULL(Co_ShortName,'') as varchar(200)) as [Company],

    Cast (ISNULL(Prod_Short,'') as varchar(200)) as [Product Name],

    Cast (ISNULL(int_min,'0') as Varchar(200)) as [Initial Interest Rate],

    Cast (ISNULL(int_roll,'0') as Varchar(200)) as [Roll Rate],

    Cast(' ' as varchar(200)) as [ ],

    ROW_NUMBER() OVER (PARTITION BY co_shortname ORDER BY co_shortname) AS RowNum

    FROM dbo.ProductTest

    )p

    UNPIVOT

    (

    [Value] FOR Heading IN

    ([Company],

    [Product Name],

    [Initial Interest Rate],

    [Roll Rate],

    [ ])

    )AS UP

    ) AS UP1

    PIVOT

    (

    Max ([Value])

    FOR RowNum in ([1],[2],[3])

    ) AS P1

    ORDER BY co_idx,

    CASE Heading

    WHEN 'Company' THEN 1

    WHEN 'Product Name' THEN 2

    WHEN 'Initial Interest Rate' THEN 3

    WHEN 'Roll Rate' THEN 4

    Else 5

    END

    What I was trying to get is a table with three columns Coidx, Heading(all the column headings), Value

    (value of each column) and a blank value after all the products for that company are listed. I have used till value3 ie since the max no of products for a given company were three.

    But i keep getting this error :"The type of column "Initial Interest Rate" conflicts with the type of other columns specified in the UNPIVOT list."

    Even though I have cast all the columns to varchar(200). I dont understand why I get that error.

    Sorry for the long post, Just wanted to make sure you guys understood my problem.

    Please show me some direction. I appreciate your time.

    Co_idxHeadingvalue1value2value3

    1CompanyTest1Test1Test1

    1Product NameProduct1Product2Product3

    1Initial Interest Rate8.758.758.75

    1Roll Rate9.769.769.76

    1

    2CompanyTest2

    2Product NameProduct1

    2Initial Interest Rate6.75

    2Roll Rate8.76

    2

    3CompanyTest3

    3Product NameProduct1

    3Initial Interest Rate4.75

    3Roll Rate6.76

    3

    Thanks, Shilpa.

  • Your code seemed to work ok for me, the only thing I noticed was you missed the close parenthesis on the create table statement:

    IF OBJECT_ID(N'Tempdb..#ProductTest', N'U') IS NOT NULL

    DROP TABLE #ProductTest

    GO

    CREATE TABLE #ProductTest

    (

    Co_idxINT NOT NULL,

    co_shortnameVARCHAR(100) COLLATE Latin1_General_CI_AS NULL,

    prod_shortVARCHAR(100) COLLATE Latin1_General_CI_AS NULL,

    int_minDECIMAL(4, 2) NULL,

    int_rollDECIMAL(4, 2) NULL,

    solicit_feeMONEY NULL

    );

    INSERT INTO #ProductTest(Co_Idx, co_shortname, prod_short, int_min, int_roll, solicit_fee)

    SELECT 1,'Test1','Product1',8.75, 9.76, 100 UNION ALL

    SELECT 1,'Test1','Product2',8.75, 9.76, 100 UNION ALL

    SELECT 1,'Test1','Product3',8.75, 9.76, 100 UNION ALL

    SELECT 2,'Test2','Product1',6.75, 8.76, 200 UNION ALL

    SELECT 3,'Test3','Product1',4.75, 6.76, 250;

    SELECT

    co_idx,Heading,

    [1] AS value1,

    [2] AS value2,

    [3] AS value3

    FROM

    (

    SELECT co_idx, Heading, RowNum, [Value]

    FROM

    (

    SELECT

    CAST (Co_idx AS VARCHAR(200)) AS co_idx,

    CAST (ISNULL(Co_ShortName,'') AS VARCHAR(200)) AS [Company],

    CAST (ISNULL(Prod_Short,'') AS VARCHAR(200)) AS [Product Name],

    CAST (ISNULL(int_min,'0') AS VARCHAR(200)) AS [Initial Interest Rate],

    CAST (ISNULL(int_roll,'0') AS VARCHAR(200)) AS [Roll Rate],

    CAST (' ' AS VARCHAR(200)) AS [ ],

    ROW_NUMBER() OVER (PARTITION BY co_shortname ORDER BY co_shortname) AS RowNum

    FROM #ProductTest

    ) AS p

    UNPIVOT

    (

    [Value] FOR Heading IN

    ([Company],[Product Name],[Initial Interest Rate],[Roll Rate],[ ])

    )AS UP

    ) AS UP1

    PIVOT

    (

    Max ([Value]) FOR RowNum in ([1],[2],[3])

    ) AS P1

    ORDER BY co_idx,

    CASE Heading

    WHEN 'Company' THEN 1

    WHEN 'Product Name' THEN 2

    WHEN 'Initial Interest Rate' THEN 3

    WHEN 'Roll Rate' THEN 4

    Else 5

    END

    Result:

    1CompanyTest1Test1Test1

    1Product NameProduct1Product2Product3

    1Initial Interest Rate8.758.758.75

    1Roll Rate9.769.769.76

    1

    2CompanyTest2NULLNULL

    2Product NameProduct1NULLNULL

    2Initial Interest Rate6.75NULLNULL

    2Roll Rate8.76NULLNULL

    2 NULLNULL

    3CompanyTest3NULLNULL

    3Product NameProduct1NULLNULL

    3Initial Interest Rate4.75NULLNULL

    3Roll Rate6.76NULLNULL

    3 NULLNULL

  • hi,

    Thanks for your reply, But I keep getting the same error message is there any other reason you can think of why its happening.

    Thanks, Shilpa.

  • sharonrao123 (5/2/2010)


    Thanks for your reply, But I keep getting the same error message is there any other reason you can think of why its happening.

    The (nicely correctly by Dohsan) posted code runs without error for me too.

    Unless you can demonstrate the problem, it's really hard to help.

  • hi,

    I just noticed why the problem is happening if I inlcude only the columns with number data it works no problem if I inlcude the columns with both character and number data. I am getting this error.

    for example the column solicit_fee has n/a in one of the row I am getting this error. Even though I have cast everything as varchar.

    Thanks, Shilpa.

  • Hi,

    I tried the code Doshan has given and am getting same error again.

    'Msg 8167, Level 16, State 1, Line 2

    The type of column "Initial Interest Rate" conflicts with the type of other columns specified in the UNPIVOT list.

    '

    @Shilpa - did you happen to get the solution for this. Please help, I have similar issue to resolve.

  • The reason this script is not working is because of a conflict in collation sequences. You are creating columns in your table #ProductTest with collation Latin1_General_CI_AS but this is not your database collation sequence. The script, as presented, will only work if the database collation happens to be Latin1_General_CI_AS

  • if all the columns as same data-type then only it's function.could you alter the table all column data type as nvarchar(25) . it's work ...

  • All columns have to be of the same data type AND length. That was the only way I got mine to work, by converting them all to varchar(50).

    If I had some columns varchar(3) AND some varchar(50) it crapped out.

  • All the columns has to be of same datatype AND Length.

    I faced the same issue for nvarchar columns and found that one of them was nvarchar(200) while all the others were nvarchar(255), after doing a CAST in the main select statement it worked for me...:-)

  • Thank you proof. You're post finally got the light bulb to come on.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply