SQL help needed please!

Punt

Punt

Punt

Associate
Joined
17 Nov 2003
Posts
1,960
Location
Gateshead
Morning all

I have this table in SQL (2005), generated by a third party and cannot be changed...

Table1.png


T1, T2, T3 and T4 are monthly targets. In reality this table is around 250 rows.

A different third party needs to access this data in a different format; a new table is fine. I needs it to look like...

Table2.png


I did something similar many, many moons ago using a PIVOT in SQL but cannot for the life of me apply what I learned way back when to this.

I'm not saying a PIVOT is the best way to go about this. Any ideas or suggestions would be awesome.

Cheers!
 
I dont think you could get that output from a single PIVOT, the data doesn't exist for that (hence all the duplicated column values).

I would do a pivot to create the first 3 columns of your desired output ("Name","Type","Target") first. Then it would depend on how your sending the data to the third party.
If you're sending a file (xls, csv, etc), it's probably easy enough just to copy and paste the final column for each month.

If you're going to give them access to the database to run the query, wrap another query around the pivot query above and just select the same Target column for each month:

SELECT
Name,
Type,
Target AS 'Jan',
Target AS 'Feb',
Target AS 'Mar',
Target AS 'Apr',
Target AS 'May',
Target AS 'Jun',
Target AS 'Jul',
Target AS 'Aug',
Target AS 'Sep',
Target AS 'Oct',
Target AS 'Nov',
Target AS 'Dec'
FROM [PivotResult]
 
I don't understand how T1, T2, T3 and T4 can be monthly targets, but then they want the months as well, it's just duplicating data...
 
touch said:
I would do a pivot to create the first 3 columns of your desired output ("Name","Type","Target") first.
Click to expand...

Cheers touch. I was over thinking a little but your idea helped me out. UNPIVOT did the trick with the single column duplicated. It's a very small table so time/processing impact isn't a factor.

AHarvey said:
I don't understand how T1, T2, T3 and T4 can be monthly targets, but then they want the months as well, it's just duplicating data...
Click to expand...

Vendor A gives us data in one format, Vendor B wants it in a different format. I agree there is a lot of duplication but that's not my call, I just have to deal with it :)
 
Punt said:
Vendor A gives us data in one format, Vendor B wants it in a different format. I agree there is a lot of duplication but that's not my call, I just have to deal with it :)
Click to expand...

Do they have access to the database or are you pulling the data and then presenting it? If so, how?
 
Vendor A pushes it into a table, I create a new table with the modified data that Vendor B has access to.
 
You must log in or register to reply here.
Back
Top Bottom