SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Column type conflict in Unpivot list


Column type conflict in Unpivot list

Author
Message
sharonrao123
sharonrao123
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 205
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_idx Heading value1 value2 value3
1 Company Test1 Test1 Test1
1 Product Name Product1 Product2 Product3
1 Initial Interest Rate 8.75 8.75 8.75
1 Roll Rate 9.76 9.76 9.76
1
2 Company Test2
2 Product Name Product1
2 Initial Interest Rate 6.75
2 Roll Rate 8.76
2
3 Company Test3
3 Product Name Product1
3 Initial Interest Rate 4.75
3 Roll Rate 6.76
3






Thanks, Shilpa.
Dohsan
Dohsan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 3510
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_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 #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:

1 Company Test1 Test1 Test1
1 Product Name Product1 Product2 Product3
1 Initial Interest Rate 8.75 8.75 8.75
1 Roll Rate 9.76 9.76 9.76
1
2 Company Test2 NULL NULL
2 Product Name Product1 NULL NULL
2 Initial Interest Rate 6.75 NULL NULL
2 Roll Rate 8.76 NULL NULL
2 NULL NULL
3 Company Test3 NULL NULL
3 Product Name Product1 NULL NULL
3 Initial Interest Rate 4.75 NULL NULL
3 Roll Rate 6.76 NULL NULL
3 NULL NULL


sharonrao123
sharonrao123
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 205
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.
Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79328 Visits: 11400
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.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sharonrao123
sharonrao123
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 205
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.
nehalpanchamia
nehalpanchamia
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 62
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.
steve-893342
steve-893342
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 2670
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
vigoudhaya
vigoudhaya
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 107
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 ...
proof
proof
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 95
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.
santanu_dutta84
santanu_dutta84
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 93
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...:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search