April 29, 2010 at 11:21 pm
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.
April 30, 2010 at 3:15 am
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
May 2, 2010 at 11:52 pm
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.
May 3, 2010 at 12:39 am
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
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 6:33 pm
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.
July 21, 2010 at 10:42 pm
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.
July 23, 2010 at 8:32 am
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
August 9, 2010 at 1:35 am
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 ...
March 24, 2011 at 1:13 pm
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.
April 9, 2012 at 12:01 pm
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...:-)
December 4, 2013 at 3:58 pm
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