Solved

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

Posted on 2012-12-21
3
417 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 22

Accepted Solution

by:
Steve Wales earned 500 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 92

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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