CODEWITHSUNDEEP
sql
user6703592
user6703592

Reputation: 1136

Reorganize table in SQL

Here is the SQL query with the result table:

USE DataBase

SELECT * 
FROM table

enter image description here

I want to reorganize table that

  1. Use element of Category as column;

  2. The value of Ccy is from Str/Num, which depends on the Value Type (s means take value from str; n means take value from Num).

And here is the expected result:

enter image description here

I usually import table into Pandas. Not sure if it can be done directly in SQL?

Upvotes: 0

Views: 158

Answers (1)

EdmCoff
EdmCoff

Reputation: 3576

The basic idea for a pivot on this data is going to be something like:

SELECT *
FROM
(
 SELECT ccy, category, CASE WHEN value_type='s' THEN str ELSE CAST(num AS varchar(128)) END as val
 FROM mytable
) p
PIVOT 
(
 MAX(val)
 FOR category in ([A], [B], [C])
) pv
ORDER BY ccy DESC

The above works in SQL Server, but the syntax might be slightly different in your DBMS. You can see it working here. The biggest drawback is that you do need to list the possible values in category that you want converted to columns, so for more complicated problems you might end up using dynamic SQL.

Although a pivot is the correct way of doing this, I want to include a different version that also solves your problem:

WITH cte AS
 (SELECT ccy, category, CASE WHEN value_type='s' THEN str ELSE CAST(num AS varchar(128)) END as val
 FROM mytable)
SELECT
  ccy, 
  (SELECT MAX(val) FROM cte c2 WHERE c1.ccy = c2.ccy AND c2.category = 'A') A,
  (SELECT MAX(val) FROM cte c2 WHERE c1.ccy = c2.ccy AND c2.category = 'B') B,
  (SELECT MAX(val) FROM cte c2 WHERE c1.ccy = c2.ccy AND c2.category = 'C') C
FROM cte c1
GROUP BY ccy
ORDER BY ccy DESC

I'm including this since I think it is very easy to read and understand how you can create columns like this. If you are doing a quick one-off analysis and aren't used to pivot, I think things like this can be very useful. (But, I'm recommending the pivot example.)

Upvotes: 1

Related Questions