Looping through table to build sql case statement

Hi,
I have the following sample table.
cust_num slsman site sales_ytd
c000200 904 11199 1023.1
c000200 904 11121 2000.12
c000201 927 11199 325.12
c000201 927 11121 245.5

Also I have the following site table prototype


Site
11199
11121


The output of the select statement should be like the following:


cust_num slsman 11199_sales_ytd 11121_sales_ytd
c000200 904 1023.1 2000.12
c000201 927 325.12 245.5 




This has been achieved through hardcoding the following.
The results are exactly as I would like above



select cust_num,slsman, 
SUM(CASE WHEN [site] = '11199' THEN sales_ytd else 0 end) as 
[11199_sales_ytd], 
SUM(CASE WHEN [site] = '11121' THEN sales_ytd else 0 end) as 
[11121_sales_ytd] 
from prototype1 
group by cust_num, slsman 




However I would like to utilize the site table to build the case 
statement.Here I would like to use a loop to utilize the table values in the 
case statement

That way I do not have to hard code the '11199' and [11100_sales_ytd] aliases.
Thus if I have twenty file sites the the case statement should pick the 
sites in the expression instead of hardcoding.


Any idea is appreciated.
0
Utf
6/24/2010 11:01:59 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
1038 Views

Similar Articles

[PageSpeed] 33

Look into creating a PIVOT query in SQL Server...

http://msdn.microsoft.com/en-us/library/ms177410.aspx

-Eric Isaacs
0
Eric
6/24/2010 11:35:50 PM
>> Any idea is appreciated. <<

Use a reporting tools for reports and quit trying to violate teh basis
of 1NF and a tiered architecture with proprietary kludges.

SELECT cust_nbr, slsman_nbr, site_nbr, SUM(sales_ytd) AS sale_ytd_tot
  FROM Prototype1
-- WHERE site_nbr IN ('11199', '11121')
 GROUP BY cust_nbr, slsman_nbr, site_nbr;

Put the filter in the report program script and let it arrange the
spreadsheet style display for you.  Crystal reports, et al are cheap
and you can probably find a free open source tool.

Don't saw wood with a hammer.

0
CELKO
6/25/2010 12:26:27 AM
Jack (Jack@discussions.microsoft.com) writes:
> However I would like to utilize the site table to build the case 
> statement.Here I would like to use a loop to utilize the table values in
> the case statement 
> 
> That way I do not have to hard code the '11199' and [11100_sales_ytd]
> aliases. Thus if I have twenty file sites the the case statement should
> pick the sites in the expression instead of hardcoding. 
 
Have a look at http://www.sommarskog.se/dynamic_sql.html#Crosstab and
particulary the link to sp_pivot.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/25/2010 12:12:21 PM
Reply:

Similar Artilces:

