I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions.
Example:
I have a table with the columns:
ID int,
ISO char(2),
Text varchar(255).
So I have this:
ID ISO Text -- --- ---- 1 DE Auto 2 EN Car
I'd like to get the following:
ID EN DE -- --- ---- 1 Car Auto
How do you accomplish that?
This answer is really frantisek's, I'm just copying here to correct the mistake (I can't edit directly).
Basically, you use that solution, with a tweak:
SELECT
max(DE) as DE, max(EN) as EN
FROM
test
PIVOT (MAX([text]) FOR ISO in (DE,EN)) p
This will get the content into a single row. Also, it drops the ID, since it doesn't make sense if you want a single row (there is no logic to indicate what to do with it when combining into a single row).
Also, the assumption is made that the values in the ISO column are unique, otherwise, this will lose data due to the MAX
aggregate.
I found the solution as the following:
SELECT
ID, DE, EN
FROM
TextTable
PIVOT(MAX([text]) FOR ISO IN (DE,EN)) p
It's possible to use PIVOT with MAX aggregating function over the text.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With