Convert Columns into Multiple Rows

Question

I am trying to get data from multiple columns into multiple rows.

Sample data:

DName   FP1   FP1% FP2  FP2%  FP3  FP3% DP1  DP1% DP2 DP2% Value
Deliv_1 Dig   50   Port 25   Risk 25   Core 50   Imp 50    10000
Deliv_2 Imp   50   Port 25   Cor  25   Risk 50   Dig 50    15000

Output required:

DName   DPName FPName Value
Deliv_1 DP1    FP1    Value*DP1*FP1
Deliv_1 DP1    FP2    Value*DP1*FP2
Deliv_1 DP1    FP3    Value*DP1*FP3
Deliv_1 DP2    FP1    Value*DP2*FP1
Deliv_1 DP2    FP2    Value*DP2*FP2
Deliv_1 DP2    FP3    Value*DP2*FP3  
Deliv_2 DP1    FP1    Value*DP1*FP1
Deliv_2 DP1    FP2    Value*DP1*FP2
Deliv_2 DP1    FP3    Value*DP1*FP3
Deliv_2 DP2    FP1    Value*DP2*FP1
Deliv_2 DP2    FP2    Value*DP2*FP2
Deliv_2 DP2    FP3    Value*DP2*FP3

Cross apply works but it does repeat each row for every Deliv_1 so trying to see if there is a better way to display it

So what code have you tried? Why do you think that code doesn't work to get the expected results? — AlwaysLearning, Oct 12 '22 at 03:12
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Got stuff, want answer, don't know data types isn't much of a question. — HABO, Oct 12 '22 at 03:12
Never did figure out why `Deliv_2` isn't in "Output required". — HABO, Oct 12 '22 at 12:25

score 2 · Accepted Answer · answered Oct 12 '22 at 03:30

Not 100% clear, but here is a guess using a CROSS APPLY

Example

Declare @YourTable Table ([DName] varchar(50),[FP1] varchar(50),[FP1%] decimal(10,2),[FP2] varchar(50),[FP2%] decimal(10,2),[FP3] varchar(50),[FP3%] decimal(10,2),[DP1] varchar(50),[DP1%] decimal(10,2),[DP2] varchar(50),[DP2%] decimal(10,2),[Value] int)  
Insert Into @YourTable Values 
 ('Deliv_1','Dig',50,'Port',25,'Risk',25,'Core',50,'Imp',50,10000)
,('Deliv_2','Imp',50,'Port',25,'Cor',25,'Risk',50,'Dig',50,15000)


Select A.DName
      ,B.*
 From @YourTable A
 Cross Apply ( Values  (DP1,FP1,Value*([DP1%]/100.0)*([FP1%]/100.0))
                      ,(DP1,FP2,Value*([DP1%]/100.0)*([FP2%]/100.0))
                      ,(DP1,FP3,Value*([DP1%]/100.0)*([FP3%]/100.0))
                      ,(DP2,FP1,Value*([DP2%]/100.0)*([FP1%]/100.0))
                      ,(DP2,FP2,Value*([DP2%]/100.0)*([FP2%]/100.0))
                      ,(DP2,FP3,Value*([DP2%]/100.0)*([FP3%]/100.0))
             ) B(DPName,FPName,Value)

Results

Convert Columns into Multiple Rows

1 Answers1