Solved

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

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

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
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…

856 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