How to pivot Text columns?

Question

Our BA gave us Excel sheet with Two columns, column1 and column2 which I loaded in SQL Table. column1 contains distinct Values EmployeeID, FirstName, LastName, Address and column2 contains respected values.

How do I convert Column1 distinct Values to Column so my table will have EmployeeID, FirstName, LastName, and Address as columns?

File

Column1     || Column2
EmployeeID  || 00251
FirstName   || Joe
LastName    || Reed
Address     || 101 5th Ave
EmployeeID  || 00145
FirstName   || Jen
LastName    || Smith
Address     || 1001 Henderson RD

I am using the following query but it's giving an error.

  SELECT pt.EmployeeID , pt.FirstName, pt.LastName, pt.[Address]
  FROM 
    (
        SELECT *
        FROM FlatFile.UserList

    ) AS temp
        PIVOT
        (
            MAX([COLUMN2])
            FOR [COLUMNE1] IN ('EmployeeID','FirstName','LastName:','[Address]')

        ) as pt

Error:

Error Message:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'EmployeeID'.

Is there any reason that you're keeping the error message a secret? — MatBailie, Feb 02 '18 at 15:36
@BHouse I received an excel file which I loaded it into SQL Table. Thank you ... — krunal Patel, Feb 02 '18 at 15:52
Typo? `[COLUMNE1]` in the SQL and `COLUMN1` in the example data. — MatBailie, Feb 02 '18 at 15:53
The list of fields shouldn't be strings. Remove the `'`... https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — MatBailie, Feb 02 '18 at 15:55

score 2 · Answer 1 · answered Feb 02 '18 at 15:57

2

Fix the typo COLUMNE1 to COLUMN1, and remove the string quotes from the field list?

 SELECT pt.EmployeeID , pt.FirstName, pt.LastName, pt.[Address]
  FROM 
    (
        SELECT *
        FROM FlatFile.UserList

    ) AS temp
        PIVOT
        (
            MAX([COLUMN2])
            FOR [COLUMN1] IN ([EmployeeID],[FirstName],[LastName],[Address])
        ) as pt

SQL Server: Examples of PIVOTing String data

answered Feb 02 '18 at 15:57

MatBailie

83,401
18
103
137

Thank you !! It gave me only one value, I know I was using max but how should I get all values, the file has 3000 Employee Infomation. – krunal Patel Feb 02 '18 at 16:11
You need to reformat your input data. Every row for the same employee needs a column that identifies it as being the same employee. So, for example, three columns : employee_id, column_name, data_value. Best read the links I gave for examples. – MatBailie Feb 02 '18 at 16:16

How to pivot Text columns?

1 Answers1