sql pivot table joining - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sql pivot table joining

Author

Topic

n3w2sql
Starting Member

4 Posts

Posted - 2012-05-28 : 05:49:09

Hi I am trying to pivot some information together and struggling to figure out how to join my third table please can anybody help? this is my code:
select success.Class,
ontime.[1] as [Q1Ontime],
late.[1] as [Q1Late],
Success.[1]as [Q1Success],

ontime.[2] as [Q2Ontime],
late.[2] as [Q2Late],
Success.[2]as [Q1Success],

ontime.[3] as [Q3Ontime],
late.[3] as [Q3Late],
Success.[3]as [Q1Success],

ontime.[4] as [Q4Ontime],
late.[4] as [Q4Late],
Success.[4]as [Q1Success]
from
(
SELECT [Dept] as Class,
[1],[2],[3],[4]
FROM
(
SELECT [Dept]
,[Q]
,sum([Late]) Late
FROM lm.dbo.[tester]

GROUP BY [Dept],[Q]
) a
pivot
(
sum(late)
FOR Q IN ( [1],[2],[3],[4])
) AS PivotTable
) late
inner join
(
SELECT [Dept] as Class,
[1],[2],[3],[4]
FROM
(
SELECT [Dept]
,[Q]
,sum([ontime]) ontime
FROM lm.dbo.[tester]

GROUP BY [Dept],[Q]
) a
pivot
(
sum(ontime)
for Q IN ( [1],[2],[3],[4])
) AS PivotTable
) ontime
inner join
(
SELECT [Dept]
,[Q]
,sum(Success) Success
FROM lm.dbo.[allb]
WHERE Inceptyear = 2012
GROUP BY [Dept],[Q]
) a
pivot
(
sum(Success)
for Q IN ( [1],[2],[3],[4])
) AS PivotTable
) Success
inner join
on ontime.class = success.class

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:45:04

i think easiest way is this


SELECT m.[Class],
[Q1Ontime],
[Q1Late],
[Q1Success],
[Q2Ontime],
[Q2Late],
[Q2Success],
[Q3Ontime],
[Q3Late],
[Q3Success],
[Q4Ontime],
[Q4Late],
[Q4Success]
FROM
(
SELECT Dept AS [Class],
SUM(CASE WHEN Q = 1 THEN [ontime] END) AS [Q1Ontime],
SUM(CASE WHEN Q = 1 THEN [late] END) AS [Q1Late],
SUM(CASE WHEN Q = 2 THEN [ontime] END) AS [Q2Ontime],
SUM(CASE WHEN Q = 2 THEN [late] END) AS [Q2Late],
SUM(CASE WHEN Q = 3 THEN [ontime] END) AS [Q3Ontime],
SUM(CASE WHEN Q = 3 THEN [late] END) AS [Q3Late],
SUM(CASE WHEN Q = 4 THEN [ontime] END) AS [Q4Ontime],
SUM(CASE WHEN Q = 4 THEN [late] END) AS [Q4Late]
FROM  lm.dbo.[tester]
GROUP BY Dept
)m
INNER JOIN (
SELECT [Dept] AS [Class],
SUM(CASE WHEN [Q]=1 THEN Success END) AS [Q1Success],
SUM(CASE WHEN [Q]=2 THEN Success END) AS [Q2Success],
SUM(CASE WHEN [Q]=3 THEN Success END) AS [Q3Success],
SUM(CASE WHEN [Q]=4 THEN Success END) AS [Q4Success]
FROM lm.dbo.[allb]
WHERE Inceptyear = 2012
GROUP BY [Dept]
)n
ON n.[Class] = m.[Class]

Keep in mind that this will only return result for 2012 in second casecdue to hardcoded condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-30 : 03:41:39

Can you please post the DDl of the tables, some sample data for all tables and the Expected Result.

Your Query seems pretty complex.
There could be better ways of doing it.
But, for anyone here to work on it you would have to help us by posting the above mentioned.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

n3w2sql
Starting Member

4 Posts

Posted - 2012-05-30 : 04:34:54

Thanks visakh16, That works perfect and is much simpified than the way I was trying. Thanks again for looking into this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:13:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:14:00

quote:
Originally posted by vinu.vijayan

Can you please post the DDl of the tables, some sample data for all tables and the Expected Result.

Your Query seems pretty complex.
There could be better ways of doing it.
But, for anyone here to work on it you would have to help us by posting the above mentioned.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

will you ever read previous suggestions before posting yours?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-31 : 00:12:44

quote:
Originally posted by visakh16

quote:
Originally posted by vinu.vijayan

Can you please post the DDl of the tables, some sample data for all tables and the Expected Result.

Your Query seems pretty complex.
There could be better ways of doing it.
But, for anyone here to work on it you would have to help us by posting the above mentioned.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

will you ever read previous suggestions before posting yours?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Previous Suggestions are better understood, tested and learnt if the OP or the person giving the suggestion posts some sample data with it so that other people who visit these threads get to learn something or get to see how the logic is working. I don't think SQLTeam is some kinda HELPLINE where people come to ask for help when they are stuck and someone reads the post and solves their problems.

