Convert MS Access Pivot Query to SQL Server 2005

[smckibbe01]

I have Pivot Table query in MS Access that I would like to run in SQL Server 2005. Can anyone help me to convert the syntax over to SQL Server. The following is the query in MS Access:

 TRANSFORM SUM(ps.psScheduled)
    SELECT pi.piMaxTurns,
           pi.piAMP,
           pi.piTons,
           '',
           '',
           ps.psPipe
      FROM PipeScheduling ps
               INNER JOIN
           PipeInfo pi
        ON ps.psPipe = pi.piPipe
       AND ps.psMachine = pi.piMachine
       AND ps.psPlant = pi.piPlant
     WHERE ps.psPlant = 'W'
       AND ps.psMachine = '1'
       AND ps.psDate BETWEEN
               #12/17/2007# 
                  AND
               #12/23/2007#
  GROUP BY ps.psPipe,
           pi.piSequence,
           pi.piMaxTurns,
           pi.piAMP,
           pi.piTons
  ORDER BY pi.piSequence
     PIVOT ps.psDate

[smckibbe01]

This is what I've come up with so far. It is still alitte slow. Any advice?

    SELECT pi.piMaxTurns,
           pi.piAMP,
           pi.piTons,
           '',
           '',
           ps.psPipe,
           SUM(CASE WHEN psDate = '1/7/2008' THEN ps.psScheduled END) AS Col1,
           SUM(CASE WHEN psDate = '1/8/2008' THEN ps.psScheduled END) AS Col2,
           SUM(CASE WHEN psDate = '1/9/2008' THEN ps.psScheduled END) AS Col3,
           SUM(CASE WHEN psDate = '1/10/2008' THEN ps.psScheduled END) AS Col4,
           SUM(CASE WHEN psDate = '1/11/2008' THEN ps.psScheduled END) AS Col5,
           SUM(CASE WHEN psDate = '1/12/2008' THEN ps.psScheduled END) AS Col6,
           SUM(CASE WHEN psDate = '1/13/2008' THEN ps.psScheduled END) AS Col7,
           pi.piSequence
      FROM fpc44.PipeScheduling ps
               INNER JOIN
           fpc44.PipeInfo pi
               ON ps.psPlant = pi.piPlant
              AND ps.psMachine = pi.piMachine
              AND ps.psPipe = pi.piPipe
     WHERE ps.psDate BETWEEN
               '1/7/2008'
                   AND
               '1/13/2008'
       AND ps.psPlant = 'W'
       AND ps.psMachine = '1'
  GROUP BY ps.psPipe,
           pi.piMaxTurns,
           pi.piAMP,
           pi.piTons,
           pi.piSequence
  ORDER BY pi.piSequence

[smckibbe01]

Does anyone know a better way of doing this?

[ck9663]

It would be faster for me to build your query if I'm looking at your table that you want to pivot and the the desired result.If you can post some sample data, it would be better.

-- CK

[smckibbe01]

The following is PipeInfo for plant "A" and PipeScheduling for one week for plant "A".

PipeInfo:
piPlant,piMachi ne,piPipe,piSeq uence,piMaxTurn s,piAMP,piTons
A,1,153CPB,1,,, 0.508
A,1,154CPB,2,,, 0.508
A,1,155CPB,3,,, 0.508
A,1,183CPB,4,50 ,,0.671
A,1,184CPB,5,,, 0.671
A,1,185CPB,6,,, 0.671
A,1,243CPB,7,50 ,,1.0605
A,1,244CPB,8,,, 1.0605
A,1,245CPB,9,,, 1.0605
A,1,303CPB,10,, ,1.535
A,1,304CPB,11,, ,1.535
A,1,305CPB,12,, ,1.535
A,1,363CPB,13,5 0,,2.0945
A,1,364CPB,14,, ,2.0945
A,1,365CPB,15,, ,2.0945
A,1,423CPB,16,, ,2.739
A,1,424CPB,17,, ,2.739
A,1,425CPB,18,, ,2.739
A,1,483CPB,19,, ,3.469
A,1,484CPB,20,, ,3.469
A,1,485CPB,21,, ,3.469

