Creating a SQL View Vertically 1

[kpetree10]

Hi All, I'd like to create a SQL view for some dashboard software that ties into our ERP system. I have five queries to run but instead of the results being formatted horizontally with a column heading I'd like them to be displayed vertically like this...

Account | Amount
=============
Sales | $123,456
------------------
Tooling | $12,345
------------------
Scrap | $12,345

and here is some of the queries I'm using in case it helps for demonstration...

select SUM(tot_sls_amt) from oehdrhst_sql where MONTH(GETDATE())=MONTH(inv_dt) and YEAR(GETDATE())=YEAR(inv_dt)

select SUM(-bdr_hfl) from gbkmut
where reknr in(' 4110') and MONTH(GETDATE())=MONTH(datum) and YEAR(GETDATE())=YEAR(datum) and bkstnr is not null

select SUM(-bdr_hfl) from gbkmut
where reknr in(' 4120') and MONTH(GETDATE())=MONTH(datum) and YEAR(GETDATE())=YEAR(datum) and bkstnr is not null

The value in the account field would be manually defined in the query. Is this possible?

 

[markros]

You will need to UNION ALL between your queries.

Another thing - the way you're constructing your WHERE clause is very inefficient. You better get the beginning of the month as

dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')

and start of the next month as


dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000201')

and use Inv_Dt >= @StartOfMonth and Inv_Dt < @StartOfNextMonth


PluralSight Learning Library