Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to apply group by with self join for complex query.

Posted on 2012-12-21
3
Medium Priority
?
433 Views
Last Modified: 2012-12-21
How to apply the complex self join with group by. The data and sample output is attached.
Data-and-Desired-Output.xlsx
0
Comment
Question by:searchsanjaysharma
3 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 38714161
I created a subset of your data:

create table tab1 
(course char(10),
 uid char(10),
 subject char(10),
 im decimal)

insert into tab1 values ('B.Com', '12BCM1002', 'CMT-101', 22);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-102', 24);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-103', 25);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-104', 26);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-105', 28);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-106', 30);

insert into tab1 values ('B.Com', '12BCM1003', 'CMT-101', 22);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-102', 32);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-103', 42);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-104', 52);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-105', 62);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-106', 72);

Open in new window


And ran this:

select a.course, a.uid,
 max(case when corder = 1 then im else 0 end) as c101,
 max(case when corder = 2 then im else 0 end) as c102,
 max(case when corder = 3 then im else 0 end) as c103,
 max(case when corder = 4 then im else 0 end) as c104,
 max(case when corder = 5 then im else 0 end) as c105,
 max(case when corder = 6 then im else 0 end) as c106,
 c.total_score
from
(select course, uid, subject, im, row_number() over (partition by uid order by uid, subject) as corder
from tab1) as a
join
(select distinct b.course, b.uid, sum(b.im) over (partition by b.uid) as total_score 
from tab1 b) as c on a.uid = c.uid and a.course = c.course
group by a.course,a.uid, c.total_score

Open in new window


I believe it gets you what you want (although I won't promise that it's the most efficient way of getting it :) )
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38714205
** code changed!


This works also:

CREATE TABLE SomeTable (Course varchar(50), UID varchar(50), SubjectCode varchar(50), IM int)

INSERT INTO SomeTable (Course, UID, SubjectCode, IM) VALUES
('B.Com','12BCM1002','CMT-101',22),
('B.Com','12BCM1002','CMT-102',24),
('B.Com','12BCM1002','CMT-103',26),
('B.Com','12BCM1002','CMT-104',28),
('B.Com','12BCM1002','CMT-105',30),
('B.Com','12BCM1002','CMT-106',32),
('B.Com','12BCM1003','CMT-101',32),
('B.Com','12BCM1003','CMT-102',34),
('B.Com','12BCM1003','CMT-103',36),
('B.Com','12BCM1003','CMT-104',38),
('B.Com','12BCM1003','CMT-105',40),
('B.Com','12BCM1003','CMT-106',42),
('B.Com','12BCM1004','CMT-101',42),
('B.Com','12BCM1004','CMT-102',44),
('B.Com','12BCM1004','CMT-103',46),
('B.Com','12BCM1004','CMT-104',48),
('B.Com','12BCM1004','CMT-105',50),
('B.Com','12BCM1004','CMT-106',52),
('B.Com','12BCM1005','CMT-101',62),
('B.Com','12BCM1005','CMT-102',64),
('B.Com','12BCM1005','CMT-103',66),
('B.Com','12BCM1005','CMT-104',68),
('B.Com','12BCM1005','CMT-105',70),
('B.Com','12BCM1005','CMT-106',72),
('B.Com','12BCM1006','CMT-101',72),
('B.Com','12BCM1006','CMT-102',74),
('B.Com','12BCM1006','CMT-103',76),
('B.Com','12BCM1006','CMT-104',78),
('B.Com','12BCM1006','CMT-105',80),
('B.Com','12BCM1006','CMT-106',82)

DECLARE @sql varchar(max)

SET @sql= 'SELECT s1.Course, 
    s1.UID'

SELECT @sql = @sql + ', 
    (SELECT SUM(s2.IM) 
    FROM SomeTable s2 
    WHERE s2.Course = s1.Course AND s2.UID = s1.UID AND s2.SubjectCode = ''' + SubjectCode + ''') AS [' + SubjectCode + ']'
FROM SomeTable
GROUP BY SubjectCode
ORDER BY SubjectCode

SET @sql = @sql + ', 
    SUM(s1.IM) AS Total 
FROM SomeTable s1 
GROUP BY s1.Course, s1.UID 
ORDER BY s1.Course, s1.UID'

EXEC(@sql)

DROP TABLE SomeTable

Open in new window

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 38714740
tx
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

608 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