if and and statements
Looking for help with a 2 x 4 matrix with the following conditions A1 B1 C1 Answer 3 0 1 A1*4+2 3 0 2 0 3 1 1 A1*2+1 3 1 2 0 =IF(AND(B1=0,C1=1),+A1*4*2,IF(AND(B1=0,C...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

Case Lookup 04-21-06
Is there a way to prevent resolved cases from appearing in the case lookup? Specifically... Create an email in Outlook. Click the 'Track in CRM' button. Click the 'Regarding' button. Change the 'Look For' to Cases. Currently this lookup displays all cases with no restrictions. I would like to restrict this to only show active cases. ------=_NextPart_0001_2F86B88A Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Cindy, Thanks for the post. I will have to take a look at this to see if there is a way to filter the view. A quick glance at the issu...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

Write conflict error with ODBC link table
Hi, I have migrate my back end access tables to SQL Server. While editing data (ODBC link) from form, I receive Wirte conflict error 'The record has been changed by other user... Copying the change to the clipboard...'. The error allow me either copy the info to clipboard or drop change. In this case, how can I save my work to the table here? SF � "SF" <xyz@online.com.kh> ������ ��� ������ news:#F7F7OtcIHA.5160@TK2MSFTNGP05.phx.gbl... > Hi, > > I have migrate my back end access tables to SQL Server. While editing data > (ODBC link) from form, I...

NESTED IF STATEMENT
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C85845.4A66D4E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have to calculate commission on sales tax collected based on the = following criteria: 1) If sales tax collected is $11.00 or less, then the commission =3D to = the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission=3D = $11.00 3) If sales tax collected is more than $333.33, then commission =3D 3.3% = of the sales tax collected, with a maximum amount =3D $99...

Detecting Case of Text in a Cell
Hi All I know it's possible to change the case of text in a cell in Excel using Lower, Upper and Proper, but is there a function that tells me the current case of the text? A user has a spreadsheet of catalogue items, some in proper case and some in upper. He wants to seperate those in upper case into a separate sheet but I can't think of an easy way of doing it. Can anyone help? Colin -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10472 View this...

IF Statement 01-17-08
Hi, I am trying to run a query that has 4 fields: location, type, year, and cost. However I need to increase the cost for the year 2007 for 2 types. The IF statement I entered into the cost field is: TotalCost:IIF([Type]="C" Or "H" And [Year]=2007,[SumCost]/.9,[SumCost]) The issue I am having is that it is increasing the costs for all types. Is there a way to fix this or do i need a new query for C and H and then combine the 2 queries? Thanks for any help you can provide. Actually, you don't need to do it that way. Create a Where clause for the query tha...

Help with SQL- SupplierList cost into Item cost field
Hi, re-posted! See below. Hi, thanks for that- it worked perfectly. There is just one snag however and I didn't think of it until I was testing. Many of the suppliers use specific currencies so the information I need copied from the Supplier tab is actually the Local Cost. When I view the SupplierList table, Local Cost doesn't appear as a column. Is it just a calculation based on exchange rates for information or does it actually exist somewhere within the database? Your help once again would be much appreciated. T. "convoluted" wrote: > Hi Tara - backup your...

Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table contains two column with X and Y coordinates, all values are numerical. The coordinate system is irrelevant. I need to "tag" all XY coordinates in Table B that have a matching XY coordinate in Table A. If Table A coordinates are in columns A and B, and Table B coordinates are in columns C and D, then I want to place the text "hit" in column E next to each coordinate pair from columns C and D that match a coordinate pair in columns A and B. The ranges for the respective tables have been named ACOORD and...

database question / feedback needed (GP8
Hello Everybody. We sold one of our companies, and the new owners are moving off Great Plains. They are transferring/rebuilding data in their new software on their own. For historical (GP) data, they have purchased some software to perform queries, and have requested I give them the data. I am planning to send a backup of the SQL Company dbase. They would not have a GP client, and would not be performing transactions - so I believe they really just want to access the various history tables. I prefer not to send the Dynamics dbase if I don't have to (passwords, etc). I guess my ques...

"How do I get rid of old records in an Pivot Table?
The spreadsheet gets copied each month to a new file and the data cleared out, BUT the selecetion in the Pivot Table still holds the data from Previous spreadsheets Debra Dalgleish has some techniques at: http://www.contextures.com/xlPivot04.html AyPee wrote: > > The spreadsheet gets copied each month to a new file and the data cleared > out, BUT the selecetion in the Pivot Table still holds the data from Previous > spreadsheets -- Dave Peterson The web-link was very helpful, thank you. I was about to reprogram and build my pivot tables to get rid of the obsolete item...

Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax. What i need to do is add the Tender Type (Credit card / cash / check) as another column in the report. So far i have found out: i need to add a column i need to import the TenderEntry table for the data I need to find out: How to import another table I have tried to import the table using sql UNION function, but that wont work for me either. any help would be greatly appriciated. Thank You Hi ED, The things make sense to me regarding the adding tables and fields which you can do and customized the .grp file. ...

Money looped
Online services wizard caused error "Money has encountered a problem and needs to close. Send report to Microsoft! And restarts and closes. And restarts and closes. .net passport not working correctly. ..net Unable to verify email. Email works, please respond to email. ...

Pivot table help I think !
Hi All, I have a report I need to create which goes as follows. I have a data list of around 56k records. The rows contain this; category 1, category 2, category 3, call id and Date I've created a pivot table with Cat1, cat2, cat 3 on the row area, Count of Call ID in the data area and date in the column area (this is grouped by Month). I'm looking at the months of Mar, Apr and May. I want to sort it, in descending order, by the difference of 'Count of Call ID' there is between Mar and May. If I cannot perform the calculation in the pivot table, is there a way of ungroupi...

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Pivot Table toolbar
Every time I edit a pivot table (Excel 2000)the toolbar launches itself and I have to then re-anchor it manually - this is very irritating! Is there any way to stop this happening? "GeoffS" <crammond-smith@tiscali.co.uk> wrote in message news:39d201c355a8$a7f788b0$a001280a@phx.gbl... > Every time I edit a pivot table (Excel 2000)the toolbar > launches itself and I have to then re-anchor it manually - > this is very irritating! > > Is there any way to stop this happening? I use Excel97 but it may be the same. You can have the toolbar on all the time, positio...

move SQL to dedicated 64 bit server
Hi, I have a 32 bit Windows 2003 server with GP 9 and SQL 2005 installed. I would like to move the SQL databses to a dedicated Windows 2008 64 bit SQL 2005 server. I would like to leave GP where it is, but move the SQL database to the new server for performance and backup purposes. is there a KB on how to do this or can anyone help point me in the right direction? thanks Simon, You can find a link to the KB article on how to perform this operation on my blog at http://dynamicsgpblogster.blogspot.com/2008/06/how-to-transfer-microsoft-dynamics-gp.html You may also want to look into the...

table of contents
I am trying to build a table of contents from the headings in my employee handbook. I have heard that this is possible, and if I change the content of the handbook, the page numbers, etc. will automatically update. What is the process to accomplish this? I would appreciate any insight given. Thanks! ...

Pivot tabels -- Incorrect sorting of specific value in pivot table
A message was posted yesterday that has not shown up so this is a second attmpt. I have a pivot table that consistently places a value at the top of the sorted llist (bottom if decending) even though that value should be in the middle of the list. A sample of the values are ALC, SNO, CET, ESU, TEL, STR, JUN, NKA, NTL, blank. The JUN value is always at the top. Any value placed in the field that begins with J shows at the top. If the value is changed to any other letter, it sorts correctly. The data has been reentered at the source, the query checked, and the downloaded data chec...

pivot table in reverse
I' ve got a spreadsheet with events as row headings, people's names as column headings and dates in the cells. E.g. Person 1 Person 2 Birthday 01/07/61 05/10/67 Anniversary 10/08/90 10/11/92 (Actually the list is much longer....) Now I want to make a new list list converting the above data to [dates (in column 1; event in column 2; person in column 3]. Sort of pivot tables in reverse......!! How to? Wim Hi, I may not be reading your post correctly but I think searching fo "Transpose" in the He...

Using MicroFramework to build my own GPS device?
But what I really need to learn at the moment is how the devices like TomTom and Garmen function? I don't have one obviously so is there a hardware forum for Microsoft devs? Do they run using batteries? Which protocol is used if they can communicate with the vehicle when these things appear to be dash mounted with a suction cup? "Hillbilly" <nobody@nowhere.com> wrote in news:ujibLyEbKHA.5348@TK2MSFTNGP06.phx.gbl: > But what I really need to learn at the moment is how the devices like > TomTom and Garmen function? I don't have one obviously so ...