0

I'm stuck with a SQL query (SQL Server) that involves converting horizontal rows to vertical rows Below is my Query that I am trying

SELECT P AS Amount_Rs 
    FROM (
Select (F1.D32-F1.D20) As Profit_For_The_Period ,F3.D2 as Current_Libilities,F5.D20 As Capital_Acount,
 --M1.Name As Name,
F2.D20 AS Loan_Liabilities,F4.d1 As Opening_Diff --F2.D68 As Loan,
 from Folio1 As F1
 --inner Join Master1 As m1 on m1.Code like '101' or m1.Code Like '102' or m1.Code Like '106' or  m1.Code Like '109' or m1.Code lIke '103'
  --And m1.Code=102  And m1.Code=101)
inner Join Folio1 As F2 On (F2.MasterCode=F2.MasterCode)
inner Join Folio1 As F3 On (F3.MasterCode=F3.MasterCode)
inner Join Folio1 As F4 On (F4.MasterCode=F4.MasterCode)
inner Join Folio1 As F5 On (F5.MasterCode=F5.MasterCode)
Where F1.MasterCode=109  
and F2.MasterCode =106
and F3.MasterCode=103 
and F4.MasterCode=102
And F5.MasterCode=101
) p UNPIVOT 
( p FOR value IN 
( Profit_For_The_Period,Capital_Acount, Current_Libilities, Loan_Liabilities, Opening_Diff ) 

) AS unvpt

Current Output:

1   12392
2     0
3     0
4   4000
5   -200

Desired Output:

1   Capital Account     12392           
2   Current Assets        0
3   Current Liabilities   0
4   Loans (Liability)    4000
5   Revenue Accounts    -200

Thanks !!!

Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
Atul Kushwaha
  • 3
  • 7
  • I'm a little confused, you have `WHERE` requisite like `F1.MasterCode=109`, however, I see no `109` value in your data. Can you provide us with the DDL and DML for your data please? – Thom A Jan 28 '19 at 12:58
  • There is no way that is your query. The query you posted here would never return any rows because you have all those tables inner joined on MasterCode and then you are looking for a different value of each one. A single value can't be all those values at the same time. – Sean Lange Jan 28 '19 at 14:18
  • Updated Query Plz Check It ! Thanks – Atul Kushwaha Jan 29 '19 at 04:57
  • @AtulKushwaha Please provide a [mcve](https://meta.stackoverflow.com/questions/366988/what-does-mcve-mean) also have a look at [here](https://stackoverflow.com/questions/53372997/pivot-query-on-distinct-records) for more detailed info – Prashant Pimpale Jan 29 '19 at 05:06

1 Answers1

0

I think you are looking for a pivot. Use the CASE statement with a SUM or any aggregate function in the SELECT part and a group by in the where clause, that's how I use to put rows into columns in a query when I have to in MySQL. I don't know SQL Server but I think you can do quite the same.

your conditions below 

F1.MasterCode=109  
and F2.MasterCode =106
and F3.MasterCode=103 
and F4.MasterCode=102
And F5.MasterCode=101

shouldn't be in the the where clause but with the case in the select part

example : 

select whatever, 
case when F2.MasterCode =106 then sum(column_name)
end case as column_alias, (other columns) from ...

hope this could help

Philippe Merle
  • 115
  • 2
  • 4
  • 13