CODEWITHSUNDEEP
sqlsql-servert-sql
spiderman
spiderman

Reputation: 1585

SQL Server : how to transpose rows into columns

In T-SQL I am trying to transpose the values of some rows into columns.
The initial table:

Project | Machine | Value
-------------------------
A   X   100
A   Y   99
A   Z   98
A   W   97
B   X   97
B   Y   96
B   W   95
C   X   95
C   Z   94

The result I'd like to have:

Project     MX  MY  MZ  MW
-----------------------------
A           100 99  98  97
B            97 96   0  95
C            95  0  94   0

I have created test code:

CREATE TABLE Company (project char(1), machine char(1), cost int)
GO

INSERT INTO Company 
VALUES ('A', 'X', 100), ('A', 'Y', 99), ('A', 'Z', 98),
       ('A', 'W', 97), ('B', 'X', 97), ('B', 'Y', 96),
       ('B', 'W', 95), ('C', 'X', 95), ('C', 'Z', 94);

My pivot query:

SELECT
    project, 'X', 'Y', 'Z', 'W'
FROM
    (SELECT 
         project, machine, cost 
     FROM 
         Company) p
PIVOT
    (MAX(cost)
        FOR machine IN ('X', 'Y', 'Z', 'W') AS  pvt

I get this error:

Incorrect syntax near ''X''

Is my pivot query wrong?

Thanks in advance

Upvotes: 4

Views: 2692

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If you have a limited machines, then i would do conditional aggregation instead of PIVOT:

SELECT Project,
       MAX(CASE WHEN machine = 'X' THEN cost END) AS MX,
       MAX(CASE WHEN machine = 'Y' THEN cost END) AS MY,
       MAX(CASE WHEN machine = 'Z' THEN cost END) AS MZ,
       MAX(CASE WHEN machine = 'W' THEN cost END) AS MW
FROM Company c
GROUP BY Project;

If you want to go with PIVOT, then i would fix the syntax error with missing closing parenthesis :

SELECT * 
FROM (SELECT project, machine, cost 
      FROM Company 
     ) AS p PIVOT
     (MAX(cost)
      FOR machine IN ([X], [Y], [Z], [W]) 
     ) AS pvt;

Note :

  • Don't use single quote as column name use square brackets instead.

Upvotes: 3

Related Questions