There are people who come here to learn stuff as well and what they see here are umpteen no. of threads without the structure of tables and without sample data. I can understand if the OP doesn't post sample data, but at least the people who solve the problems can do that for the OP.

I did read your posts and since both you and the OP didn't find it important to be nice enough to post some sample data so that millions of users who are new to MSSQL and visit this forum for learning purpose can understand what happened, that is why I asked the OP to post some sample data.

The only thing I didn't write in my post was "If Visakh's solution isn't what you are looking for". May be that was what ticked you off.

Serious Apologies for that Visakh.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

n3w2sql
Starting Member

4 Posts

Posted - 2012-05-31 : 04:45:16

Sorry vinu.vijayan for not reading your post but I didnt actually see it. I would like to supply the sample data but I am unsure of what you need. I wanted the data to show Class, q1ontime,q1late,q1Success through to q4.I still would like to know the logic so I can do pivot tables like this for the future. When you ask for a sample of DDL tables does that mean you want to know what fields are in the other tables that relate? Thanks

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-31 : 13:09:37

I am also agree on point of Vinu..visakh16 you are champ,No doubt(Most of the things we learn from your code) but we are learner.I also look for sample data to understand the requirement so that I can also provide some solution to requester.

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:57:19

quote:
Originally posted by vijays3

I am also agree on point of Vinu..visakh16 you are champ,No doubt(Most of the things we learn from your code) but we are learner.I also look for sample data to understand the requirement so that I can also provide some solution to requester.

Vijay is here to learn something from you guys.

I was not challenging that
I myself have also learned like that and I know the value of sample data for giving quick and accurate solution.
But 80 % of time if I see the same reply without even having a look at posted query at all it just gives a feeling like you're not even making a try!.
I can understand the point if question was so vague but here it was quite evident from what OP posted that it was an attempt for pivotting. In such cases I certainly feel we can give solution based on that and then probably add "if this is not what you wanted please post sample data to make scenario more clearer". That would look much better to OP as well as other posters rather than always seeing an automated kind of message like this!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 16:04:35

quote:
Originally posted by vinu.vijayan

quote:
Originally posted by visakh16

quote:
Originally posted by vinu.vijayan

Can you please post the DDl of the tables, some sample data for all tables and the Expected Result.

Your Query seems pretty complex.
There could be better ways of doing it.
But, for anyone here to work on it you would have to help us by posting the above mentioned.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

will you ever read previous suggestions before posting yours?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Previous Suggestions are better understood, tested and learnt if the OP or the person giving the suggestion posts some sample data with it so that other people who visit these threads get to learn something or get to see how the logic is working. I don't think SQLTeam is some kinda HELPLINE where people come to ask for help when they are stuck and someone reads the post and solves their problems.

There are people who come here to learn stuff as well and what they see here are umpteen no. of threads without the structure of tables and without sample data. I can understand if the OP doesn't post sample data, but at least the people who solve the problems can do that for the OP.

I did read your posts and since both you and the OP didn't find it important to be nice enough to post some sample data so that millions of users who are new to MSSQL and visit this forum for learning purpose can understand what happened, that is why I asked the OP to post some sample data.

The only thing I didn't write in my post was "If Visakh's solution isn't what you are looking for". May be that was what ticked you off.

Serious Apologies for that Visakh.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

No need of any apology
As told before I have seen umpteen threads before you've just restated what previous poster had already suggested. My only point was to make a try yourself and come up with different suggestions rather than simply restating earlier suggestions or just giving guidelines. Thats when you add value to yourself as well as to the forum.
I'd also started as a learner here and have learned a lot from the great people here. Thats one of reasons for my extensive commitment to this site. I wanted others also to try and learn by applying themselves which was the sole intention behind my statement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-01 : 03:34:19

quote:
Originally posted by n3w2sql

Sorry vinu.vijayan for not reading your post but I didnt actually see it. I would like to supply the sample data but I am unsure of what you need. I wanted the data to show Class, q1ontime,q1late,q1Success through to q4.I still would like to know the logic so I can do pivot tables like this for the future. When you ask for a sample of DDL tables does that mean you want to know what fields are in the other tables that relate? Thanks

DDL of the tables = Create Table script for the tables.
Sample Data = Some Data that you see.
Expected result Set = The way you want to see the results.

These three things should be posted because without this a lot of people(that includes me) might not understand the requirement 100%.

quote:
But 80 % of time if I see the same reply without even having a look at posted query at all it just gives a feeling like you're not even making a try!.

I gave it a try and came up with something like your query. But, you have a lot of experience and can understand what the OP wants, without Sample Data. That's not the same with us. 80% of the time if you see the same reply it means that 100 out of 80% of the times you could understand the OP's requirement but other people could not. Think from that angle.

And I can show you so many threads where its just a conversation between you and the OP without any sign of Sample Data or DDL for other Forum Visitors to understand.

When you build queries for posters then you must be testing it on some sample data Visakh. The OP might not be aware but when you make a reply to a post, you can post the data you worked with . Right?.....

You can look at the posts I've made. I make it a point to post the data I worked with so that other people can understand. It is a request from my side to everyone from the millions of users of this forum.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources