question

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)