Solved

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

Posted on 2012-12-21
3
424 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
[X]
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
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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 …
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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