Solved

SQL PIVOT?  2000

Posted on 2015-02-17
21
55 Views
Last Modified: 2015-02-19
WIth the below query we output data that is listed by inventory month then year.  The end-user want to show this in columns not rows.   That column will house the sales figures, and since they are already totaled I assume that this cannot be accomplished. Can anyone help?  Crystal reports didn't like the amount of data being grouped so it errors out.  I asked this question and accepted the solution (crystal reports) but it didn't work..



select       inv_Datem,
            inv_Datey,
            item,
            rollup,
                matlcode,
               pdesc,
               cert_desc,
               size,
               type,
            whse,
             inv_amt,
          qty_openOrders,
          cur_sales,
           avg_pricelb            
from #rollupsales
0
Comment
Question by:spoye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 6
21 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 40615231
You can reopen the previous question. What was not working ?

{+administrative edit}
0
 

Author Comment

by:spoye
ID: 40615235
Sorry I didn't' t know I could do that.   they want the months across the top, I tried to do this with crystal but got an error too much data to group...
0
 
LVL 18

Expert Comment

by:vasto
ID: 40615238
How many records do you have ?

{+administrative edit}
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:spoye
ID: 40615279
about 55,000
0
 
LVL 18

Expert Comment

by:vasto
ID: 40615306
How many different months ? If you want to show data for 10 years this will be 120 columns , which might not be possible.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40615362
spoye

In my time at EE I can say that without doubt the fastest way to get a SQL related question answered is to provide:

1. "sample data", and
2. "expected result"

anything sensitive or private may be replaced in the sample data & this data should be on a "per table" basis

the expected result should be based on the sample data
0
 

Author Comment

by:spoye
ID: 40615742
First I have 13 months, the query goes back one year from today.  I will attach a sample of data tomorrow,  Hopefully I will be able to get the format I need.  Thanks...
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40615763
sample does NOT mean we need a massive volume, please keep that in mind
0
 

Author Comment

by:spoye
ID: 40616528
Hopefully this isn't too large I am starting to believe this cannot be accomplished.  They want the month/years across the top with the current sales below.
example.xlsx
0
 
LVL 18

Expert Comment

by:vasto
ID: 40616686
I don't know what is the limit for cross tab. My guess is that the limit is for the number of columns and row after the data is summarized but I don't know. Did you try to use less data ?

You can also check this video: http://www.r-tag.com/Pages/Preview_Demo.aspx
It shows cross tab / pivot table with Crystal report SSRS and ad-hoc report.
You can download a trial version and check if it is going to work for you.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40617832
Q1: Do you create the temp table #rollupsales?  If yes, Q1a:  Can we see the complete script please?

Q2: What does the client want to see as the final report? (you haven't shown this yet)

(+edit}
I re-looked and see there are both years and months, so I changed this comment, sorry.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40618024
To achieve a "pivot" in older products like SQL 2000 you can use:
a. case expressions based on dates or date unit such as month number
b. an aggregation function, typically MAX() but can be others.
Don't be diverted by use of MAX() here, it is simply a method needed to get values into a row, the actual data should remain unchanged unless you do need to SUM() for example.
c. GROUP BY

For an example result of this:
| INV_DATEY |            ITEM |          ROLLUP |  INV_1 |  INV_2 |  INV_3 |  INV_4 |  INV_5 |  INV_6 |  INV_7 |  INV_8 |  INV_9 | INV_10 | INV_11 | INV_12 |  QTY_1 |  QTY_2 |  QTY_3 |  QTY_4 |  QTY_5 |  QTY_6 |  QTY_7 |  QTY_8 |  QTY_9 | QTY_10 | QTY_11 | QTY_12 |  SAL_1 |  SAL_2 |  SAL_3 |  SAL_4 |  SAL_5 |  SAL_6 |  SAL_7 |  SAL_8 |  SAL_9 | SAL_10 | SAL_11 | SAL_12 |  AVG_1 |  AVG_2 |  AVG_3 |  AVG_4 |  AVG_5 |  AVG_6 |  AVG_7 |  AVG_8 |  AVG_9 | AVG_10 | AVG_11 | AVG_12 |
|-----------|-----------------|-----------------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
|      2014 |   4001006250120 |   4001006250120 | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      2014 |   4001007500120 |   4001007500120 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      2014 |  8901007500120C |  8901007500120C | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 |
|      2014 |  8901045000060C |   Made To Order | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) |
|      2014 |  8901050000012C |   Made To Order | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      3 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) |
|      2015 | 0001007500039NT | 0001007500039NT |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      2015 |   1001001875120 |   1001001875120 |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      2015 |   4501005000120 |   4501005000120 | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

