473,325 Members | 2,774 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Converting rows in column header

4
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?
Jan 28 '08 #1
7 3665
Kerre
4
Also i would ike to add that
with following querry,
Expand|Select|Wrap|Line Numbers
  1. SELECT DATENAME(MONTH,sales.SalesDate) AS [Month], YEAR(sales.SalesDate) AS [Year],
  2.         SUM(ISNULL(sales.Units,0)) AS Units 
  3.     FROM Sales 
  4.     WHERE Sales.CompanyID = '1' 
  5.         AND HORevenueTranscation.EntryDate BETWEEN '01/01/2008' AND '12/31/2008'
  6.     GROUP BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
  7.     ORDER BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
  8.  
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
Jan 28 '08 #2
deepuv04
227 Expert 100+
Also i would ike to add that
with following querry,
Expand|Select|Wrap|Line Numbers
  1. SELECT DATENAME(MONTH,sales.SalesDate) AS [Month], YEAR(sales.SalesDate) AS [Year],
  2.         SUM(ISNULL(sales.Units,0)) AS Units 
  3.     FROM Sales 
  4.     WHERE Sales.CompanyID = '1' 
  5.         AND HORevenueTranscation.EntryDate BETWEEN '01/01/2008' AND '12/31/2008'
  6.     GROUP BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
  7.     ORDER BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
  8.  
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
Jan 29 '08 #3
Delerna
1,134 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT SalesDate,
  3.           sum(case when companyID=1 then units else 0 end) as Company1,
  4.           sum(case when companyID=2 then units else 0 end) as Company2,
  5.           sum(case when companyID=3 then units else 0 end) as Company3
  6. FROM Sales
  7. group by SalesDate
  8.  
adjust to suit your needs
Jan 29 '08 #4
Kerre
4
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT SalesDate,
  3.           sum(case when companyID=1 then units else 0 end) as Company1,
  4.           sum(case when companyID=2 then units else 0 end) as Company2,
  5.           sum(case when companyID=3 then units else 0 end) as Company3
  6. FROM Sales
  7. group by SalesDate
  8.  
adjust to suit your needs
Jan 30 '08 #5
Delerna
1,134 Expert 1GB
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE @CompanyID bigint, @Company nvarchar(50),@SQL nvarchar(4000)
  2.  
  3. DECLARE Company_Cursor CURSOR FOR
  4. select CompanyID,CompanyName From Company order by ID
  5. OPEN Company_Cursor
  6. FETCH NEXT FROM Company_Cursor
  7. INTO @CompanyID,@Company
  8.  
  9. set @SQL='SELECT SalesDate,'
  10.  
  11.  
  12. WHILE @@FETCH_STATUS = 0
  13. BEGIN
  14.    set @SQL=@SQL + ',sum(case when companyID=' + @CompanyID + ' then units else 0 end) as ' + @Company
  15.    FETCH NEXT FROM Employee_Cursor
  16.    INTO @CompanyID,@Company
  17. END
  18.  
  19.  
  20. CLOSE Employee_Cursor
  21. DEALLOCATE Company_Cursor
  22.  
  23. set @SQL=@SQL + ' FROM Sales group by SalesDate'
  24.  
  25. EXECUTE sp_executesql @SQL
  26.  
  27.  
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.
Jan 30 '08 #6
ck9663
2,878 Expert 2GB
sounds like a PIVOT table...

try reading this

-- ck
Jan 30 '08 #7
Kerre
4
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE @CompanyID bigint, @Company nvarchar(50),@SQL nvarchar(4000)
  2.  
  3. DECLARE Company_Cursor CURSOR FOR
  4. select CompanyID,CompanyName From Company order by ID
  5. OPEN Company_Cursor
  6. FETCH NEXT FROM Company_Cursor
  7. INTO @CompanyID,@Company
  8.  
  9. set @SQL='SELECT SalesDate,'
  10.  
  11.  
  12. WHILE @@FETCH_STATUS = 0
  13. BEGIN
  14.    set @SQL=@SQL + ',sum(case when companyID=' + @CompanyID + ' then units else 0 end) as ' + @Company
  15.    FETCH NEXT FROM Employee_Cursor
  16.    INTO @CompanyID,@Company
  17. END
  18.  
  19.  
  20. CLOSE Employee_Cursor
  21. DEALLOCATE Company_Cursor
  22.  
  23. set @SQL=@SQL + ' FROM Sales group by SalesDate'
  24.  
  25. EXECUTE sp_executesql @SQL
  26.  
  27.  
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.
Jan 30 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Jay | last post by:
I'm wondering if anyone has even encountered a control or other scripting that will automatically rotate the rows of a datagrid... I am in the process of building an asp.net application that will...
3
by: João Roberto Alonso | last post by:
Hi I wanto to know if there is a way to convert a DBNull.Value to Decimal and returns to me Decimal.Zero? Examples COLUMN1 = NULL from DB Ex1.: cstr((dt.rows(0).item("COLUMN1"))
9
by: Coleen | last post by:
Hi All :-) I found the way to get my column sum (Thanks Cor I did it a little different, but the result is what I wanted) I used: dt_stat_report_3b.Columns.Add(New DataColumn("Sum",...
4
by: skOOb33 | last post by:
I successfully autosized the columns and rows on my Datagrid, and am now facing another issue. Having the sorting ability by clicking the column headers is key, but when I do that, it resizes all...
13
by: ppateel | last post by:
Hi, I am new to c++ and I am converting a c program to c++. I changed malloc call to new and I am getting an exception violation. Here is the relevant piece of code. Compiler vc++ 7.0 (.Net...
4
by: Otie | last post by:
Hello, I am using the MSFlexGrd Control in VB5. I have 1 fixed row and one fixed column. I am trying to do a sort when the user clicks a column in the FIXED ROW. But when I capture the row...
21
by: py_genetic | last post by:
Hello, I'm importing large text files of data using csv. I would like to add some more auto sensing abilities. I'm considing sampling the data file and doing some fuzzy logic scoring on the...
1
by: graphicguru | last post by:
Hi i got some javascript from (*) these site for lock one column with js and header row with css. i am not a programmer i am a designer. i am working in a small company. i don't find such...
7
by: jfkjfk | last post by:
Hello, I am trying to make a page with a design as follows: page is centered horizontaly with a total width of 1000 - a Header on top - a Footer at the bottom - in between the header and the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.