UNPIVOT Query Fails When A Second Column Name Prefix Is Introduced

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.