UNPIVOT Query Fails When A Second Column Name Prefix Is Introduced

Al C 41 Reputation points
2022-04-17T19:46:14.433+00:00

Good Afternoon,

I am sql server express newbie working with a table that has 250+ columns. Previously, I have used PowerBi to unpivot this wide table to the following four columns: prvdr_num, ffy, attribute, expense which were then imported into ms sql server express.

I would like to learn more about the unpivot operator in sql server so I found an unpivot query and through trial and error, adapted it to unpivot a sample table that I constructed from the original table with 250+ columns. Here is the query that I got to work:

SELECT *
FROM dbo.TESTLOAD_DIRECT3

SELECT prvdr_num,ffy,ATTRIBUTE,EXPENSES
FROM dbo.TESTLOAD_DIRECT3
UNPIVOT
(
EXPENSES
FOR ATTRIBUTE IN (expenses_empeebens_salary,expns_admin_and_general_salary,expns_maint_and_repairs_salary,expns_operation_of_plant_salary,expns_laundry_and_linen_salary,expns_housekeeping_salary,expns_dietary_salary,expns_cafeteria_salary,expns_maint_of_personnel_salary,expns_nursing_admin_salary,expns_central_srvcs_supps_salary,expns_pharmacy_salary,expns_medical_records_salary,expns_social_service_salary,expns_other_gen_service_salary,expns_nonphys_anesth_salary,expns_nursing_school_salary,expns_intern_res_salary_salary,expns_intern_res_other_salary,expns_paramedical_educ_salary)

)AS X

The above query worked perfectly. However, when I added more column titles with a different prefix, (there are five prefixes such as expns_, net_expenses, and total-cost in the table_) I received the following error message:

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

I am wondering if there is some way to let sql server know that it will be called upon to unpivot columns with five different prefixes?

Thank you for taking the time to read and respond to this question.

Al

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,117 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,583 questions
{count} votes

Accepted answer
  1. Naomi 7,366 Reputation points
    2022-04-17T20:33:50.92+00:00

    You can generate the command using dynamic SQL based on INFORMATION_SCHEMA.COLUMNS view to get information about your columns. However, it may be better to specify columns manually or semi-manually by still using that view to get all columns for the table first and then turn them into the query like the above.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 104.1K Reputation points MVP
    2022-04-17T20:14:26.027+00:00

    The prefix has nothing to with it. As far as SQL Server is concerned that is just a random combination of letters.

    But the data type has everything to do with it. You will have to bear with me, because I don't know the UNPIVOT operator. Rather, I prefer to write my unpivot operations as in this example:

    CREATE TABLE #temp (id         int NOT NULL PRIMARY KEY,
                        firstname  varchar(20) NOT NULL,
                        middlename varchar(20) NOT NULL,
                        lastname   varchar(20) NOT NULL,
                        birthdate date         NOT NULL)
    INSERT #temp (id, firstname, middlename, lastname, birthdate)
      VALUES(1, 'Gusten', 'Eriksson', 'Nilsson', '1976-04-03'),
            (2, 'Gro', 'Harlem', 'Bruntland', '1939-04-03'),
            (3, 'Richard', 'Millhouse', 'Nixon', '1920-03-08')
    
    SELECT t.id, t.birthdate, u.nametype, u.name
    FROM   #temp t
    CROSS  APPLY (VALUES('First', t.firstname),
                        ('Middle', t.middlename),
                        ('Last', t.lastname)) AS u(nametype, name)
    ORDER BY t.id
    

    As you see here I unpivot the three name columns to a single column and that sort of makes sense. But I don't pivot the date column, because what would the date mean among all the names? A column normally holds a single attribute of the entity that the table models.

    Please keep in mind that I don't know anything about your data, and maybe it makes perfect sense to mix all these source columns in a single column. Maybe the only difference is that the column with the new prefix has a different, but related data type, and mixing makes sense. In that case, you may have to introduce a type conversion to get things right.

    To go back to my example, say that I would after all like to get the date in there. This is how I need to do it:

    SELECT t.id, u.nametype, u.name
    FROM   #temp t
    CROSS  APPLY (VALUES('First', t.firstname),
                        ('Middle', t.middlename),
                        ('Last', t.lastname),
                        ('Date', convert(char(10), t.birthdate))) AS u(nametype, name)
    ORDER BY t.id
    

  2. LiHong-MSFT 10,046 Reputation points
    2022-04-18T03:06:59.443+00:00

    Hi @Al C

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

    This error message means that you need to uniformed data type & size of all columns in the IN_list in the source data set.

    What made me conclude that it was the prefixes is that I set the data types to bigint while importing the table into sql server

    You need to run this query, and check if the columns to be UNPIVOT are of the same datatype.

    SELECT COLUMN_NAME,DATA_TYPE  
    FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE TABLE_NAME='Your_TableName'  
    

    If, as you said, all columns are BIGINT, then using UNPIVOT will not receive this error.
    If some of the columns have different datatypes, then you need to use a subquery to modify their datatypes to BIGINT before UNPIVOT.

    SELECT prvdr_num,ffy,ATTRIBUTE,EXPENSES  
    FROM ( SELECT  prvdr_num,ffy,  
                   ...  
                   --columns with same datatype  
                   ...  
                   CAST(column1 AS BIGINT) column1,  
                   CAST(column2 AS BIGINT) column2,  
                   ...  
           FROM dbo.TESTLOAD_DIRECT3  ) d  
    UNPIVOT  
    (  
    EXPENSES  
    FOR ATTRIBUTE IN(expenses_empeebens_salary,expns_admin_and_general_salary,......)  
    )un;  
    

    Considering that you have more than 200 columns, you could run the following query and then copy the column names.

    DECLARE @column_names VARCHAR(MAX)  
      
    SELECT @column_names = ISNULL(@column_names + ',','') + QUOTENAME(COLUMN_NAME)   
    FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE TABLE_NAME='Your_TableName'  
      
    PRINT @column_names  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.