how to use unpivot in sql?

Question

0.00/5 (No votes)

See more:

please help me to solve this error...

SQL

SELECT
    *
FROM
(
    SELECT t.N_Srno, t.N_Visitorder,
        t.N_workedwithmcr, t.N_MCLshow,
        t.N_MtpSbmMcr, t.N_MtpSbmDcr,
        t.N_MtpappMcr, t.N_MtpappDcr,
        t.N_Stpsubmitiondcr, t.N_PriTargetOn,
        t.N_Doctortime, t.n_mscexpences,
        t.N_SecTargeton, t.N_mtpsubarea,
        t.c_prefixTN, t.c_prefixGDN,
        t.N_year, t.N_secvalue,
        t.N_Stpsubmitionmcr, t.N_TargItemBrand,
        t.n_closingday, t.n_pri_inv_imp,
        t.c_tacode, t.c_dacode, t.c_monthly
    FROM tbl_controlpanel_mst as t
) AS SourceTable
UNPIVOT
(
    Value FOR Col IN (N_Srno, N_Visitorder,
        N_workedwithmcr, N_MCLshow,
        N_MtpSbmMcr, N_MtpSbmDcr,
        N_MtpappMcr, N_MtpappDcr,
        N_Stpsubmitiondcr, N_PriTargetOn,
        N_Doctortime, n_mscexpences,
        N_SecTargeton, N_mtpsubarea,
        c_prefixTN, c_prefixGDN,
        N_year, N_secvalue,
        N_Stpsubmitionmcr, N_TargItemBrand,
        n_closingday, n_pri_inv_imp,
        c_tacode, c_dacode,
        c_monthly
)
) AS unpvt

I am getting error of

Msg 8167, Level 16, State 1, Line 39
The type of column "N_Visitorder" conflicts with the type of other columns specified in the UNPIVOT list.

please tell me..

Posted 26-Oct-15 21:17pm

Member 11337367

Updated 26-Oct-15 21:25pm

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

The error message is pretty obvious. You should CAST or CONVERT[^] data into destination data type.

Seems, you're trying to unpivot too many columns with different data type. I'd suggest you to read these MSDN articles:
Unpivot Transformation[^]
Using PIVOT and UNPIVOT[^]

Tons of very interesting articles you'll find on CP Knowledge Base[^] too.

Posted 26-Oct-15 21:30pm

Maciej Los

Comments

Member 11337367 27-Oct-15 3:34am

can you show me to write for one column to convert to original column datatye..

Maciej Los 27-Oct-15 3:35am

Follow the links to find out how to unpivot data.

Member 11337367 27-Oct-15 3:39am

I didn't get the answer in that which i was looking for..so i have asked you..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PANKAJMAURYA · Accepted Answer · 2015-10-26T22:49:00

Solution 3

Try below query

SELECT
    *
FROM
(
    SELECT t.N_Srno, t.N_Visitorder,
        t.N_workedwithmcr, t.N_MCLshow,
        t.N_MtpSbmMcr, t.N_MtpSbmDcr,
        t.N_MtpappMcr, t.N_MtpappDcr,
        t.N_Stpsubmitiondcr, t.N_PriTargetOn,
        t.N_Doctortime, t.n_mscexpences,
        t.N_SecTargeton, t.N_mtpsubarea,
        t.c_prefixTN, t.c_prefixGDN,
        t.N_year, t.N_secvalue,
        t.N_Stpsubmitionmcr, t.N_TargItemBrand,
        t.n_closingday, t.n_pri_inv_imp,
        t.c_tacode, t.c_dacode, t.c_monthly
    FROM tbl_controlpanel_mst as t
) AS SourceTable
UNPIVOT
(
    Value FOR Col IN (N_Visitorder,
        N_workedwithmcr, N_MCLshow,
        N_MtpSbmMcr, N_MtpSbmDcr,
        N_MtpappMcr, N_MtpappDcr,
        N_Stpsubmitiondcr, N_PriTargetOn,
        N_Doctortime, n_mscexpences,
        N_SecTargeton, N_mtpsubarea,       
        N_year, N_secvalue,
        N_Stpsubmitionmcr, N_TargItemBrand,
        n_closingday, n_pri_inv_imp        
)
) AS unpvt

Posted 26-Oct-15 22:49pm

PANKAJMAURYA

Updated 26-Oct-15 23:08pm

v2

Comments

Member 11337367 27-Oct-15 4:52am

this is the error..why u didn't metion n_srno column

Msg 8167, Level 16, State 1, Line 31
The type of column "c_tacode" conflicts with the type of other columns specified in the UNPIVOT list.

[no name] 27-Oct-15 4:54am

you have not posted full table . You missed the column c_tacode, c_dacode, c_monthly in your create table script post .

[no name] 27-Oct-15 4:59am

n_srno can not be included as its datatype is numeric. To Include it you have to cast it to int cause all the columns you are going to include will be of same datatype.

Member 11337367 27-Oct-15 5:06am

please take this

CREATE TABLE [dbo].[Tbl_ControlPanel_Mst](
[N_Srno] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[N_Visitorder] [int] NOT NULL,
[N_workedwithmcr] [int] NULL,
[N_MCLshow] [int] NOT NULL,
[N_MtpSbmMcr] [int] NOT NULL,
[N_MtpSbmDcr] [int] NOT NULL,
[N_MtpappMcr] [int] NOT NULL,
[N_MtpappDcr] [int] NOT NULL,
[N_Stpsubmitiondcr] [int] NULL,
[N_PriTargetOn] [int] NULL,
[N_Doctortime] [int] NULL,
[n_mscexpences] [int] NULL,
[N_SecTargeton] [int] NULL,
[N_mtpsubarea] [int] NOT NULL,
[c_prefixTN] [varchar](5) NULL,
[c_prefixGDN] [varchar](5) NULL,
[N_year] [int] NULL,
[N_secvalue] [int] NULL,
[N_Stpsubmitionmcr] [int] NULL,
[N_TargItemBrand] [int] NULL,
[n_closingday] [int] NULL,
[n_pri_inv_imp] [int] NULL,
[c_tacode] [varchar](50) NULL,
[c_dacode] [varchar](50) NULL,
[c_monthly] [varchar](10) NULL

[no name] 27-Oct-15 5:09am

Use the updated one. Hope will work.

Member 11337367 27-Oct-15 5:18am

Thank thats fine..i want to make this column c_prefixTN in the list
..

[no name] 27-Oct-15 5:24am

Please mark as Answer if it helps you .

[no name] 27-Oct-15 5:48am

Thanks....

Member 11337367 27-Oct-15 5:55am

now u plese tell me how i can i add c_prefixTN into list..

[no name] 27-Oct-15 5:58am

Is it having only number value or string also ?

Member 11337367 27-Oct-15 6:14am

both number and character ..

[no name] 27-Oct-15 6:16am

Then its not possible cause you will unpivot only one type of values at a time.

Member 11337367 27-Oct-15 6:17am

we can use varchar data type for that column..

[no name] 27-Oct-15 6:23am

Then you have to include only varchar datatype columns with same lenth like varchar(5)

Member 11337367 27-Oct-15 6:29am

can you tell me what you did for this column N_Visitorder when we gets error..

[no name] 27-Oct-15 6:39am

I did not do much only exclude all the columns which have different datatype than int.

Member 11337367 27-Oct-15 6:42am

if i want to add that column what i have to do?

[no name] 27-Oct-15 6:49am

You can not add that column in this list.

Member 11337367 27-Oct-15 6:53am

no..i have to include that column also..

Member 11337367 27-Oct-15 7:30am

hii
if some column having null value is not coming
why is it like that