I have below table in SQL database with column 'name' has same value and column 'Item' has different values.

table :

I would like achieve below result as output.

Result:

CodePudding user response：

Answer

Here it is, though it will only work for 3 entries and will also sort them alphabetically reversed:

select a.name ,
a.item as Item1,
b.item as Item2,
c.item as Item3
from TEMP a,
TEMP b, 
TEMP c
where a.name = b.name and b.name = c.name
and a.item > b.item and a.item > c.item
and b.item > c.item 
;

SqlFiddle: http://sqlfiddle.com/#!9/0a098f/5

Data

create table temp(
  name varchar(20),
  item varchar(20)
  );
  
insert into temp values('A','Good');
insert into temp values('A','Better');
insert into temp values('A','Best');

CodePudding user response：

Pivoting can done using an aggregated conditional case expression.

Your data is missing any column to provide explicit ordering to determin which row belongs in which column, which you may wish to address in your actual implementation, the ordering here is arbitrary:

select name,
    Max(case when col=1 then item end) Item1,
    Max(case when col=2 then item end) Item2,
    Max(case when col=3 then item end) Item3
from (
    select name, item, Row_Number() over(partition by name order by name) col
    from t
)t
group by name

CodePudding user response：

You can use something like below when you do not know how many ITEM values would be present for a given NAME, but for that case you'd have to use loop iterator in your code (like iterate from 1 to TOTAL_ITEMS):

CREATE OR REPLACE TABLE TEST (NAME VARCHAR (10),ITEM VARCHAR(10))
AS SELECT * FROM VALUES 
('A', 'GOOD'),
('A', 'BETTER'),
('A', 'BEST'),
('B', 'GOOD'),
('B', 'BETTER'),
('B', 'BEST');

WITH ITEMS_LIST AS ( 
SELECT NAME, LISTAGG (ITEM, ', ') WITHIN GROUP (ORDER BY ITEM DESC) ITEMS,
  COUNT (DISTINCT ITEM) AS TOTAL_ITEMS 
FROM TEST
GROUP BY NAME)
SELECT NAME, 
SPLIT_PART(ITEMS, ',', 1) AS ITEM_1,
SPLIT_PART(ITEMS, ',', 2) AS ITEM_2,
SPLIT_PART(ITEMS, ',', 3) AS ITEM_3,
SPLIT_PART(ITEMS, ',', TOTAL_ITEMS) AS ITEM_N
FROM ITEMS_LIST ORDER BY NAME;