SQL help needed please!

Punt · 15 Aug 2018 at 11:34

Morning all

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

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...

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!

touch · 15 Aug 2018 at 13:40

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]

AHarvey · 15 Aug 2018 at 15:33

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...

skyripper · 15 Aug 2018 at 16:22

Are T1, T2, T3, T4 categories of Target to be achieved Monthly?

Punt · 15 Aug 2018 at 17:06

touch said:
I would do a pivot to create the first 3 columns of your desired output ("Name","Type","Target") first.

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...

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

AHarvey · 15 Aug 2018 at 17:12

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

Do they have access to the database or are you pulling the data and then presenting it? If so, how?

Punt · 15 Aug 2018 at 17:15

Vendor A pushes it into a table, I create a new table with the modified data that Vendor B has access to.

AHarvey · 15 Aug 2018 at 17:18

Punt said:
Vendor A pushes it into a table, I create a new table with the modified data that Vendor B has access to.

What an absolute ball ache for you. Glad you've got it sorted but it's not an ideal situation.

Punt · 15 Aug 2018 at 17:23

It is indeed a ballache but awkward ones like this don't come around very often.