How to pivot multiple column?

Question

How to pivot multiple column?

Naveen J V

SSC-Addicted

Points: 446
More actions
October 24, 2017 at 6:36 am

#325908

Hi Experts,
I have the following table and would like to pivot first 3 columns, order should be based on FormulaOrder column:

Expected output:

Please find below the data:
CREATE TABLE #Results
    (
        FieldName    Nvarchar(50),
        RecordStaus int,
        FieldValue    Nvarchar(50),
        FormulaOrder int
    );
INSERT INTO #Results(FieldName, RecordStaus, FieldValue, FormulaOrder)
VALUES
('Coverage',    1,    40,        1),
('Premium',        1,    50,        2),
('F1',            1,    1000,    3),
('F2',            1,    500,    4),
('F3',            2,    50,        5);
Your help would be much appreciated.
Thanks,
Naveen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

For that, you need to unpivot your values and pivot them again.
I'm not using PIVOT and UNPIVOT operators, although , you could use them. I prefer the alternatives that offer more versatility and sometimes better performance.
SELECT MAX( CASE WHEN FormulaOrder = 1 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 2 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 3 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 4 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 5 THEN Value END) FROM #Results CROSS APPLY(VALUES(1,FieldName), (2,CAST(RecordStaus AS varchar(10))), (3,FieldValue))u(RowOrder,Value) GROUP BY RowOrder ORDER BY RowOrder;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Naveen J V SSC-Addicted Points: 446 More actions · Answer 2

Luis Cazares - Tuesday, October 24, 2017 7:04 AM
For that, you need to unpivot your values and pivot them again.
I'm not using PIVOT and UNPIVOT operators, although , you could use them. I prefer the alternatives that offer more versatility and sometimes better performance.
SELECT MAX( CASE WHEN FormulaOrder = 1 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 2 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 3 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 4 THEN Value END) ,MAX( CASE WHEN FormulaOrder = 5 THEN Value END) FROM #Results CROSS APPLY(VALUES(1,FieldName), (2,CAST(RecordStaus AS varchar(10))), (3,FieldValue))u(RowOrder,Value) GROUP BY RowOrder ORDER BY RowOrder;

Thanks Luis,
This works, but number of rows varies. we cannot hard code the FormulaOrder as in your query.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

For a dynamic approach, check the following article:
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2