Solved

continue - Subtypes count

Posted on 2009-07-08
2
166 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now