x

UNPIVOT and JOIN using col and row numbers

I have a data of order per costumer and per date that I achieved to present in the following way:

 ID    Date    Record   ColNumber   RowNumber
 ----------------------------------------------------------------

ColNumber / RowNumber being the position of the information in the formular (Example: Description of the product is in the first column of the formular,

                                 FORMULAR
                              ________________

 Description   BarCode   Amount   RetailPrice  Others
 -----------------------------------------------------

hence the description of the 4th product on the formular will be of the form:

 ID    Date    Record   ColNumber   RowNumber
 ----------------------------------------------------------------
 
 ID    Date  Description    1           4

"Record" contain all the columns of the formula (Description, BarCode, Amount, Retail Price, etc..)

So the aim is to have a table with the following columns:

 ID    Date    Description    BarCode    Amount    Retail
 ------------------------------------------------------------------------

I feel I need to use a mix of PIVOT and JOIN but what I achieved to do just brought me back to my initial "raw" table.

Here is a sample to make you understand:

 IF OBJECT_ID ('tempdb..#temptab') IS NOT NULL
 DROP TABLE #temptab

 CREATE TABLE #temptab (
 ID INT NOT NULL,
 SellDate date NOT NULL,
 Record nvarchar(255),
 ColNumber int, 
 RowNumber int)

 INSERT INTO #temptab (ID, SellDate, Record, ColNumber, RowNumber) VALUES
 (1, '2017-01-01', 'Cookie1', 1, 1),
 (1, '2017-01-01', '21312332', 1, 2),
 (1, '2017-01-01', '3', 1, 2),
 (1, '2017-01-01', 'Banana1', 2, 1),
 (2, '2017-01-01', 'Apple1', 1, 1),
 (3, '2017-01-01', 'Peach1', 1, 1),
 (3, '2017-01-01', '546462', 1, 2);

Which gives :

 ID    SellDate    Record    ColNumber        RowNumber
 1    2017-01-01    Cookie1       1                1
 1    2017-01-01    21312332   1                2
 1    2017-01-01    3           1                2
 1    2017-01-01    Banana1       2                1
 2    2017-01-01    Apple1       1                1
 3    2017-01-01    Peach1       1                1
 3    2017-01-01    546462       1                2

In the end I would like a table which look like that

 ID    SellDate    Description    BarCode    Amount            
 1    2017-01-01     Cookie1                
 1    2017-01-01                   21312332
 1    2017-01-01                                3
 1    2017-01-01      Banana1        
 2    2017-01-01   Apple1           
 3    2017-01-01   Peach1         
 3    2017-01-01                    546462

(The missing will be filled either by the reste of the information, I obviously didn't add the whole data in the table, or with NULL's)

more ▼

asked Jul 07 at 11:45 AM in Default

avatar image

endlessend2525
1 1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x385
x112
x44

asked: Jul 07 at 11:45 AM

Seen: 63 times

Last Updated: Jul 07 at 12:20 PM

Copyright 2017 Redgate Software. Privacy Policy