PipeScheduling:
psPlant,psDate, psMachine,psPip e,psScheduled,p sPoured
A,1/28/2008,1,153CPB,0 ,0
A,1/28/2008,1,154CPB,0 ,0
A,1/28/2008,1,155CPB,0 ,0
A,1/28/2008,1,183CPB,0 ,0
A,1/28/2008,1,184CPB,0 ,0
A,1/28/2008,1,185CPB,0 ,0
A,1/28/2008,1,243CPB,0 ,0
A,1/28/2008,1,244CPB,0 ,0
A,1/28/2008,1,245CPB,0 ,0
A,1/28/2008,1,303CPB,0 ,0
A,1/28/2008,1,304CPB,0 ,0
A,1/28/2008,1,305CPB,0 ,0
A,1/28/2008,1,363CPB,0 ,0
A,1/28/2008,1,364CPB,0 ,0
A,1/28/2008,1,365CPB,0 ,0
A,1/28/2008,1,423CPB,0 ,0
A,1/28/2008,1,424CPB,0 ,0
A,1/28/2008,1,425CPB,0 ,0
A,1/28/2008,1,483CPB,0 ,0
A,1/28/2008,1,484CPB,0 ,0
A,1/28/2008,1,485CPB,0 ,0
A,1/29/2008,1,153CPB,1 20,0
A,1/29/2008,1,154CPB,0 ,0
A,1/29/2008,1,155CPB,0 ,0
A,1/29/2008,1,183CPB,0 ,0
A,1/29/2008,1,184CPB,0 ,0
A,1/29/2008,1,185CPB,0 ,0
A,1/29/2008,1,243CPB,6 0,56
A,1/29/2008,1,244CPB,0 ,0
A,1/29/2008,1,245CPB,0 ,0
A,1/29/2008,1,303CPB,3 0,28
A,1/29/2008,1,304CPB,0 ,0
A,1/29/2008,1,305CPB,0 ,0
A,1/29/2008,1,363CPB,0 ,0
A,1/29/2008,1,364CPB,0 ,0
A,1/29/2008,1,365CPB,0 ,0
A,1/29/2008,1,423CPB,0 ,0
A,1/29/2008,1,424CPB,0 ,0
A,1/29/2008,1,425CPB,0 ,0
A,1/29/2008,1,483CPB,0 ,0
A,1/29/2008,1,484CPB,0 ,0
A,1/29/2008,1,485CPB,0 ,0
A,1/30/2008,1,153CPB,1 20,4
A,1/30/2008,1,154CPB,0 ,0
A,1/30/2008,1,155CPB,0 ,0
A,1/30/2008,1,183CPB,0 ,80
A,1/30/2008,1,184CPB,0 ,0
A,1/30/2008,1,185CPB,0 ,0
A,1/30/2008,1,243CPB,6 0,56
A,1/30/2008,1,244CPB,0 ,0
A,1/30/2008,1,245CPB,0 ,0
A,1/30/2008,1,303CPB,3 0,27
A,1/30/2008,1,304CPB,0 ,0
A,1/30/2008,1,305CPB,0 ,0
A,1/30/2008,1,363CPB,0 ,0
A,1/30/2008,1,364CPB,0 ,0
A,1/30/2008,1,365CPB,0 ,0
A,1/30/2008,1,423CPB,0 ,0
A,1/30/2008,1,424CPB,0 ,0
A,1/30/2008,1,425CPB,0 ,0
A,1/30/2008,1,483CPB,0 ,0
A,1/30/2008,1,484CPB,0 ,0
A,1/30/2008,1,485CPB,0 ,0
A,1/31/2008,1,153CPB,1 20,72
A,1/31/2008,1,154CPB,0 ,0
A,1/31/2008,1,155CPB,0 ,0
A,1/31/2008,1,183CPB,0 ,0
A,1/31/2008,1,184CPB,0 ,0
A,1/31/2008,1,185CPB,0 ,0
A,1/31/2008,1,243CPB,6 0,38
A,1/31/2008,1,244CPB,0 ,0
A,1/31/2008,1,245CPB,0 ,0
A,1/31/2008,1,303CPB,3 0,19
A,1/31/2008,1,304CPB,0 ,0
A,1/31/2008,1,305CPB,0 ,0
A,1/31/2008,1,363CPB,0 ,0
A,1/31/2008,1,364CPB,0 ,0
A,1/31/2008,1,365CPB,0 ,0
A,1/31/2008,1,423CPB,0 ,0
A,1/31/2008,1,424CPB,0 ,0
A,1/31/2008,1,425CPB,0 ,0
A,1/31/2008,1,483CPB,0 ,0
A,1/31/2008,1,484CPB,0 ,0
A,1/31/2008,1,485CPB,0 ,0
A,2/1/2008,1,153CPB,1 20,104
A,2/1/2008,1,154CPB,0 ,0
A,2/1/2008,1,155CPB,0 ,0
A,2/1/2008,1,183CPB,0 ,0
A,2/1/2008,1,184CPB,0 ,0
A,2/1/2008,1,185CPB,0 ,0
A,2/1/2008,1,243CPB,6 0,52
A,2/1/2008,1,244CPB,0 ,0
A,2/1/2008,1,245CPB,0 ,0
A,2/1/2008,1,303CPB,3 0,26
A,2/1/2008,1,304CPB,0 ,0
A,2/1/2008,1,305CPB,0 ,0
A,2/1/2008,1,363CPB,0 ,0
A,2/1/2008,1,364CPB,0 ,0
A,2/1/2008,1,365CPB,0 ,0
A,2/1/2008,1,423CPB,0 ,0
A,2/1/2008,1,424CPB,0 ,0
A,2/1/2008,1,425CPB,0 ,0
A,2/1/2008,1,483CPB,0 ,0
A,2/1/2008,1,484CPB,0 ,0
A,2/1/2008,1,485CPB,0 ,0
A,2/2/2008,1,153CPB,0 ,0
A,2/2/2008,1,154CPB,0 ,0
A,2/2/2008,1,155CPB,0 ,0
A,2/2/2008,1,183CPB,0 ,0
A,2/2/2008,1,184CPB,0 ,0
A,2/2/2008,1,185CPB,0 ,0
A,2/2/2008,1,243CPB,0 ,0
A,2/2/2008,1,244CPB,0 ,0
A,2/2/2008,1,245CPB,0 ,0
A,2/2/2008,1,303CPB,0 ,0
A,2/2/2008,1,304CPB,0 ,0
A,2/2/2008,1,305CPB,0 ,0
A,2/2/2008,1,363CPB,0 ,0
A,2/2/2008,1,364CPB,0 ,0
A,2/2/2008,1,365CPB,0 ,0
A,2/2/2008,1,423CPB,0 ,0
A,2/2/2008,1,424CPB,0 ,0
A,2/2/2008,1,425CPB,0 ,0
A,2/2/2008,1,483CPB,0 ,0
A,2/2/2008,1,484CPB,0 ,0
A,2/2/2008,1,485CPB,0 ,0
A,2/3/2008,1,153CPB,0 ,0
A,2/3/2008,1,154CPB,0 ,0
A,2/3/2008,1,155CPB,0 ,0
A,2/3/2008,1,183CPB,0 ,0
A,2/3/2008,1,184CPB,0 ,0
A,2/3/2008,1,185CPB,0 ,0
A,2/3/2008,1,243CPB,0 ,0
A,2/3/2008,1,244CPB,0 ,0
A,2/3/2008,1,245CPB,0 ,0
A,2/3/2008,1,303CPB,0 ,0
A,2/3/2008,1,304CPB,0 ,0
A,2/3/2008,1,305CPB,0 ,0
A,2/3/2008,1,363CPB,0 ,0
A,2/3/2008,1,364CPB,0 ,0
A,2/3/2008,1,365CPB,0 ,0
A,2/3/2008,1,423CPB,0 ,0
A,2/3/2008,1,424CPB,0 ,0
A,2/3/2008,1,425CPB,0 ,0
A,2/3/2008,1,483CPB,0 ,0
A,2/3/2008,1,484CPB,0 ,0
A,2/3/2008,1,485CPB,0 ,0

