none
Transposing Data or using Coelsce

    Question

  • Hi ,

    We have a requirement to write data to flat files.the challenge we are facing here is to transpose rows to columns when data not available .below is the scenario.

    we have the below table strucutre and while writing to the flat file we need to creted 12 columns for each emp if irrespective of if he is got data or not(i.e only jan and mar is acilable in table but we need to show all months with blank data )

    EMPID EMPNAme SAL
    100 abc JAN
    100 abc Mar

    Below is the result required in Flat fie.Mainframe system is expecting result in this format .could you please suggest the best method to achieve this or any articles on this

    EMPID EMPname Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 Col11 Col12
    100 abc JAN FEB MAR APR MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER

    Thanks

    Monday, November 04, 2013 5:10 AM

Answers

  • Are you looking for the below:

    create Table Test_Nov4th(EmpNo int, EmpName Varchar(10),SAL Varchar(20))
    Insert into Test_Nov4th Select 100,'abc','Jan'
    Insert into Test_Nov4th Select 101,'abc','Mar'
    
    			
    ;With cte
    As
    (Select EmpNo,EmpName,Val From 
    (Select EmpNo, EmpName,B.Val
    From Test_Nov4th A
    Cross Apply(Values('Jan'),('Feb'),('Mar'),('Apr'),('May'),('Jun'),
    					('Jul'),('Aug'),('Sep'),('Oct'),('Nov'),('Dec') ) B(Val)) T1
    ) Select EmpNo,EmpName,[Jan] Col1,[Feb] Col1,[Mar] Col3,[Apr] Col4,[May] Col5,
    	[Jun] Col6, [Jul] Col7,[Aug] Col8,[Sep] Col9,[Oct] Col10,[Nov] Col11,[Dec] Col12 From cte
    pivot (MAX (Val) for Val in ([Jan],[Feb],[Mar],[Apr],[May],[Jun]
    							,[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) as AvgIncomePerDay
    
    Drop Table Test_Nov4th
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, November 04, 2013 5:32 AM