I have a few tables like this where a person has multiple data rows. The IDs are sequential but do not always start at 1. Is there a way to have the results come out in a single data row for each person. I have a few tables like this and I ultimately would like to join them via CLIENT_ID, but I'm a bit stumped. Is this possible?

Using oracle sql.

where the results look like this

CodePudding user response：

If Pivot ist not available, this should do it:

Select
Client_id,
sum(case when id_description='apple' then 1 else 0 end) as Apples,
sum(case when id_description='orange' then 1 else 0 end) as Oranges...
[]etc.
from
t
group by Client_ID

CodePudding user response：

Might need some minor tweaking as I wrote this just off the top of my head, but something like this should work. Will say this doesn't account for more than 3 rows per CLIENT_ID. For that, would need to do a dynamic pivot (plenty of online articles on this topic).

Pivoting Based on Order of Items

WITH cte_RowNum AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY CLIENT_ID ORDER BY ID) AS RowNum
        ,*
    FROM YourTable
)
SELECT CLIENT_ID
    ,MAX(CASE WHEN RowNum = 1 THEN ID END) AS ID1
    ,MAX(CASE WHEN RowNum = 1 THEN [Description] END) AS ID1_DESCRIPTION
    ,MAX(CASE WHEN RowNum = 2 THEN ID END) AS ID2
    ,MAX(CASE WHEN RowNum = 2 THEN [Description] END) AS ID2_DESCRIPTION
    ,MAX(CASE WHEN RowNum = 3 THEN ID END) AS ID3
    ,MAX(CASE WHEN RowNum = 3 THEN [Description] END) AS ID3_DESCRIPTION
FROM cte_RowNum
GROUP BY CLIENT_ID;