PIVOT Multiple Rows to Different Column with Additional Column-CodePudding

I need your suggestion on this.

I have a before table like below, which I would like to transform to the after table,

(note: the table below is just an example, there is over 1000 rows in the real table)

Before:

line_type	line_name	op_id	org_code
Internal	Storage 1	1	ABC
Makloon	Storage 2	1	DEF
Process	Storage 2	1	XYZ
Internal	Storage 3	2	XYZ
Makloon	Storage 1	2	ABC
Process	Storage 2	2	XYZ

After:

op_id	org_code internal	Internal	org_code Makloon	Makloon	org_code Process	Process
1	ABC	Storage 1	DEF	Storage 2	XYZ	Storage 2
2	XYZ	Storage 3	ABC	Storage 1	XYZ	Storage 2

Can I use PIVOT for this case? Or do I need to use another way?

I have only succeeded in using PIVOT for the line_name, I'm not sure how to PIVOT both of them (line_name and org_code)

Here is what I have tried:

SELECT 
    [op_id], 
    [Internal],
    [Makloon],
    [Process]
FROM
    (SELECT
         [line_type],
         [line_name],
         [op_id]
     FROM 
         [database_name]
    ) pvt
PIVOT 
    (MAX(line_name)
        FOR [line_type] IN ([Internal], [Makloon], [Process])
    ) AS pvt_table
ORDER BY 
    [op_id];

The result of this query:

op_id	Internal	Makloon	Process
1	Storage 1	Storage 2	Storage 2
2	Storage 3	Storage 1	Storage 2

CodePudding user response：

Pivot is just a fancy CASE WHEN expression, and it's often easier to spell it out manually:

SELECT op_id
,   MAX(case when line_type = 'Internal' THEN org_code END) AS org_code_internal
,   MAX(case when line_type = 'Internal' THEN line_name END) AS internal
,   MAX(case when line_type = 'Makloon' THEN org_code END) AS org_code_Makloon
,   MAX(case when line_type = 'Makloon' THEN line_name END) AS internal_Makloon
,   MAX(case when line_type = 'Process' THEN org_code END) AS org_code_Process
,   MAX(case when line_type = 'Process' THEN line_name END) AS Process

FROM (
    VALUES  (N'Internal', N'Storage 1', 1, N'ABC')
    ,   (N'Makloon', N'Storage 2', 1, N'DEF')
    ,   (N'Process', N'Storage 2', 1, N'XYZ')
    ,   (N'Internal', N'Storage 3', 2, N'XYZ')
    ,   (N'Makloon', N'Storage 1', 2, N'ABC')
    ,   (N'Process', N'Storage 2', 2, N'XYZ')
) t (line_type,line_name,op_id,org_code)
group by op_id

By this method, you can flip almost any kind of rows