[ck9663]

And how do you want your result?

-- CK

[smckibbe01]

pi.piAMP,
pi.piTons,
'',
'',
ps.psPipe,
psDate(0),
psDate(1),
psDate(2),
psDate(3),
psDate(4),
psDate(5),
psDate(6)

[ck9663]

Based on the sample you posted. How would your pivoted table would look like?

-- CK

[smckibbe01]

Scheduled:
,,0.508,,,153CP B,,,,
,,0.508,,,154CP B,,,,
,,0.508,,,155CP B,,,,
,,0.671,200,,18 3CPB,160,160,16 0,160
,,0.671,,,184CP B,,,,
,,0.671,,,185CP B,,,,
,,1.0605,100,,2 43CPB,,,,
,,1.0605,,,244C PB,,,,
,,1.0605,,,245C PB,,,,
,,1.535,,,303CP B,,,,
,,1.535,,,304CP B,,,,
,,1.535,,,305CP B,,,,
,,2.0945,50,,36 3CPB,80,80,80,8 0
,,2.0945,,,364C PB,,,,
,,2.0945,,,365C PB,,,,
,,2.739,,,423CP B,,,,
,,2.739,,,424CP B,,,,
,,2.739,,,425CP B,,,,


Poured:
,,0.508,,,153CP B,,,,
,,0.508,,,154CP B,,,,
,,0.508,,,155CP B,,,,
,,0.671,200,,18 3CPB,136,128,10 4,87
,,0.671,,,184CP B,,,,
,,0.671,,,185CP B,,,,
,,1.0605,100,,2 43CPB,,,,
,,1.0605,,,244C PB,,,,
,,1.0605,,,245C PB,,,,
,,1.535,,,303CP B,,,,
,,1.535,,,304CP B,,,,
,,1.535,,,305CP B,,,,
,,2.0945,50,,36 3CPB,62,62,22,1 2
,,2.0945,,,364C PB,,,22,23
,,2.0945,,,365C PB,,,,
,,2.739,,,423CP B,,,,
,,2.739,,,424CP B,,,,
,,2.739,,,425CP B,,,,

