Member Avatar for anand9796

How to use pivoting WIth join of 3 tables without Aggregate function within it

  • 2 Contributors
  • 2 Replies
  • 230 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by alan.davies

Dani AI

Generated

asked how to pivot across three joined tables without using aggregate functions; as noted, the exact approach depends on the RDBMS and the cardinality of the data. If each (master, pivot-key) pair has at most one value, the usual non-aggregate patterns are: 1) a filtered LEFT JOIN per pivot column, or 2) correlated scalar subqueries. If there can be multiple rows per pivot-key but only one should be shown, a window function (ROW_NUMBER) can be used to pick the single row before joining — ROW_NUMBER is not an aggregate.

Filtered-LEFT-JOIN pattern (works in most SQL dialects; fast when the pivot set is small):

SELECT a.id,
       b.some_col,
       c1.val AS PivotA,
       c2.val AS PivotB,
       c3.val AS PivotC
FROM TableA a
JOIN TableB b ON b.a_id = a.id
LEFT JOIN TableC c1 ON c1.b_id = b.id AND c1.key = 'PivotA'
LEFT JOIN TableC c2 ON c2.b_id = b.id AND c2.key = 'PivotB'
LEFT JOIN TableC c3 ON c3.b_id = b.id AND c3.key = 'PivotC';

Correlated-subquery alternative (scalar subqueries per column; syntactic variant: LIMIT 1 on MySQL/Postgres or TOP 1 on SQL Server):

SELECT a.id,
       b.some_col,
       (SELECT val FROM TableC c WHERE c.b_id=b.id AND c.key='PivotA' LIMIT 1) AS PivotA,
       ...
FROM TableA a
JOIN TableB b ON b.a_id=a.id;

ROW_NUMBER approach to handle duplicates without aggregation:

WITH ranked AS (
  SELECT c.*, ROW_NUMBER() OVER (PARTITION BY c.b_id, c.key ORDER BY c.updated_at DESC) rn
  FROM TableC c
)
SELECT a.id, b.some_col, rA.val AS PivotA
FROM TableA a
JOIN TableB b ON b.a_id=a.id
LEFT JOIN ranked rA ON rA.b_id=b.id AND rA.key='PivotA' AND rA.rn=1;

Notes and caveats: repeated LEFT JOINs or subqueries become expensive when pivot columns are many or when TableC has many rows — add indexes on the join/filter columns. If pivot columns are dynamic, dynamic SQL (or the DB’s PIVOT operator) is usually required; be aware that SQL Server’s PIVOT expects an aggregate (MIN/MAX on unique values is a common workaround).

Recommended Answers

All 2 Replies

Member Avatar for alan.davies

Why do you need to avoid aggregate functions? What data are you trying to pivot? No info supplied so it's v difficult to help you.

commented: I just want to display boolean value in pivoted column +0
Member Avatar for alan.davies

May be my display, but I can't see any tags so not sure which rdbms you're using. Each flavour of sql deals with different syntax. Also need an idea of table structure and joins. Nothing much to go on.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.