April 7, 2009 at 11:51 am
Hello, is it possible to change my column based query into row based? I have daily and monthly sales totals and i use union to connect the two. Unfortunately, the output(which is correct) is one big column. How can I achieve the same results in rows.
For example, below is my column based solution
Fund, Title, [Sales Amount], isOpen
-------------------------------
Fund1, Daily Sales:,50000.00, 0
Fund2, Daily Sales:,3500.00,0
Fund1, Monthly Sales:,3500000.00, 0
Fund2, Monthly Sales:,170000.00,0
The desired row based output
Fund, [Daily Sales], [Month To Date], isOpen
-------------------------------
Fund1, 50000.00, 3500000.00, 0
Fund2, 3500.00,2050000.00, 0
The SQL code is:
--Daily Sales
(
SELECT
ISNULL(t.fund,f.description) AS fund,
'Daily Sales:' as Title,
ISNULL(sum(t.ticketAmt), 0.00) as [Sales Amount],
f.isOpen
FROM
tblFunds f LEFT OUTER JOIN
tbltransactions t
ON f.description = t.fund AND
(CONVERT(char, t.createDate, 101) = CONVERT(char, GETDATE(), 101)) and t.status <> 'deleted'
where
(f.isopen = 0)
group by
ISNULL(t.fund,f.description) ,
f.isopen
)
union all(
--monthly sales
SELECT t.fund, 'Monthly:' as Title,sum(t.ticketAmt) as [Sales Amount], f.isOpen
FROM tblfunds f INNER JOIN
tblTransActions t ON f.description = t.fund
where DATEPART(yy, t.createDate) = datepart(yy,getdate()) and
(DATEPART(m, t.createDate) = datepart(m,getdate())) and (f.isopen = 0)
and t.status <> 'deleted'
group by t.fund, f.isopen)
April 7, 2009 at 11:52 am
I think this is a double-post. Please don't do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 11:54 am
No cross posts...
http://www.sqlservercentral.com/Forums/Topic692311-149-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply