1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL help needed please!

Discussion in 'HTML, Graphics & Programming' started by Punt, Aug 15, 2018.

  1. Punt

    Wise Guy

    Joined: Nov 17, 2003

    Posts: 1,384

    Location: Gateshead

    Morning all

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

    [​IMG]

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

    [​IMG]

    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!
     
  2. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,473

    Location: Sufferlandria

    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]
     
  3. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

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

    Wise Guy

    Joined: Jul 19, 2011

    Posts: 1,608

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

    Wise Guy

    Joined: Nov 17, 2003

    Posts: 1,384

    Location: Gateshead

    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.

    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 :)
     
  6. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

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

    Wise Guy

    Joined: Nov 17, 2003

    Posts: 1,384

    Location: Gateshead

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

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

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

    Wise Guy

    Joined: Nov 17, 2003

    Posts: 1,384

    Location: Gateshead

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