SQL query to group a table into table of tables?

user_2758656 0
Viewed 59

I'm in the middle of "translating" Power BI queries into SQL queries. One of the key queries does the following:

  1. Takes the following table:
+-----+-----+----------+
| PID | FID | Quantity |
+-----+-----+----------+
| 1   | A   | 15       |
+-----+-----+----------+
| 1   | B   | 2        |
+-----+-----+----------+
| 2   | B   | 3        |
+-----+-----+----------+
| 2   | D   | 8        |
+-----+-----+----------+
| 3   | C   | 2        |
+-----+-----+----------+
  1. Group by PID, keeping all rows (each Data is a table):
+-----+-------+
| PID | Table |
+-----+-------+
| 1   | Data  |
+-----+-------+
| 2   | Data  |
+-----+-------+
| 3   | Data  |
+-----+-------+
  1. Runs some custom logic/manipulation (some of which are very complicated, so not just COUNT or MAX, etc.) per Data, and turns each Data into a new table per row:
+-----+-------+-------------+
| PID | Table | Transformed |
+-----+-------+-------------+
| 1   | Data  | Data        |
+-----+-------+-------------+
| 2   | Data  | Data        |
+-----+-------+-------------+
| 3   | Data  | Data        |
+-----+-------+-------------+
  1. Expand the Transformed column:
+-----+----------------+----------------+
| PID | ResultColumn-1 | ResultColumn-2 |
+-----+----------------+----------------+
| 1   | SomeResult-1   | SomeResult-1b  |
+-----+----------------+----------------+
| 2   | SomeResult-2   | SomeResult-2b  |
+-----+----------------+----------------+
| 3   | SomeResult-3   | SomeResult-3b  |
+-----+----------------+----------------+

Is this procedure possible to do in SQL? I was thinking we could use Group By with some custom function, but not entirely sure how to go about doing that - any guidance would be greatly appreciated.

Thanks!

Sample Output:

+-----+------------------+----------------+
| PID | ResultColumn-1   | ResultColumn-2 |
+-----+------------------+----------------+
| 1   | There are 2 of B | and 15 of A    |
+-----+------------------+----------------+
| 2   | There are 8 of D | and 3 of B     |
+-----+------------------+----------------+
| 3   | There are 2 of C |                |
+-----+------------------+----------------+
powerbi pivot group-by sql string
2 Answers

You can do this with row_number() and conditional aggregation:

select 
    pid,
    max(case when rn = 1 then concat('There are ', quantity, ' of ', fid) end) res1,
    max(case when rn = 2 then concat('And ', quantity, ' of ', fid) end) res2
from (
    select 
        t.*,
        row_number() over(partition by pid order by fid desc) rn
    from mytable t
) t
group by pid

Note that this assumes not more than 2 rows per pid, as shown in your data. Otherwise you would probably need to expand the select clause with more conditional expressions.

user_106767160

Yes it's possible using JOIN to join between them, and then you can group your results or do further process on them.

Your initial query would be something like :

SELECT 
    PID 
,   Table 
,   Transformed 
,   ResultColumn-1
,   ResultColumn-2
FROM 
    table1 t1
LEFT JOIN table2 t2 ON t2.PID = t1.PID 
LEFT JOIN table3 t3 ON t3.PID = t1.PID 
LEFT JOIN table4 t4 ON t4.PID = t1.PID

That is if the PID Is primary key which is used as a foreign key in the other tables.

If you provided the expected results, then we can give you an initial query that would produce it, which you can use as a kick-starter !

user_23220990
Related