By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Convert MS Access Pivot Query to SQL Server 2005

P: 29
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:

Expand|Select|Wrap|Line Numbers
  1.  TRANSFORM SUM(ps.psScheduled)
  2.     SELECT pi.piMaxTurns,
  3.            pi.piAMP,
  4.            pi.piTons,
  5.            '',
  6.            '',
  7.            ps.psPipe
  8.       FROM PipeScheduling ps
  9.                INNER JOIN
  10.            PipeInfo pi
  11.         ON ps.psPipe = pi.piPipe
  12.        AND ps.psMachine = pi.piMachine
  13.        AND ps.psPlant = pi.piPlant
  14.      WHERE ps.psPlant = 'W'
  15.        AND ps.psMachine = '1'
  16.        AND ps.psDate BETWEEN
  17.                #12/17/2007# 
  18.                   AND
  19.                #12/23/2007#
  20.   GROUP BY ps.psPipe,
  21.            pi.piSequence,
  22.            pi.piMaxTurns,
  23.            pi.piAMP,
  24.            pi.piTons
  25.   ORDER BY pi.piSequence
  26.      PIVOT ps.psDate
Dec 20 '07 #1
Share this Question
Share on Google+
18 Replies


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

Expand|Select|Wrap|Line Numbers
  1.     SELECT pi.piMaxTurns,
  2.            pi.piAMP,
  3.            pi.piTons,
  4.            '',
  5.            '',
  6.            ps.psPipe,
  7.            SUM(CASE WHEN psDate = '1/7/2008' THEN ps.psScheduled END) AS Col1,
  8.            SUM(CASE WHEN psDate = '1/8/2008' THEN ps.psScheduled END) AS Col2,
  9.            SUM(CASE WHEN psDate = '1/9/2008' THEN ps.psScheduled END) AS Col3,
  10.            SUM(CASE WHEN psDate = '1/10/2008' THEN ps.psScheduled END) AS Col4,
  11.            SUM(CASE WHEN psDate = '1/11/2008' THEN ps.psScheduled END) AS Col5,
  12.            SUM(CASE WHEN psDate = '1/12/2008' THEN ps.psScheduled END) AS Col6,
  13.            SUM(CASE WHEN psDate = '1/13/2008' THEN ps.psScheduled END) AS Col7,
  14.            pi.piSequence
  15.       FROM fpc44.PipeScheduling ps
  16.                INNER JOIN
  17.            fpc44.PipeInfo pi
  18.                ON ps.psPlant = pi.piPlant
  19.               AND ps.psMachine = pi.piMachine
  20.               AND ps.psPipe = pi.piPipe
  21.      WHERE ps.psDate BETWEEN
  22.                '1/7/2008'
  23.                    AND
  24.                '1/13/2008'
  25.        AND ps.psPlant = 'W'
  26.        AND ps.psMachine = '1'
  27.   GROUP BY ps.psPipe,
  28.            pi.piMaxTurns,
  29.            pi.piAMP,
  30.            pi.piTons,
  31.            pi.piSequence
  32.   ORDER BY pi.piSequence
  33.  
Jan 3 '08 #2

P: 29
Does anyone know a better way of doing this?
Apr 2 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Apr 2 '08 #4

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

PipeInfo:
piPlant,piMachine,piPipe,piSequence,piMaxTurns,piA MP,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,50,,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,psPipe,psScheduled,psPour ed
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,120,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,60,56
A,1/29/2008,1,244CPB,0,0
A,1/29/2008,1,245CPB,0,0
A,1/29/2008,1,303CPB,30,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,120,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,60,56
A,1/30/2008,1,244CPB,0,0
A,1/30/2008,1,245CPB,0,0
A,1/30/2008,1,303CPB,30,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,120,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,60,38
A,1/31/2008,1,244CPB,0,0
A,1/31/2008,1,245CPB,0,0
A,1/31/2008,1,303CPB,30,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,120,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,60,52
A,2/1/2008,1,244CPB,0,0
A,2/1/2008,1,245CPB,0,0
A,2/1/2008,1,303CPB,30,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
Apr 2 '08 #5

ck9663
Expert 2.5K+
P: 2,878
And how do you want your result?

-- CK
Apr 2 '08 #6

P: 29
pi.piAMP,
pi.piTons,
'',
'',
ps.psPipe,
psDate(0),
psDate(1),
psDate(2),
psDate(3),
psDate(4),
psDate(5),
psDate(6)
Apr 7 '08 #7

ck9663
Expert 2.5K+
P: 2,878
Based on the sample you posted. How would your pivoted table would look like?

-- CK
Apr 9 '08 #8

P: 29
Scheduled:
,,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,,,,


Poured:
,,0.508,,,153CPB,,,,
,,0.508,,,154CPB,,,,
,,0.508,,,155CPB,,,,
,,0.671,200,,183CPB,136,128,104,87
,,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,62,62,22,12
,,2.0945,,,364CPB,,,22,23
,,2.0945,,,365CPB,,,,
,,2.739,,,423CPB,,,,
,,2.739,,,424CPB,,,,
,,2.739,,,425CPB,,,,
Apr 22 '08 #9

ck9663
Expert 2.5K+
P: 2,878
You lost me here.....

You have


Expand|Select|Wrap|Line Numbers
  1. PipeInfo:
  2. piPlant,piMachine,piPipe,piSequence,piMaxTurns,piA MP,piTons
Expand|Select|Wrap|Line Numbers
  1.  
  2. PipeScheduling:
  3. psPlant,psDate,psMachine,psPipe,psScheduled,psPour ed
Expand|Select|Wrap|Line Numbers
  1. Pivot:
  2. ???
