1

I have the following data and to need to transpose it:

Job   CRSE    Count
O   UG_UPPER    8
O   UG_LOWER    4
O   GR          0
S   UG_LOWER    1
T   UG_UPPER    21
T   UG_LOWER    4
T   GR          9
TA  UG_UPPER    0
TA  UG_LOWER    0

into the following:

Job         O           O       O      S           T          T         T      TA          TA
CRSE    UG_LOWER    UG_UPPER    GR  UG_LOWER    UG_LOWER    UG_UPPER    GR  UG_LOWER    UG_UPPER
Count       4           8       0      1           4          21        9       0          0

Can it be done in one SQL statement in MySQL ??

How about below simplified input:

Job  Count
O    41.5
S     0
T    5.5
TA   21

to be turned into:

Job      O      S    T    TA
Count   41.5    0   5.5   21
jean
  • 89
  • 1
  • 7
  • 1
    This transformation doesn't make sense in SQL. For instance, a column contains only a single type. – Gordon Linoff Dec 11 '17 at 13:26
  • so, it is not possible ? – jean Dec 11 '17 at 13:39
  • How about if I simplify the above input, please refer to above new sample\ – jean Dec 11 '17 at 13:39
  • You aren't transforming the data at all. You've just transposed columns and rows, but the data is the same. This should be accomplished in the presentation layer of your application. It will be unbelievably complicated to do with SQL, which has strict requirements on rows and strict requirements on columns. – Bacon Bits Dec 11 '17 at 13:48
  • 1
    You can _probably_ achieve this with some [hacks and tricks](https://stackoverflow.com/questions/3392956/sql-how-to-transpose) in MySQL, but it will fail when you have multiple values / inconsistent data or missing values. Besides - if you want to "transpose" a 100k+ rows table - you will simply hit [the limitation](https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html) of the DBMS most likely. Tip: define your data schema first according to your business constraints. Don't battle the [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Alma Do Dec 11 '17 at 13:50
  • @jean . . . It is not reasonable. You would need to explain why you want to transform numbers into strings, for instance. – Gordon Linoff Dec 12 '17 at 02:31
  • Hello, I am just transposing columns into rows since I have a huge survey to submit and they require their own format as outlined in the output file – jean Dec 13 '17 at 06:30

0 Answers0