Help on SQL Query Pivot

Need help on SQL Query to pivot records so as to see them from this:

LOC         CODE      DATE                             HR   PR   SEG   PRICE     QTY
101963      RTEM      2018-02-05 00:00:00      4      2      1      -2000.00       0.000
101963      RTEM      2018-02-05 00:00:00      4      2      2      -2000.00       12.000
101748      RTEM      2018-02-05 00:00:00      1      7      1      -2000.00       0.000
101748      RTEM      2018-02-05 00:00:00      1      7      2      -2000.00       18.000
101748      RTEM      2018-02-05 00:00:00      1      7      3      -1999.70       33.100
101748      RTEM      2018-02-05 00:00:00      1      7      4      356.50       37.000

To something like below:

LOC         CODE      DATE                             HR   PR   SEG1   PRICE1 QTY1     SEG2  PRICE2       QTY2      SEG3  PRICE3    QTY3      SEG4   PRICE4    QTY4
101963      RTEM      2018-02-05 00:00:00      4      2      1      -2000.00       0.000     2        -2000.00     12.000
101748      RTEM      2018-02-05 00:00:00      1      7      1      -2000.00       0.000     2        -2000.00     18.000    3      -1999.70        33.100    4           356.50     37.000
LVL 1
mathew_sAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
Could you post the desired goal in a excel file so it looks cleaner.  It is hard to make out your desired goal.
0
Mark WillsTopic AdvisorCommented:
Hi,

This is my bread and butter :)  Your challenge is having 3 groups of columns to be pivoted. How many could there be ? If that is unknown, then will need to resort to Dynamic SQL.

Have written a couple of Articles about it  https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
mathew_sAuthor Commented:
This is what I am looking for attached in Excel file going from what there currently is in table to what I require. Hopefully this is more readable.
QueryResult.xlsx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mathew_sAuthor Commented:
No dynamic SQL, it will be a fixed number of columns so if I can get a solution to this I can expand it out. Thanks
0
yo_beeDirector of Information TechnologyCommented:
Are you opposed to using Excel with a sql or a odbc connection to the sql server?  

You have few things that you need to over come. The first is that your primary data table has a column named Seg with values 1 thru 4.   This needs to be changed with  a switch function to change the value to Seg1 thru Seg4.

If you need to pivot in MSSQL you still will need to create a dynamic table of headers being that the headers are pulling from actual data from the table.
0
mathew_sAuthor Commented:
It has to be done in SQL or TSQL code on the database end.
0
mathew_sAuthor Commented:
If there is some other method using TSQL instead of PIVOT, I am open to that.
0
mathew_sAuthor Commented:
I got it working using rank function. For future reference for others, this link helped me a lot.
https://groups.google.com/forum/#!topic/microsoft.public.sqlserver.programming/VdFCxRsTFlI
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
There is a RANK() function, but the solution you refer to is a ROW_NUMBER() function.

Well done on finding your solution.


It was going to be the basis of a solution I was going to suggest
select loc,code,[date],hr,pr
       ,row_number() over (partition by loc,code,[date],hr,pr order by [date],hr,pr,seg) as RN 
       ,seg,price,qty
from yourtable

Open in new window

But would have expressed it as a CTE and derive the results from that rather than a subquery.

With multiple columns and known / predictable number of 'new' columns, then often easier to use CASE or IIF constructs.

Nothing wrong with PIVOT, nor Dynamic SQL, just need to choose the best approach given the variables to achieve your desired result.
0
mathew_sAuthor Commented:
Using rank function was the key to finding a way to do this. The link I provided helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.