Solved

continue - Subtypes count

Posted on 2009-07-08
2
170 Views
Last Modified: 2012-05-07
See related question.

How would I return 1 row with all the subtypes.  
The return row should be:
Id, T1S1, T1S2, T1S3, T2S1,T2S2,T2S3
T1S1 means Type=1 SubType=1, T2S1 means Type=2 and SubType=1 etc.
T1S1,T1S2... should show how many subtypes exist.
0
Comment
Question by:johnkainn
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24802355
Hope this helps
SELECT ID, sum( CASE WHEN Type=1 AND SubType=1 THEN 1 ELSE 0 END) T1S1,
sum( CASE WHEN Type=1 AND SubType=2 THEN 1 ELSE 0 END) T1S2,
sum( CASE WHEN Type=1 AND SubType=3 THEN 1 ELSE 0 END) T1S3,
sum( CASE WHEN Type=2 AND SubType=1 THEN 1 ELSE 0 END) T2S1,
sum( CASE WHEN Type=2 AND SubType=2 THEN 1 ELSE 0 END) T2S2,
sum( CASE WHEN Type=2 AND SubType=3 THEN 1 ELSE 0 END) T2S3
FROM ur_table
GROUP BY ID

Open in new window

0
 
LVL 2

Expert Comment

by:sunil_mails
ID: 24803600
put ur table structure to help experts gv u perfect solution.. if possible put 4-5 sample rows
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 5 55
Incremental load example 2 56
Please help for the below sql query. 1 29
SQL parsing XML works but want to do it another way 4 15
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question