Pivot without aggregation in SQL

Question

I am trying to figure out how to pivot column to row without aggregation.

I have a table with Account, Company Name and Company %. I am trying to pivot so that all companies info with respect to that account are on the same row. There isn't a limit on how many companies an account could have and I will need to include all companies.

Currently:

Account	Company Name	Company %
1	Company A	Company A%
1	Company B	Company B%
2	Company B	Company B%
3	Company A	Company A%
3	Company B	Company B%
3	Company K	Company K%
3	Company W	Company W%

Expected Results:

Account	Company 1	Company 1%	Company 2	Company 2%	Company 3	Company 3%	Company 4	Company 4%
1	Company A	Company A%	Company B	Company B%
2	Company B	Company B%
3	Company A	Company A%	Company B	Company B%	Company K	Company K%	Company W	Company W%

Also, does it need to be a true pivot with actual columns, or would something like listagg be acceptable? e.g. 2, "Company B, Company B%" — EdmCoff
– EdmCoff, Commented Jun 21, 2021 at 18:29

MatBailie · Accepted Answer · 2021-06-21 19:21:36Z

2

Any modern implementation of SQL has ROW_NUMBER() and that can be used for what you want.

It still uses MAX() to collapse multiple rows in to single rows, but it is being used as pick the one value that isn't NULL.

There is no sane reason to avoid its use.

WITH
  sorted AS
(
  SELECT
    yourTable.*,
    ROW_NUMBER() OVER (PARTITION BY account ORDER BY company_name)  AS account_row_id
  FROM
    yourTable
)
SELECT
  account,
  MAX(CASE WHEN account_row_id = 1 THEN company_name END)   AS company_name_1,
  MAX(CASE WHEN account_row_id = 1 THEN company_pct  END)   AS company_pct_1,

  MAX(CASE WHEN account_row_id = 2 THEN company_name END)   AS company_name_2,
  MAX(CASE WHEN account_row_id = 2 THEN company_pct  END)   AS company_pct_2,

  MAX(CASE WHEN account_row_id = 3 THEN company_name END)   AS company_name_3,
  MAX(CASE WHEN account_row_id = 3 THEN company_pct  END)   AS company_pct_3,

  MAX(CASE WHEN account_row_id = 4 THEN company_name END)   AS company_name_4,
  MAX(CASE WHEN account_row_id = 4 THEN company_pct  END)   AS company_pct_4
FROM
  sorted
GROUP BY
  account
ORDER BY
  account

answered Jun 21, 2021 at 19:21

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLROOKIE Over a year ago

Sorry, it took me a long time to test this but it worked. Thank you so much! I tried to upvote but it said i need 15 reputation points. Also how do i accept this as the answer?

Collectives™ on Stack Overflow

Pivot without aggregation in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related