Open in new window


The SQL query for that is:
select
          inv_Datey
        , item
        , [rollup]
        , MAX( case when inv_Datem = 1  then inv_amt    else null end ) as inv_1
        , MAX( case when inv_Datem = 2  then inv_amt    else null end ) as inv_2
        , MAX( case when inv_Datem = 3  then inv_amt    else null end ) as inv_3
        , MAX( case when inv_Datem = 4  then inv_amt    else null end ) as inv_4
        , MAX( case when inv_Datem = 5  then inv_amt    else null end ) as inv_5
        , MAX( case when inv_Datem = 6  then inv_amt    else null end ) as inv_6
        , MAX( case when inv_Datem = 7  then inv_amt    else null end ) as inv_7
        , MAX( case when inv_Datem = 8  then inv_amt    else null end ) as inv_8
        , MAX( case when inv_Datem = 9  then inv_amt    else null end ) as inv_9
        , MAX( case when inv_Datem = 10 then inv_amt    else null end ) as inv_10
        , MAX( case when inv_Datem = 11 then inv_amt    else null end ) as inv_11
        , MAX( case when inv_Datem = 12 then inv_amt    else null end ) as inv_12

        , MAX( case when inv_Datem = 1  then qty_openOrders    else null end ) as qty_1
        , MAX( case when inv_Datem = 2  then qty_openOrders    else null end ) as qty_2
        , MAX( case when inv_Datem = 3  then qty_openOrders    else null end ) as qty_3
        , MAX( case when inv_Datem = 4  then qty_openOrders    else null end ) as qty_4
        , MAX( case when inv_Datem = 5  then qty_openOrders    else null end ) as qty_5
        , MAX( case when inv_Datem = 6  then qty_openOrders    else null end ) as qty_6
        , MAX( case when inv_Datem = 7  then qty_openOrders    else null end ) as qty_7
        , MAX( case when inv_Datem = 8  then qty_openOrders    else null end ) as qty_8
        , MAX( case when inv_Datem = 9  then qty_openOrders    else null end ) as qty_9
        , MAX( case when inv_Datem = 10 then qty_openOrders    else null end ) as qty_10
        , MAX( case when inv_Datem = 11 then qty_openOrders    else null end ) as qty_11
        , MAX( case when inv_Datem = 12 then qty_openOrders    else null end ) as qty_12

        , MAX( case when inv_Datem = 1  then cur_sales    else null end ) as sal_1
        , MAX( case when inv_Datem = 2  then cur_sales    else null end ) as sal_2
        , MAX( case when inv_Datem = 3  then cur_sales    else null end ) as sal_3
        , MAX( case when inv_Datem = 4  then cur_sales    else null end ) as sal_4
        , MAX( case when inv_Datem = 5  then cur_sales    else null end ) as sal_5
        , MAX( case when inv_Datem = 6  then cur_sales    else null end ) as sal_6
        , MAX( case when inv_Datem = 7  then cur_sales    else null end ) as sal_7
        , MAX( case when inv_Datem = 8  then cur_sales    else null end ) as sal_8
        , MAX( case when inv_Datem = 9  then cur_sales    else null end ) as sal_9
        , MAX( case when inv_Datem = 10 then cur_sales    else null end ) as sal_10
        , MAX( case when inv_Datem = 11 then cur_sales    else null end ) as sal_11
        , MAX( case when inv_Datem = 12 then cur_sales    else null end ) as sal_12


        , MAX( case when inv_Datem = 1  then avg_pricelb    else null end ) as avg_1
        , MAX( case when inv_Datem = 2  then avg_pricelb    else null end ) as avg_2
        , MAX( case when inv_Datem = 3  then avg_pricelb    else null end ) as avg_3
        , MAX( case when inv_Datem = 4  then avg_pricelb    else null end ) as avg_4
        , MAX( case when inv_Datem = 5  then avg_pricelb    else null end ) as avg_5
        , MAX( case when inv_Datem = 6  then avg_pricelb    else null end ) as avg_6
        , MAX( case when inv_Datem = 7  then avg_pricelb    else null end ) as avg_7
        , MAX( case when inv_Datem = 8  then avg_pricelb    else null end ) as avg_8
        , MAX( case when inv_Datem = 9  then avg_pricelb    else null end ) as avg_9
        , MAX( case when inv_Datem = 10 then avg_pricelb    else null end ) as avg_10
        , MAX( case when inv_Datem = 11 then avg_pricelb    else null end ) as avg_11
        , MAX( case when inv_Datem = 12 then avg_pricelb    else null end ) as avg_12
from rollupsales
group by 
          inv_Datey
        , item
        , [rollup]
