Postgresql – compress two rows with a difference in two columns into a single row with both columns

pivotpostgresqlqueryrow

I have a query that returns customer product information that shows which region a product is sourced from.

Query:

SELECT 
    prod.prod_id, 
    prod.is_source,
    link.prod_link_disabled, 
    loc.location_id, 
    loc.customer_id, 
    cust.customer_name
FROM 
    product prod, 
    product_link link, 
    region region, 
    location loc, 
    customer cust
WHERE 
    prod.prod_id = link.prod_id
    AND link.region_id = region.region_id
    AND region.loc_id = loc.loc_id
    AND link.prod_link_deleted = FALSE
    AND loc.customer_id = customer_id
    AND prod.prod_id = 5874;

This returns the following result:

| prod_id             | is_source | prod_link_disabled | location_id | customer_id | customer_name   |
| ------------------- | --------- | ------------------ | ----------- | ----------- | --------------- |
| Test Product - 5874 |         t |                  f |           2 |         332 | Test Playground |
| Test Product - 5874 |         f |                  f |           1 |         332 | Test Playground |

This is showing that product 5874 is coming from location 2 (because is_source is true).

I'd like to compress this information into a single row. I'd like a result that looks like this:

| prod_id             | prod_link_disabled | src_location_id | dest_location_id | customer_id | customer_name   |
| ------------------- | ------------------ | --------------- | ---------------- | ----------- | --------------- |
| Test Product - 5874 |                  f |               2 |                1 |         332 | Test Playground |

This compressed the two previous rows into a single row where src_location_id is 2 (again because that row had is_source as true) and dest_location_id is 1

How can I pivot the two row result into a single row?

It is safe to assume that there are always only two results in the first query.

Best Answer

Do I understand correctly and you need something like:

SELECT prod1.prod_id, 
       link1.prod_link_disabled, 
       loc1.location_id AS src_location_id, 
       loc1.location_id AS dst_location_id, 
       loc1.customer_id, 
       cust1.customer_name
  FROM product AS prod1 
  JOIN product_link AS link1 ON link1.prod_id = prod1.prod_id
  JOIN region AS region1 ON region1.region_id = link1.region_id
  JOIN location AS loc1 ON loc1.loc_id = region1.loc_id
  JOIN customer AS cust1 ON cust1.customer_id = loc1.customer_id
  JOIN product AS prod2 ON prod2.prod_id = prod1.prod_id
  JOIN product_link AS link2 ON link2.prod_id = prod2.prod_id
  JOIN region AS region2 ON region2.region_id = link2.region_id
  JOIN location AS loc2 ON loc2.loc_id = region2.loc_id
  JOIN customer AS cust2 ON cust2.customer_id = loc2.customer_id
 WHERE prod1.is_source AND
       NOT link1.prod_link_deleted AND
       NOT prod2.is_source AND
       NOT link2.prod_link_deleted AND
       prod1.prod_id = 5874;

Sure, you can remove excessive JOINs.

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Sql-server – Transforming multiple rows into a single row

You must pivot your data.

This can be done using the Pivot operator:

SELECT ID, [date]
    , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4
    , total as [Sum]
FROM (
    SELECT * FROM data d
    CROSS APPLY (SELECT total = SUM([money]) FROM data
        WHERE [date] = d.[date] AND ID = d.[id]) a
) t
PIVOT (
    MAX([money])
    FOR [hour] IN ([1], [2], [3], [4])
) piv

You are using SQL Server 2005 and therefore the total is calculated using CROSS APPLY. See SQL Fiddle.

Without PIVOT, you can also use a GROUP BY with CASEs:

SELECT ID, [date]
    , Hour1 = SUM(CASE WHEN [hour] = 1 THEN [money] END)
    , Hour2 = SUM(CASE WHEN [hour] = 2 THEN [money] END)
    , Hour3 = SUM(CASE WHEN [hour] = 3 THEN [money] END)
    , Hour4 = SUM(CASE WHEN [hour] = 4 THEN [money] END)
    , [Sum] = SUM([money])
FROM data d
GROUP BY ID, [date]

See SQL Fiddle.

With SQL Server 2012 you could easily calculate the total using the SUM(...) OVER(...) window function:

SELECT ID, [date], [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4, [Sum]
FROM (
       SELECT *, [Sum] = SUM([money]) OVER(PARTITION BY [date], ID) FROM data
) d
PIVOT (
       MAX([money])
       FOR [hour] IN ([1], [2], [3], [4])
) piv

See SQL Fiddle.

Note that a window function with an aggregate such as SUM(...) OVER(...) also works with SQL Server 2005 when the OVER(...) clause only contains a PARTITION BY ... and no ORDER BY .... See OVER Clause (2005) (Thanks Andriy M for the link).

Output:

ID  date    Hour1   Hour2   Hour3   Hour4   Sum
1   20-1    10      20      30      40      100
1   21-1    50      10      20      30      110
2   20-1    40      50      10      20      120

Best Answer

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

Sql-server – Transforming multiple rows into a single row

Related Question