how to convert table columns into rows in sql 2005

hi i have following table columns and i want to convert these all columns into row

SELECT [CASHINHAND]
      ,[CASHATBANK]
      ,[DEPOSITS]
      ,[ACCRECEVABLE]
      ,[SUNDRYDEBTORS]
      ,[LOANANDADVANCES]
      ,[CURRENTASSETS]
      ,[FIXEDASSETS]
      ,[INVESTMENTS]
      ,[CLOSINGSTOCK]
      ,[ACCPAYABLE]
      ,[SUNDRYCREDITORS]
      ,[BANKLOANS]
      ,[DUTIESANDTAXES]
      ,[PROVISIONS]
      ,[STAFF]
      ,[CURRENTLIABILITIES]
  FROM [BalanceSheet]

required output looks like

CASHINHAND 118950

CASHATBANK  200

DEPOSITS 3000

ACCRECEIVABLE 25000

August 27th, 2015 1:29am

Hi haqayyum,

You haven't confirmed, the Balancesheet table has only one row or multiple rows.

(1) If BALANCESHEET table has always only one row then you may try this:

select 'CASHINHAND' as Title, [CASHINHAND] as Amount from  [BalanceSheet]
UNION ALL
select 'CASHATBANK' as Title, [CASHATBANK] as Amount from  [BalanceSheet]
UNION ALL
select 'DEPOSITS' as Title, [DEPOSITS] as Amount from  [BalanceSheet]
UNION ALL
select 'ACCRECEIVABLE' as Title, [ACCRECEIVABLE] as Amount from  [BalanceSheet]
:
:


(2) If BALANCESHEET table has more than one row then you can try this.  Add suitable order by clause with each select query:

select top 1 'CASHINHAND' as Title, [CASHINHAND] as Amount from  [BalanceSheet]
UNION ALL
select top 1 'CASHATBANK' as Title, [CASHATBANK] as Amount from  [BalanceSheet]
UNION ALL
select top 1 'DEPOSITS' as Title, [DEPOSITS] as Amount from  [BalanceSheet]
UNION ALL
select top 1 'ACCRECEIVABLE' as Title, [ACCRECEIVABLE] as Amount from  [BalanceSheet]
:
:

Thanks.

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 1:47am

Hi Haqayyum,

Please follow the below link which have various ways to achieve your requirement.

http://blog.devart.com/is-unpivot-the-best-way-for-converting-columns-into-rows.html

August 27th, 2015 2:23am

Hi,

I'm not sure if pivot/unpivot is working in sql server 2005, but at least a try using unpivot that converts columns into rows.

select x.Description, Value
from BalanceSheet s
unpivot
(
  Value
  for Description in (CashInhand, CashAtBank, Deposits, AccRecevable)
) x;

Hope it helps.


Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 2:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics