I am new to writing sql queries. here is my present query
select distinct DAY(InvDate) as Date,Location,sum(Inv+St) as Total From InvInfo where (InvDate >= '8/1/2012') and (InvDate <= '8/2/2012') Group by DAY(InvDate) ,Location
Here i am getting reults like this
Date Location Total 1 Dallas 100 1 Atlanta 50 1 Houston 80 1 Raleigh 75 1 Chicago 120 2 Dallas 80 2 Atlanta 60 2 Houston 77 2 Raleigh 82 2 Chicago 110
But i need to get data like this
Date Dallas Atlanta Houston Raleigh Chicago 1 100 50 80 75 120 2 80 60 77 82 110
Can you guys please help on this.. i am very new to sql coding.
SELECT *
FROM
(
SELECT DAY(InvDate), Location, Inv+St as Total
FROM InvInfo
) d
PIVOT
(
SUM (Total)
FOR Location in ( [Dallas', [Atlanta], [Houston], [Raleigh], [Chicago] )
) p
SELECT * FROM ( SELECT InvDate, Location, Inv+obs as Total FROM InvInfo where (InvDate >= '8/1/2012') and (InvDate <= '8/31/2012') Group by InvDate ,Location ) d PIVOT ( SUM (Total) FOR Location in ( [Dallas', [Atlanta], [Houston], [Raleigh], [Chicago] ) ) p
By using above query getting this results
Date Dallas Atlanta Houston Raleigh Chicago 1 100 50 80 75 120 2 80 60 77 82 110
But i want one more extra column(OBS) in my Actual results as shown below
Date Dallas Atlanta Houston Raleigh Chicago OBS 1 100 50 80 75 120 20 2 80 60 77 82 110 18
This extra columns will get by below query
select sum(OBS) as OBS from InvInfo where (InvDate >= '8/1/2012') and (InvDate <= '8/31/2012') Group by InvDate
So how to combine these two queries to get Actual results as shown above.
Help on Sql Query
Posted - 09/24/2012 : 10:13:21
