Data reorganization - SQL

Question

I have a question for reorganizing the data in SQL in the manner indicated below.

I have a table with the given data structure.

Username	Type	Data
test	1	Data1
test	1	Data2
test	1	Data3
test	2	Data1
test	2	Data2
test	2	Data3

Using query how can i get the view as given below? How can I make more columns from the last column, for those rows where the data in the first and second columns are the same?

Username	Type	Data1	Data2	Data3
test	1	Data1	Data2	Data3
test	2	Data1	Data2	Data3

Answer 1

Assuming you know in advance you just need three values for each of your output records, you can use the ROW_NUMBER window function to provide a ranking for each of your fields, then you can use conditional aggregation, using CASE expressions, to set each of your Data information in the specific field. Eventually aggregate per <Username, Type> partition.

WITH cte AS (
    SELECT tab.*, 
           ROW_NUMBER() OVER(PARTITION BY UserName, Type ORDER BY Data) AS rn
    FROM tab
)
SELECT Username, Type,
       MAX(CASE WHEN rn = 1 THEN Data END) AS Data1,
       MAX(CASE WHEN rn = 2 THEN Data END) AS Data2,
       MAX(CASE WHEN rn = 3 THEN Data END) AS Data3
FROM cte 
GROUP BY Username, Type

This is likely to work on all most common up-to-date DBMS'.