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
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 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
SSC Eights!
SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)

Group: General Forum Members
Points: 920 Visits: 3482
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
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 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-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34820 Visits: 11359
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
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 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 Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 60
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1169 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
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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