CODEWITHSUNDEEP
sqlformatrow
Quiron
Quiron

Reputation: 370

Need row as column

I am working with SQL and I need some help with the following table:

      ID_enterprise | ID_workplace | Service | Address | N_Workers |
           1              1            X         A1         50       
           1              1            Y         A2         50
           2              2            Z         A3         10
           2              3            X         A4         5
           3              4            Z         A5         5

And I need this format:

      ID_enterprise | X | Y | Z | Address1 | Address 2 | Address 3 | Address 4 | AddressN | N_Workers1 | N_Workers2 | N_Workers3 | N_WorkersN | T_Workers
           1          1   1   0      A1        A2          NULL        NULL        NULL        50           50          NULL           NULL        100
           2          1   0   1      A3        A4          NULL        NULL        NULL        10           5           NULL           NULL        15
           3          0   0   1      A5        NULL        NULL        NULL        NULL        5            0           NULL           NULL        5

The AddressN and N_WorkersN are for enterprises which have more than 3 workcenters (The biggest has 9). So I think that I need something dynamic...

Sorry about my English and my poor explanation! Thanks!

Upvotes: 0

Views: 40

Answers (1)

Jacek Trociński
Jacek Trociński

Reputation: 920

You can use PIVOT, here's an example query using a subset of the data you provided.

WITH input_data AS (
  SELECT 1 AS id, 'X' AS service FROM DUAL
  UNION ALL
  SELECT 1 AS id, 'Y' AS service FROM DUAL
  UNION ALL
  SELECT 2 AS id, 'Z' AS service FROM DUAL
  UNION ALL
  SELECT 2 AS id, 'X' AS service FROM DUAL
  UNION ALL
  SELECT 3 AS id, 'Z' AS service FROM DUAL
)
SELECT * FROM input_data
PIVOT (
  MAX(service) FOR service IN ('X' AS x, 'Y' AS y, 'Z' AS z)
)

Upvotes: 1

Related Questions