Converting rows in column header

[Kerre]

Hello,
I have two tables.
Table 1 name is Company
Table 2 is Sales

Data in 'Company' table is,
CompanyID CompanyName
--------------------------------------------
1 Company1
2 Company2
3 Company3

Data in 'Sales' table is

ID CompanyID SalesDate Units
---------------------------------------------------------------
1 1 01/23/2008 40
2 1 01/24/2008 20
2 2 01/23/2008 30
2 2 01/24/2008 10
2 2 01/23/2008 100
2 3 01/24/2008 75
2 3 01/25/2008 125

I want result set as follwoing

Unit Sales Company1 Company2 Company3
-----------------------------------------------------------------------------
Jan 08 60 40 300
Feb 08 0 0 0
Mar 08 0 0 0

Does anyone has idea how to achieve it?

[Kerre]

Also i would ike to add that
with following querry,
[code=sql]
SELECT DATENAME(MONTH, sales.SalesDate ) AS [Month], YEAR(sales.Sale sDate) AS [Year],
SUM(ISNULL(sale s.Units,0)) AS Units
FROM Sales
WHERE Sales.CompanyID = '1'
AND HORevenueTransc ation.EntryDate BETWEEN '01/01/2008' AND '12/31/2008'
GROUP BY YEAR(sales.Sale sDate), DATENAME(MONTH, sales.SalesDate )
ORDER BY YEAR(sales.Sale sDate), DATENAME(MONTH, sales.SalesDate )
[/code]
i get result set as

Month Year Units
-----------------------------------------
January 2007 60


But i have no idea how to get result set as specified previously.
ie,

Unit Sales Company1 Company2 Company3
-----------------------------------------------------------------------------
Jan 08 60 40 300
Feb 08 0 0 0
Mar 08 0 0 0

[deepuv04]

Also i would ike to add that
with following querry,
[code=sql]
SELECT DATENAME(MONTH, sales.SalesDate ) AS [Month], YEAR(sales.Sale sDate) AS [Year],
SUM(ISNULL(sale s.Units,0)) AS Units
FROM Sales
WHERE Sales.CompanyID = '1'
AND HORevenueTransc ation.EntryDate BETWEEN '01/01/2008' AND '12/31/2008'
GROUP BY YEAR(sales.Sale sDate), DATENAME(MONTH, sales.SalesDate )
ORDER BY YEAR(sales.Sale sDate), DATENAME(MONTH, sales.SalesDate )
[/code]
i get result set as

Month Year Units
-----------------------------------------
January 2007 60


But i have no idea how to get result set as specified previously.
ie,

Unit Sales Company1 Company2 Company3
-----------------------------------------------------------------------------
Jan 08 60 40 300
Feb 08 0 0 0
Mar 08 0 0 0

i think this is OLAP Concept... search for olap queries

[Delerna]

SELECT SalesDate,
          sum(case when companyID=1 then units else 0 end) as Company1,
          sum(case when companyID=2 then units else 0 end) as Company2,
          sum(case when companyID=3 then units else 0 end) as Company3
FROM Sales
group by SalesDate

adjust to suit your needs

[Kerre]

Delerna,
You suggested a good option, but companies are not going to be 3 only.
Think about the case when someone add a new company 'Company4' and 'Company5' in "Company' table.
I think then your querry will show result only for company 1,2 and 3 but not 4 and 5.
I am looking for something else here.

SELECT SalesDate,
          sum(case when companyID=1 then units else 0 end) as Company1,
          sum(case when companyID=2 then units else 0 end) as Company2,
          sum(case when companyID=3 then units else 0 end) as Company3
FROM Sales
group by SalesDate

adjust to suit your needs

[Delerna]

If you need to cater for a variable number of companies the you could use a cursors to build a dynamic query string
Here is an example using the query i presented before

DECLARE @CompanyID bigint, @Company nvarchar(50),@SQL nvarchar(4000)

DECLARE Company_Cursor CURSOR FOR
select CompanyID,CompanyName From Company order by ID
OPEN Company_Cursor
FETCH NEXT FROM Company_Cursor
INTO @CompanyID,@Company

set @SQL='SELECT SalesDate,'


WHILE @@FETCH_STATUS = 0
BEGIN
   set @SQL=@SQL + ',sum(case when companyID=' + @CompanyID + ' then units else 0 end) as ' + @Company
   FETCH NEXT FROM Employee_Cursor
   INTO @CompanyID,@Company
END


CLOSE Employee_Cursor
DEALLOCATE Company_Cursor

set @SQL=@SQL + ' FROM Sales group by SalesDate'

EXECUTE sp_executesql @SQL

this way the query can handle any number of companies and the number of companies in the table can be changed without having to change the query.

PS I havn't tested the code so you may have to erradicate some bugs before it will work. I did try as carefully as I could to get it right.

[ck9663]

sounds like a PIVOT table...

try reading this

-- ck

[Kerre]

Hey that's what exactly i was looking for.
Thanks a lot .......

If you need to cater for a variable number of companies the you could use a cursors to build a dynamic query string
Here is an example using the query i presented before

DECLARE @CompanyID bigint, @Company nvarchar(50),@SQL nvarchar(4000)

DECLARE Company_Cursor CURSOR FOR
select CompanyID,CompanyName From Company order by ID
OPEN Company_Cursor
FETCH NEXT FROM Company_Cursor
INTO @CompanyID,@Company

set @SQL='SELECT SalesDate,'


WHILE @@FETCH_STATUS = 0
BEGIN
   set @SQL=@SQL + ',sum(case when companyID=' + @CompanyID + ' then units else 0 end) as ' + @Company
   FETCH NEXT FROM Employee_Cursor
   INTO @CompanyID,@Company
END


CLOSE Employee_Cursor
DEALLOCATE Company_Cursor

set @SQL=@SQL + ' FROM Sales group by SalesDate'

EXECUTE sp_executesql @SQL

this way the query can handle any number of companies and the number of companies in the table can be changed without having to change the query.

PS I havn't tested the code so you may have to erradicate some bugs before it will work. I did try as carefully as I could to get it right.