Solved

SQL PIVOT?  2000

Posted on 2015-02-17
21
49 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
  • 7
  • 7
  • 6
21 Comments
 
LVL 18

Expert Comment

by:vasto
Comment Utility
You can reopen the previous question. What was not working ?

{+administrative edit}
0
 

Author Comment

by:spoye
Comment Utility
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
Comment Utility
How many records do you have ?

{+administrative edit}
0
 

Author Comment

by:spoye
Comment Utility
about 55,000
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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 48

Expert Comment

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

Author Comment

by:spoye
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Microsoft SQL query 7 38
ASP SQL Syntax Duplicate Key 7 64
How to convert JSON file to csv? 7 49
.htaccess file settings 4 32
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now