Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Column type conflict in Unpivot list Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 11:21 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
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.
Post #913419
Posted Friday, April 30, 2010 3:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:59 AM
Points: 356, Visits: 2,607
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

Post #913533
Posted Sunday, May 2, 2010 11:52 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
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.
Post #914471
Posted Monday, May 3, 2010 12:39 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 9,923, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #914479
Posted Monday, May 3, 2010 6:33 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
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.
Post #915019
Posted Wednesday, July 21, 2010 10:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 5:55 AM
Points: 12, Visits: 58
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.
Post #956923
Posted Friday, July 23, 2010 8:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 2:08 PM
Points: 405, Visits: 2,670
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
Post #958005
Posted Monday, August 9, 2010 1:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, May 4, 2014 2:09 AM
Points: 58, 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 ...
Post #965754
Posted Thursday, March 24, 2011 1:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 10:32 AM
Points: 5, Visits: 92
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.
Post #1083559
Posted Monday, April 9, 2012 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 4:36 PM
Points: 1, 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...
Post #1280314
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse