User:Alokmt/sandbox

Stack Exchange ▼Vivek Singh 83 | chat | meta | about | help

Stack Overflow

Questions Tags Users Badges Unanswered

Ask Question

SQL pivot nested up vote 1 down vote favorite 1

I am using Sql Server 2008 R2. My work is regarding Business Intelligence and Reporting. Now I have a table as below.

declare @t table(  prod_type NVARCHAR(20),   WEEK   NVARCHAR(20),   COD_ACCT NVARCHAR(20),   BBH NVARCHAR(20)   );

INSERT INTO @t VALUES ('Salary',  '41306',    '12313',    'Vikas'), ('Salary', '41306',   '311',  'Ramesh'), ('Salary', '41306',   '55',   'Vicky'), ('Salary', '41306',   '44',   'Vicky'), ('Salary', '41313',   '33',   'Vikas'), ('Salary', '41313',   '22',   'Vikas'), ('Salary', '41313',   '11',   'Ramesh'), ('Salary', '41313',   '99',   'Vicky'), ('Salary', '41320',   '88',   'Vicky'), ('Salary', '41320',   '76',   'Vikas'), ('Salary', '41320',   '545',  'Ramesh'), ('Savings',   '41306',    '3',    'Vicky'), ('Savings',   '41306',    '27',   'Ramesh'), ('Savings',   '41306',    '324',  'Ramesh'), ('Savings',   '41306',    '887',  'Vikas'), ('Savings',   '41313',    '998',  'Vicky'), ('Savings',   '41313',    '6754', 'Vikas'), ('Savings',   '41313',    '3457', 'Ramesh'), ('Savings',   '41313',    '5434', 'Vicky'), ('Savings',   '41320',    '6554', 'Ramesh'), ('Savings',   '41320',    '3322', 'Ramesh'), ('Savings',   '41320',    '6542', 'Vikas') ;

Now I want output in following format-

Salary                         ST            Savings              STT  GT

Row Labels 01-Feb-13 08-Feb-13 15-Feb-13      01-Feb-13 08-Feb-13 15-Feb-13 Ramesh       1        1           1       3      2       1          2          5    8 Vicky        2        1           1       4      1       2          0          3    7 Vikas        1        2           1       4      1       1          1          3    7 Grand Total  4        4           3       11     4       4          3         11   22

Note- ST= Salary Total ,STT= Saving Total ,GT= Grand Total (as Obtained in Pivot in Excel)

What I do now is take table into Excel and Make a pivot as such with COUNT of COD_ACCT in Values in Excel and BBH in Row and Prod_type and week in Columns in Pivot in Excel.

Can I do that same in TSql. It will save me all of that Pivot hassle.

Thanks! sql-server excel tsql pivot share|edit|delete|flag edited Feb 17 at 12:42

asked Feb 17 at 12:18 Vivek Singh 83 Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 17 at 12:21 Please suggest any terse SQL like PIVOT or CUBE syntax and any other syntax well – Vivek Singh Feb 17 at 12:44 add comment 1 Answer active oldest votes up vote 0 down vote

You can use the PIVOT function as well as GROUPING SETS in SQL Server 2008+.

Your code would be similar to this:

select case when bbh is null then 'Grand Total' else bbh end bbh, sum([Salary_2013-02-03]) [Salary_2013-02-03], sum([Salary_2013-02-10]) [Salary_2013-02-10], sum([Salary_2013-02-17]) [Salary_2013-02-17], sum([Salary_2013-02-03]+[Salary_2013-02-10]+[Salary_2013-02-17]) ST, sum([Savings_2013-02-03]) [Savings_2013-02-03], sum([Savings_2013-02-10]) [Savings_2013-02-10], sum([Savings_2013-02-17]) [Savings_2013-02-17], sum([Savings_2013-02-03]+[Savings_2013-02-10]+[Savings_2013-02-17]) STT from ( select     prod_type +'_'+    convert(varchar(10), cast(cast(week as int) as datetime), 120) week,     cod_acct,     bbh  from t ) src pivot ( count(cod_acct)  for week in ([Salary_2013-02-03], [Salary_2013-02-10], [Salary_2013-02-17], [Savings_2013-02-03], [Savings_2013-02-10], [Savings_2013-02-17]) ) piv group by grouping sets((bbh), )

See SQL Fiddle with Demo.

This gives the result:

SALARY_2013-02-03 | SALARY_2013-02-10 | SALARY_2013-02-17 | ST | SAVINGS_2013-02-03 | SAVINGS_2013-02-10 | SAVINGS_2013-02-17 | STT | -                1 |                 1 |                 1 |  3 |                  2 |                  1 |                  2 |   5 |                 2 |                 1 |                 1 |  4 |                  1 |                  2 |                  0 |   3 |                 1 |                 2 |                 1 |  4 |                  1 |                  1 |                  1 |   3 |                 4 |                 4 |                 3 | 11 |                  4 |                  4 |                  3 |  11 |

share|edit answered Feb 17 at 15:13 bluefeet 80.1k1754111 Thanks Buddy! That helped a lot! – Vivek Singh Feb 19 at 13:54 @VivekSingh I am glad it is working for you. If you found this answer helpful, then be sure to accept it via the checkmark to the left of it. It will let future visitors know that the question was answered and you will get site rep for accepting. :) – bluefeet Feb 19 at 13:55 add comment Your Answer discard Would you like to have responses to your questions sent to you via email?

tagged sql-server × 72096 excel × 27964 tsql × 21625 pivot × 1825

asked

5 months ago

viewed

164 times

active

5 months ago Community Bulletin blog Podcast #50 – Listen To This Podcast

Related 63 SQL Server PIVOT examples? 6 PIVOT in sql 2005 1 How can I use an SQL Pivot for this? 2 How to count incrementally in SQL SERVER 4 Pivots with dynamic columns in SQL Server 1 PIVOT SQL - How to display the column we are pivoting on? 1 How to pivot this table 1 How to ORDER BY in SQL PIVOT 0 Pivot sql with totals 0 Sql pivot between 2 tables question feed about help blog chat data legal privacy policy jobs advertising info mobile contact us feedback Technology 	Life / Arts 	Culture / Recreation 	Science 	Other

Stack Overflow Server Fault Super User Web Applications Ask Ubuntu Webmasters Game Development TeX - LaTeX

Programmers Unix & Linux Ask Different (Apple) WordPress Answers Geographic Information Systems Electrical Engineering Android Enthusiasts IT Security

Database Administrators Drupal Answers SharePoint User Experience Mathematica more (13)

Photography Science Fiction & Fantasy Seasoned Advice (cooking) Home Improvement more (13)

English Language & Usage Skeptics Mi Yodeya (Judaism) Travel Christianity Arqade (gaming) Bicycles Role-playing Games more (21)

Mathematics Cross Validated (stats) Theoretical Computer Science Physics MathOverflow more (7)

Stack Apps Meta Stack Overflow Area 51 Stack Overflow Careers

site design / logo © 2013 stack exchange inc; user contributions licensed under cc-wiki with attribution required rev 2013.7.26.898

--

tech on the net

Home Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL

Languages

C Language

UNIX

General UNIX Linux

Misc

ASCII Table Java Clipart

Other Sites

CheckYourMath BigActivities

Home > Oracle/PLSQL > Question & Answer

Oracle/PLSQL: Test a string for a numeric value

Question: In Oracle, I want to know if a string value is numeric only. How can I do this?

Answer: To test a string for numeric characters, you could use a combination of the length function, trim function, and translate function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1 is the string value that you are testing

This function will return a null value if string1 is numeric. It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example, LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))); 	would return 1 LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))); 	would return 2 LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))); 	would return null LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))); 	would return null

Learn more about the translate function.

Learn more about the length function.

Learn more about the trim function.

Terms of Service Privacy Policy

Copyright © 2003-2013 TechOnTheNet.com. All rights reserved. We are not responsible for any loss or liability incurred by using this information.

How to disable Active Desktop in Windows XPThe desktop is an integral component of the Windows operating systemand cannot be removed. However, you do have the option of disabling theresource-hungry Active Desktop and using the classic user interfaceinstead. The biggest advantage of doing this is that your PC will feelmuch snappier. Unfortunately, it is much harder to disable the ActiveDesktop in Windows XP than it was in previous versions of the operatingsystem - it requires a change to the registry.To open the registry editor, go to "Start, Run", type regeditand hit "OK". Find the entry"HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer"and create the DWORD values ForceActiveDesktopOn and NoActiveDesktop. To do so, right-click into the right-hand window and select "New,DWORD". Then change the binary value of the registry key called"ForceActiveDesktopOn" to 0 and that of "NoActiveDesktop" to 1.

--

Click Start, Run and type CMD.EXE

Note: In Windows Vista, you need to open an elevated Command Prompt window. To do so, click Start, click All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.

Type this command and press Enter:

net stop winmgmt

Using Windows Explorer, rename the folder %windir%\System32\Wbem\Repository. (For example, %windir%\System32\Wbem\Repository_bad). %windir% represents the path to the Windows directory, which is typically C:\Windows.

Switch to Command Prompt window, and type the following and press ENTER after each line:

net start winmgmt

EXIT

Courtesy: The above is excerpted from Microsoft Technet article WMI Isn't Working! © 2007 Microsoft Corporation. All rights reserved. For Windows XP Service Pack 2

Click Start, Run and type the following command:

rundll32 wbemupgd, UpgradeRepository

This command is used to detect and repair a corrupted WMI Repository. The results are stored in the setup.log (%windir%\system32\wbem\logs\setup.log) file. For Windows Vista

Open an elevated Command Prompt window. To do so, click Start, click All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.

Type the following command:

winmgmt /salvagerepository

The above command Performs a consistency check on the WMI repository, and if an inconsistency is detected, rebuilds the repository. The content of the inconsistent repository is merged into the rebuilt repository, if it can be read. For Windows Server 2003

Use the following command to detect and repair a corrupted WMI Repository:

rundll32 wbemupgd, RepairWMISetup

Re-registering the WMI components (Ref WMI FAQ)

The .DLL and .EXE files used by WMI are located in %windir%\system32\wbem. You might need to re-register all the .DLL and .EXE files in this directory. If you are running a 64-bit system you might also need to check for .DLLs and .EXE files in %windir%\sysWOW64\wbem.

To re-register the WMI components, run the following commands at the command prompt:

cd /d %windir%\system32\wbem for %i in (*.dll) do RegSvr32 -s %i for %i in (*.exe) do %i /RegServer

Note that none of the above two methods restore the missing files related to Windows Management Instrumentation (WMI). So, below is a comprehensive repair procedure that restores all the missing WMI modules. In case of missing WMI modules, you may use the following method.

Comprehensive rebuild method

Important note: If you've installed a Service Pack, you need to insert your Windows XP CD with Service Pack integration (called as the Slipstreamed Windows XP CD). If you don't have one, you may point to the %Windir%\ServicePackFiles\i386 folder for a recent version of the system files required during WMI repair. Or you may create a slipstreamed Windows XP CD and insert it when prompted.

Click Start, Run and type the following command, and press ENTER:

rundll32.exe setupapi,InstallHinfSection WBEM 132 %windir%\inf\wbemoc.inf

Insert your Windows XP CD into the drive when prompted. Repair process should take few minutes to complete. Then restart Windows for the changes to take effect.