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 ? https://stackoverflow.com/questions/19055902/unpivot-with-column-name https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 — Sanpas, Apr 25 '19 at 06:51

score 2 · Accepted Answer · answered Apr 25 '19 at 07:10

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

convert rows to column using sql server

1 Answers1