将重复值列为SQL /数据透视表中的列

I would like to list the duplicate values as column in SQL or Pivot Table, is it possible ?

我想将重复值列为SQL或数据透视表中的列,是否可能?

Output:

    1   2   3
A   A1  A2  A3
B   B1  B2  
C   C1  C2  

Raw Data:

A   A1  1
A   A2  2
A   A3  3
B   B1  1
B   B2  2
C   C1  1
C   C2  2

1 个解决方案

#1

Using PIVOT:

CREATE TABLE #tab(col1 VARCHAR(10), col2 VARCHAR(10), col3 INT);

INSERT INTO #tab(col1, col2, col3) VALUES
 ('A',   'A1',  1),('A',   'A2',  2),('A',   'A3',  3),('B',   'B1',  1)
,('B',   'B2',  2),('C',   'C1',  1),('C',   'C2',  2);

SELECT col1,[1],[2], [3]
FROM #tab
PIVOT (
  MAX(col2)
  FOR col3 IN ([1],[2], [3])
) AS p

LiveDemo