order by 
          inv_Datey
        , item
        , [rollup]
;

Open in new window


So in this example (where years remain as rows) there is 12 columns for each metric and 4 metrics, plus 3 = 51 columns
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40618029
oh, changing to sort order to
order by
          item
        , inv_Datey DESC
        , [rollup]
produces:
|            ITEM | INV_DATEY |          ROLLUP |  INV_1 |  INV_2 |  INV_3 |  INV_4 |  INV_5 |  INV_6 |  INV_7 |  INV_8 |  INV_9 | INV_10 | INV_11 | INV_12 |  QTY_1 |  QTY_2 |  QTY_3 |  QTY_4 |  QTY_5 |  QTY_6 |  QTY_7 |  QTY_8 |  QTY_9 | QTY_10 | QTY_11 | QTY_12 |  SAL_1 |  SAL_2 |  SAL_3 |  SAL_4 |  SAL_5 |  SAL_6 |  SAL_7 |  SAL_8 |  SAL_9 | SAL_10 | SAL_11 | SAL_12 |  AVG_1 |  AVG_2 |  AVG_3 |  AVG_4 |  AVG_5 |  AVG_6 |  AVG_7 |  AVG_8 |  AVG_9 | AVG_10 | AVG_11 | AVG_12 |
|-----------------|-----------|-----------------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
| 0001007500039NT |      2015 | 0001007500039NT |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|   1001001875120 |      2015 |   1001001875120 |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|   4001006250120 |      2014 |   4001006250120 | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|   4001007500120 |      2014 |   4001007500120 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|   4501005000120 |      2015 |   4501005000120 | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|  8901007500120C |      2014 |  8901007500120C | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 |
|  8901045000060C |      2014 |   Made To Order | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) |
|  8901050000012C |      2014 |   Made To Order | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      3 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      2 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      0 | (null) | (null) | (null) |

Open in new window

0
 

Author Closing Comment

by:spoye
ID: 40619089
Thanks for all the help to all.  This works they like it, I never used the max function like this, thank you
0
 
LVL 18

Expert Comment

by:vasto
ID: 40619199
PortletPaul, I posted links to 3 solutions, you edited my post to remove the links and then copy pasted the content of one of the links as an answer ? The answer was available at 13:12:49 2015-02-17 and thanks to your moderation the asker was not able to implement it until 2 days later. I and all the other experts are volunteering here and you are loosing my time and the time of the asker.  I am not sure this is how this site should work. You probably should connsider asking somebody else to moderate questions, which you are going to answer. I read your private message where you are stating that the links did not complain with the EE policy. This is very subjective and weird since one of the links was to an article in CodeProject.com and the other one to a discussion on Microsoft.com.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40619648
I explained why the bind and disallowed links were removed in a message to you, which gave you adequate time to prepare an answer.

I did not copy/paste the solution either, it is very evident that my solution is specific to the fields of this question.
0
 
LVL 18

Expert Comment

by:vasto
ID: 40619700
PortletPaul, I posted 3 links, 1 might be considered as a competing website and is 1:1 the approach in your answer. You do understand that copying a SQL and replacing the column names doesn't mean that you did not copied right ? The other 2 links were not competing websites and had the absolute answer, which is an exclusion from the blind link rule: "Blind links: Links with no commentary from the poster (except in the case of absolute answers, as above)."

The link to CodeProject.com was to an article , which include the absolute answer and is a better approach because it will get the columns from the existing data and there will be no month hardcoding. Why this link was removed ?
The links I posted are 3 of the first 4 links if you google: "SQLServer2000 transpose". Your answer is in the result # 2, result #3 (CodeProject) will be a better solution because it does not require hardcoding and will take care about the case when a month from 2 different years is presented ( for example January 2014 and January 2015)

Again, the result of your moderation is that the asker got the answer 2 days later and I feel like I am loosing my time. I sent this question for a review , hopefully you will be not the one who is going to review it.
0
 

Author Comment

by:spoye
ID: 40619941
Very sorry for all the confusion I didn't look at who posted the solutions.  To be honest I though the accepted answer was from the same publisher as the post above.  Again sorry.  Thank you all for the outstanding help I very much appreciate it.
0
 
LVL 18

Expert Comment

by:vasto
ID: 40619993
spoye , no reason to be sorry. I actually felt bad because you asked twice the same question. You can still reopen and request the previous question to be delete since the solution did not work for you. Points are not important at all. I even think that if EE removes them will be easier because it will not be necessary to explain each time when there is a dispute that the points are not important :)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40620012
It was not "good form" of me to on the one hand play "bad cop" and at the same time play the opposite
- for this I sincerely apologize to all.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question