Sql-server – How to get the row as column and one of the column as rows under the first column data

pivotsql serversql-server-2008-r2

I have table like below:

Tname         Scode
-----         -----
SVC           code1
SVC           code2
SVC           code3
AKV           code1
AKV           code12
AKV           code3
AKS           code3
AKS           code2
AKS           code1

I would want a result like below:

SVC      AKV         AKS
----     -----       ----
code1    code1       code3
code2    code12      code2
code3    code3       code1

Best Answer

You can try this. I think it's usefull, and will fix the problem if you have more codes in a column, I'm just trying to not select the row_number column.

SELECT *
FROM 
    (SELECT 
         Tname,scode,
         rn = ROW_NUMBER() OVER (PARTITION BY Tname ORDER BY Scode) 
     FROM teste_1) AS src
PIVOT (
  MIN(scode)
  FOR Tname IN ([SVC],[AKV],[AKS])
) AS pvt


| rn | SVC   | AKV    | AKS   | 
|----|-------|--------|-------|
| 1  | bola  | code1  | code1 |   
| 2  | code1 | code12 | code2 |   
| 3  | code2 | code3  | code3 |   
| 4  | code3 | NULL   | NULL  |

Related Solutions

Sql-server – Select multiple rows from one row based on column values

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
       Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Mysql – How to get multiple row data into a row with multiple columns

You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.

Query:

SELECT User_id
    , MAX(
        CASE WHEN course_id = 1 THEN course_location END
    ) as Course_1
    , MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
    , MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
    , MAX(CASE WHEN course_id = 4 THEN course_location END) as Course_4
FROM data
GROUP BY User_id;

Sample query in SQL Fiddle.

You can replace course_location by CONCAT('YES-', course_location) is the leading YES is indeed needed.

Output:

User_Id | Course_1      | Course_2      | Course_3      | Course_4
1       | location 1    | location 2    | location 1    | (null)
2       | (null)        | location 1    | (null)        | location 4