Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

continue - Subtypes count

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 40
SQL Express connecting form remote error 26 7 49
sql query help 2 57
Need help debbuging stored procedure 21 44
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…
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 …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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