How to set multiple columns into rows in sql query

Question

I want to convert the columns into rows using SQL Server. I have already tried unpivot but not enough knowledge to make it only can make the one column

SELECT Unit,Excess_Reactivity ,Refuelling_required,MBP,MCP ,CPPF,Date FROM yourTable // select statement

//insert  to check data
INSERT INTO yourTable
    (Unit,Excess_Reactivity ,Refuelling_required,MBP ,MCP ,CPPF ,Date)
VALUES
    ('TAP-4','52','test1','12','96','35','2019-06-11 00:00:00.000')

INSERT INTO yourTable
    (Unit,Excess_Reactivity ,Refuelling_required,MBP ,MCP ,CPPF ,Date)
VALUES
    ('TAP-2','52','test1','12','96','35','2019-06-11 00:00:00.000')

INSERT INTO yourTable
    (Unit,Excess_Reactivity ,Refuelling_required,MBP ,MCP ,CPPF ,Date)
VALUES
    ('TAP-3','52','test1','12','96','35','2019-06-11 00:00:00.000')

This is table

+--------+--------+---------+-----+-----+------+------------+
|  UNit  | excess | refuell | MBp | MCP | CPPF |    DATE    |
+--------+--------+---------+-----+-----+------+------------+
| TAPS-4 |     52 | test1   |  12 |  96 |   35 | 11/06/2019 |
| TAPS-3 |     52 | test1   |  13 |  96 |   35 | 11/06/2019 |
| TAPS-2 |     52 | test1   |  42 |  96 |   35 | 11/06/2019 |
| TAPS-1 |     52 | test1   |  18 |  96 |   35 | 11/06/2019 |
+--------+--------+---------+-----+-----+------+------------+

I want like this as shown below:

Values   TAPS-4  TAPS-3  TAPS-2 TAPS-1
--------------------------------------
excess    52      52     52      52
MBp       12      13     42      18
MCP       96      96     96      96
CPPF      35      35     35      35

yes nees dynamic which collects the names and set into columns and the multiple columns data would be set into rows — Jay Mewada
– Jay Mewada, Commented Jun 11, 2019 at 17:51
Juan Carlos Oropeza sry adding first times in stackoverflow need these to solve very quickly i was not knowing abt the format you change you can change it again sry — Jay Mewada
– Jay Mewada, Commented Jun 11, 2019 at 17:53

Juan Carlos Oropeza · Accepted Answer · 2019-06-11 18:24:40Z

1

first you need to unpivot your table, take note you need convert your data to the same type because every data will go to the same column.
then you can pivot to your desire result.
if need it convert back to original data type.

SQL FIDDLE

WITH unpvt as (
  SELECT Unit, Attribute, Vals
  FROM ( SELECT Unit, 
                cast(excess as nvarchar(100)) as excess, 
                cast(MBp as nvarchar(100)) as MBp, 
                cast(MCP as nvarchar(100)) as MCP,  
                cast(CPPF as nvarchar(100)) as CPPF
         FROM Table1) as p
  UNPIVOT
       (Vals FOR Attribute in (excess, MBp, MCP, CPPF)
       ) as unpvt
)
SELECT Attribute, [TAPS-4],  [TAPS-3], [TAPS-2], [TAPS-1]
FROM ( SELECT Unit, Attribute, Vals
       FROM unpvt) p 
PIVOT (
         MAX(Vals) FOR  Unit IN 
         ([TAPS-4], [TAPS-3], [TAPS-2], [TAPS-1])
      ) pvt

OUTPUT

| Attribute | TAPS-4 | TAPS-3 | TAPS-2 | TAPS-1 |
|-----------|--------|--------|--------|--------|
|      CPPF |     35 |     35 |     35 |     35 |
|    excess |     52 |     52 |     52 |     52 |
|       MBp |     12 |     13 |     42 |     18 |
|       MCP |     96 |     96 |     96 |     96 |

Note: Try SELECT * FROM unpvt in the sql fiddle to see the intermediate result.

answered Jun 11, 2019 at 18:24

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jay Mewada Over a year ago

Yupz thanks very much the way i want and also, can i bring the taps-4 , taps-3 , taps-2 value dynamically by calling function in it ?

Collectives™ on Stack Overflow

How to set multiple columns into rows in sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related