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?
 
mathew_sConnect With a Mentor Author 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
 
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.