convert rows to column using sql server

Question

I have fix column and rows in my result. I want to convert the rows into columns.

I have checked but in pivoting we can transpose only single column into rows.

SELECT   MoveType,
         SUM(IsHazardous) AS IsHazardous,
         SUM(IsReefer) AS IsReefer,
         SUM (IsOOG) AS IsOOG
FROM     (SELECT qce.MoveType,
                 (CASE WHEN qce.IsHazardous='YES' THEN 1 ELSE 0 END) AS IsHazardous,
                 (CASE WHEN qce.IsReefer='YES' THEN 1 ELSE 0 END) AS IsReefer,
                 (CASE WHEN qce.IsOOG='YES' THEN 1 ELSE 0 END) AS IsOOG
          FROM   qbtCallEquipmentExecution AS qcee
            INNER JOIN qbtCallEquipment qce ON
                 qce.ID=qcee.CallEquipmentID
            AND  qce.IsActive=1
          WHERE  qce.CallID=1169
          AND qcee.IsActive=1) AS A
GROUP BY MoveType

how to pivot this query .

Actual output:

MoveType                                        IsHazardous IsReefer    IsOOG
-------------------------------------------------- ----------- ----------- ---
DSCR                                               0           0           0
LOAD                                               0           0           0
SHFT                                               0           0           0

Expected Output:

MoveType                                         DSCR       LOAD         SHFT
-------------------------------------------------- ----------- ----------- ---
IsHazardous                                        0           0           0
IsReefer                                           0           0           0
IsOOG                                              0           0           0

Hi have you take look at UNPIVOT functionnality ? stackoverflow.com/questions/19055902/unpivot-with-column-name learn.microsoft.com/en-us/sql/t-sql/queries/… — Sanpas
– Sanpas, Commented Apr 25, 2019 at 6:51

DhruvJoshi · Accepted Answer · 2019-04-25 07:10:44Z

If you have a known list of MoveType data then you can use the below query. If not, this will be a dynamic pivot/unpivot

demo link

;with t as 
(
    SELECT   MoveType,
         SUM(IsHazardous) AS IsHazardous,
         SUM(IsReefer) AS IsReefer,
         SUM (IsOOG) AS IsOOG
FROM     (SELECT qce.MoveType,
                 (CASE WHEN qce.IsHazardous='YES' THEN 1 ELSE 0 END) AS IsHazardous,
                 (CASE WHEN qce.IsReefer='YES' THEN 1 ELSE 0 END) AS IsReefer,
                 (CASE WHEN qce.IsOOG='YES' THEN 1 ELSE 0 END) AS IsOOG
          FROM   qbtCallEquipmentExecution AS qcee
            INNER JOIN qbtCallEquipment qce ON
                 qce.ID=qcee.CallEquipmentID
            AND  qce.IsActive=1
          WHERE  qce.CallID=1169
          AND qcee.IsActive=1) AS A
GROUP BY MoveType
)

select v as MoveType,DSCR,LOAD,SHFT 
from 
    (
        select * from t

    ) s
unpivot
    (
        data for v in (IsHazardous,IsReefer, IsOOG)
    )u
pivot 
    (
        sum(data) for MoveType in (DSCR,LOAD,SHFT)
    )p

Collectives™ on Stack Overflow

convert rows to column using sql server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related