How would your pivot table look like?

-- CK
Apr 23 '08 #10

P: 29
Like the SQL I provided in the first two messages.
Apr 28 '08 #11

P: 29
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

Expand|Select|Wrap|Line Numbers
  1.             //string SQL1 = "";
  2.             //SQL1 += " TRANSFORM SUM(ps.";
  3.             //if (SchedPoured.Text.ToString() == "Scheduled")
  4.             //    SQL1 += "psScheduled)";
  5.             //else
  6.             //    SQL1 += "psPoured)";
  7.  
  8.             //SQL1 += "    SELECT pi.piMaxTurns,";
  9.             //SQL1 += "           pi.piAMP,";
  10.             //SQL1 += "           pi.piTons,";
  11.             //SQL1 += "           '',";
  12.             //SQL1 += "           '',";
  13.             //SQL1 += "           ps.psPipe";
  14.             //SQL1 += "      FROM " + MainMenu.PSDBQ + "PipeScheduling ps";
  15.             //SQL1 += "               INNER JOIN";
  16.             //SQL1 += "           " + MainMenu.PSDBQ + "PipeInfo pi";
  17.             //SQL1 += "        ON ps.psPipe = pi.piPipe";
  18.             //SQL1 += "       AND ps.psMachine = pi.piMachine";
  19.             //SQL1 += "       AND ps.psPlant = pi.piPlant";
  20.             //SQL1 += "     WHERE ps.psPlant = '" + PlantChr() + "'";
  21.             //SQL1 += "       AND ps.psMachine = '" + Machine.Text.ToString() + "'";
  22.             //SQL1 += "       AND ps.psDate BETWEEN";
  23.             //SQL1 += "               #" + DateTime.Parse(ChosenWeek.Text.ToString()).ToShortDateString() + "#";
  24.             //SQL1 += "                   AND";
  25.             //SQL1 += "               #" + DateTime.Parse(ChosenWeek.Text.ToString()).AddDays(6).ToShortDateString() + "#";
  26.             //SQL1 += "  GROUP BY ps.psPipe,";
  27.             //SQL1 += "           pi.piSequence,";
  28.             //SQL1 += "           pi.piMaxTurns,";
  29.             //SQL1 += "           pi.piAMP,";
  30.             //SQL1 += "           pi.piTons";
  31.             //SQL1 += "  ORDER BY pi.piSequence";
  32.             //SQL1 += "     PIVOT ps.psDate";
  33.  
May 5 '08 #12

P: 29
I have provided all the information I can about this issue. Can anyone help me. It would be greatly appreciated.
Jul 8 '08 #13

ck9663
Expert 2.5K+
P: 2,878
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
Jul 8 '08 #14

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

Expand|Select|Wrap|Line Numbers
  1. Max Turns,AMP,Tons,Sched,Poured,Type,Date(0),Date(1),Date(2),Date(3),Date(4),Date(5),Date(6)
  2. ,,0.508,,,153CPB,,,,
  3. ,,0.508,,,154CPB,,,,
  4. ,,0.508,,,155CPB,,,,
  5. ,,0.671,200,,183CPB,160,160,160,160
  6. ,,0.671,,,184CPB,,,,
  7. ,,0.671,,,185CPB,,,,
  8. ,,1.0605,100,,243CPB,,,,
  9. ,,1.0605,,,244CPB,,,,
  10. ,,1.0605,,,245CPB,,,,
  11. ,,1.535,,,303CPB,,,,
  12. ,,1.535,,,304CPB,,,,
  13. ,,1.535,,,305CPB,,,,
  14. ,,2.0945,50,,363CPB,80,80,80,80
  15. ,,2.0945,,,364CPB,,,,
  16. ,,2.0945,,,365CPB,,,,
  17. ,,2.739,,,423CPB,,,,
  18. ,,2.739,,,424CPB,,,,
  19. ,,2.739,,,425CPB,,,,
  20.  
Jul 8 '08 #15

ck9663
Expert 2.5K+
P: 2,878
Almost there....

Is there a unique key on PipeSchedule table? Based on this you need a unique rowId and a unique key on PipeSchedule would help.

-- CK
Jul 9 '08 #16

P: 29
Unfortunately I have four keys {psPlant, psDate, psMachine, psPipe}

The table looks like:

>psPlant,nvarchar(1)
>psDate,datetime
>psMachine,nvarchar(1)
>psPipe,nvarchar(15)
psScheduled,int
psPoured,int
psUpdated,bit
Jul 10 '08 #17

ck9663
Expert 2.5K+
P: 2,878
Here's your pivoted table. Just use this as subquery and join the other tables to get the other columns you need.


Expand|Select|Wrap|Line Numbers
  1. select psplant, psmachine, pspipe,
  2. date1, date2, date3, date4, date5, date6, date7
  3. from (select A.psplant, A.psmachine, A.pspipe, A.psDate,
  4.    rowid = 'date' + cast (
  5.       (
  6.          select count(*) from pipesched B
  7.          where 
  8.             a.psplant = b.psplant
  9.             and a.psmachine = b.psmachine
  10.             and a.pspipe = b.pspipe 
  11.             and cast(a.psDate as datetime) >= cast(b.psDate as datetime)      
  12.       ) as varchar(5))
  13.    from pipesched A) P
  14.    PIVOT
  15.    (
  16.       MIN(psdate)
  17.       FOR rowid in ([date1],[date2],[date3],[date4],[date5],[date6],[date7])
  18.    ) as pvt
  19. order by  psplant, psmachine, pspipe

Happy Coding!

-- CK
Jul 10 '08 #18

P: 29
Thanks for your help CK!!
Jul 11 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.