SQL Query Help 2

[sqlblind]

Hi Guys

Im pretty new to sql and was wondering if one of you could help me out. I have a dataset which looks a bit like this:

Year crs_code school sub %
2011 KT101 ML French 30%
2011 KT101 Law Law 50%
2011 KT101 ML German 20%


and I need it to look like this

Year Crs_Code School1 School% School2 School2% Sub1 Sub1%
2011 KT101 ML 50% Law 50% French 30%

--Continued
Sub2 Sub2% Sub3 Sub3%
Law 50% German 20%

So effectively i need to pivot the data so its all on one row, however

Thanks
Dave

 

[r937]

this type of cosmetic re-arrangement of data should be done in the application, not the database

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sqlblind]

Hi r937,

Thanks for you response, I agree it should. However on this occasion i don’t have the luxury to going back to the application owner and asking for this.

Do you know how i might go about pivoting this data?

 

[r937]

i'm sorry, i don't understand the objection, or the remark about asking the application owner -- aren't you building part of the application, i.e. a report?

plus, it's not a straightforward pivot -- you also have some grouping/aggregation going on there, as well as detailed data




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sqlblind]

Sorry this is how the data has been supplied to me from an external party. I've given the task to try and pivot it so i only have one record for each crs_code.

 

[r937]

but why would you choose to accomplish your task with SQL?

using an application language would be the better approach

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sqlblind]

Cause unfortunately SQL is the only language i know a bit about.... If you could head me in the right direction i would be very greatful

Dave

 

[simian336]

Dave,

Your actually doing a summary and a pivot.

Does it need to be in a table or can it be more of a report?

Simi

 

[simian336]

hey dave,

Also do

select cRS_code, Count(crs_code)
from yourtable
group by year, crs_code

and see what the max number is your dealing with.

Simi

 

[sqlblind]

Thanks for your response Simi,

It needs to be in a table if at all possible.

I have over 1600 records of which each crs_code can occur more than once. this is due to the fact a crs_code can have muliple subjects each with a different %'s.

 

[simian336]

The problem is the unknown number of sub's.

Take a look at

http://dotnetgalactics.wordpress.co...t-on-unknown-number-of-columns-dynamic-pivot/

Simi

 

[Qik3Coder]

sqlblind,
Gimmie a lil bit. I wrote something last week for this, and can't find it, so i have to redo it.
If you could post some more sample data it would be very helpful.

r937,
Sometimes we're only given a hammer...so we swing it.


BBS,
Lodlaiden

I'll answer your question, not solve your problem

 

[Qik3Coder]

That was a LOT easier to write the second time...

SETUP:

CREATE TABLE myTestData(
[Year] int,
CRS_Code varchar(10),
School varchar(10),
Sub varchar(10),
prcnt money)

INSERT INTO myTestData
SELECT 2011, 'KT101', 'ML', 'English', .10 UNION ALL
SELECT 2011, 'KT101', 'ML', 'French', .60 UNION ALL
SELECT 2011, 'KT101', 'ML', 'German', .30 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'English', .60 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'French', .40 UNION ALL
SELECT 2011, 'KT102', 'ML', 'French', .50 UNION ALL
SELECT 2012, 'KT102', 'ML', 'French', .80 UNION ALL
SELECT 2013, 'KT102', 'ML', 'Japanese', .80 UNION ALL
SELECT 2013, 'KT102', 'JK', 'English', .20

QUERY:

DECLARE @_SQL varchar(8000)
SELECT @_SQL = 'SELECT [Year], CRS_Code'
SELECT  @_SQL = @_SQL + ', MAX(CASE WHEN t.School = ''' + tabs.School + ''' and t.SUB = ''' + 
	tabs.Sub + ''' THEN t.prcnt else 0 END) as ['+tabs.School+'-'+tabs.Sub +']'
	FROM (Select School, SUB FROM (SELECT DISTINCT SUB FROM myTestData) as a
	CROSS JOIN (SELECT DISTINCT School FROM myTestData) as b) as Tabs
SELECT @_SQL = @_SQL + 'FROM myTestData t GROUP BY [YEAR], CRS_Code'

Exec(@_SQL)

HTH,
Lodlaiden

I'll answer your question, not solve your problem

 

[sqlblind]

Thanks Simi, Thant looks very interestin...

Qik3coder, thanks also for your resoinse. Please see below for sameple data, this is how it has been presented to me...

Year crs_code school sub %
2011 KT101 ML French 30%
2011 KT101 Law Law 50%
2011 KT101 ML German 20%
2011 Q432 Biology Biology 100%
2011 T534 CS Medicine 50%
2011 T534 CS Radiological 50%

and I need it to look like this

Year Crs_Code School1 School% School2 School2% Sub1 Sub1%
2011 KT101 ML 50% Law 50% French 30%
2011 Q432 Biology 100% NULL NULL Biology 100%
2011 T534 CS 100% NULL NULL Medicine 50%

--Continued
Sub2 Sub2% Sub3 Sub3%
Law 50% German 20%
NULL NULL NULL NULL
Radiological 50% NUL NULL

Thanks again

Dave

 

[Qik3Coder]

sqlblind,
Take what I gave you, run it.

I know it's not exactly what you asked for, but sometimes what's asked for and what'
s technically feasible are different.

You could do the extra rows with the schools, but it gets weird with the School 1 for 1 row not being the same for the others.

I'm sure the Dos XX's man (gmmastros) will chime in, but I'm going to bow out from trying to create that kind of dynamic sql. It will wind up doing something weird with a Row_NUMBER or something.

Lod

I'll answer your question, not solve your problem

 

[markros]

Hey Simi,

It's interesting you also asked this question. I am going to write a new blog this weekend on the Dynamic Pivot on multiple columns problem.

When I saw your link, I thought at first, that the guy beat me up to it, but I see it's just a dynamic pivot for one column, not multiple.



PluralSight Learning Library

 

[simian336]

markros,

Where is your blog?

Simi

 

[markros]

I haven't written it yet - it's in my head.

I blog at LessThanDot and at

http://www.beyondrelational.com

website



PluralSight Learning Library

 

[markros]

There is a new blog on the topic we discussed

Dynamic PIVOT on multiple columns

PluralSight Learning Library

 

[sqlblind]

Thanks Guys, you've all been a great help. Im still having a few problems getting it to work with my example but im sure ill work it out.

Thanks again