[ck9663]

You lost me here.....

You have

PipeInfo:
piPlant,piMachine,piPipe,piSequence,piMaxTurns,piA MP,piTons

PipeScheduling:
psPlant,psDate,psMachine,psPipe,psScheduled,psPour ed

Pivot:
???

How would your pivot table look like?

-- CK

[smckibbe01]

Like the SQL I provided in the first two messages.

[smckibbe01]

This is what the pivot table looked like when I wrote it with access. Do you know of a page on the internet that describes the differences between access and sql server 2005 pivot table syntax

            //string SQL1 = "";
            //SQL1 += " TRANSFORM SUM(ps.";
            //if (SchedPoured.Text.ToString() == "Scheduled")
            //    SQL1 += "psScheduled)";
            //else
            //    SQL1 += "psPoured)";

            //SQL1 += "    SELECT pi.piMaxTurns,";
            //SQL1 += "           pi.piAMP,";
            //SQL1 += "           pi.piTons,";
            //SQL1 += "           '',";
            //SQL1 += "           '',";
            //SQL1 += "           ps.psPipe";
            //SQL1 += "      FROM " + MainMenu.PSDBQ + "PipeScheduling ps";
            //SQL1 += "               INNER JOIN";
            //SQL1 += "           " + MainMenu.PSDBQ + "PipeInfo pi";
            //SQL1 += "        ON ps.psPipe = pi.piPipe";
            //SQL1 += "       AND ps.psMachine = pi.piMachine";
            //SQL1 += "       AND ps.psPlant = pi.piPlant";
            //SQL1 += "     WHERE ps.psPlant = '" + PlantChr() + "'";
            //SQL1 += "       AND ps.psMachine = '" + Machine.Text.ToString() + "'";
            //SQL1 += "       AND ps.psDate BETWEEN";
            //SQL1 += "               #" + DateTime.Parse(ChosenWeek.Text.ToString()).ToShortDateString() + "#";
            //SQL1 += "                   AND";
            //SQL1 += "               #" + DateTime.Parse(ChosenWeek.Text.ToString()).AddDays(6).ToShortDateString() + "#";
            //SQL1 += "  GROUP BY ps.psPipe,";
            //SQL1 += "           pi.piSequence,";
            //SQL1 += "           pi.piMaxTurns,";
            //SQL1 += "           pi.piAMP,";
            //SQL1 += "           pi.piTons";
            //SQL1 += "  ORDER BY pi.piSequence";
            //SQL1 += "     PIVOT ps.psDate";

[smckibbe01]

I have provided all the information I can about this issue. Can anyone help me. It would be greatly appreciated.

[ck9663]

I was trying to ask how the pivot table (list of fields) would look like. You said it looks like the first two message you post.

Those are queries. I'm sorry I easily get confused reading queries. If you post a sample table of how your pivot would look like, I might be able to help you.

See my postings above.

-- CK

[smckibbe01]

Here is some sample output data. I'm putting it in comma delimited format. I'm not sure if there is a better way.

Max Turns,AMP,Tons,Sched,Poured,Type,Date(0),Date(1),Date(2),Date(3),Date(4),Date(5),Date(6)
,,0.508,,,153CPB,,,,
,,0.508,,,154CPB,,,,
,,0.508,,,155CPB,,,,
,,0.671,200,,183CPB,160,160,160,160
,,0.671,,,184CPB,,,,
,,0.671,,,185CPB,,,,
,,1.0605,100,,243CPB,,,,
,,1.0605,,,244CPB,,,,
,,1.0605,,,245CPB,,,,
,,1.535,,,303CPB,,,,
,,1.535,,,304CPB,,,,
,,1.535,,,305CPB,,,,
,,2.0945,50,,363CPB,80,80,80,80
,,2.0945,,,364CPB,,,,
,,2.0945,,,365CPB,,,,
,,2.739,,,423CPB,,,,
,,2.739,,,424CPB,,,,
,,2.739,,,425CPB,,,,