-2

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  CH212332   2                1
1   2017-01-01  3         3             1
1   2017-01-01  Banana1    1                2
2   2017-01-01  Apple1     1                1
3   2017-01-01  Peach1     1                1
3   2017-01-01  546462     2                1

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

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

(The missing values 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)

endlessend2525
  • 11
  • 1
  • 2
  • 7
  • Please provide sample data and desired results. – Gordon Linoff Jul 07 '17 at 11:46
  • Could you please provide more information about the line "Description of the product is in the first column of the formular" ? – Kaushik Nayak Jul 07 '17 at 12:06
  • Added both of your requests. Let me know if still unclear – endlessend2525 Jul 07 '17 at 12:19
  • Your column number field doesn't make sense... can you explain that – S3S Jul 07 '17 at 14:03
  • Now they are corrected. The ColNumber stands for the different columns on the Formular (Description, BarCode, Amount, etc...) hence for the first "Cookie1" it is ColNumber = 1 because it is in column "Description". The Row Number stands for the number of products on the same formular for the same costumer and SellDate. For "Banana1", it is row 2 because Costumer with ID1 bought a "Cookie1" and a "Banana1" on the same date. I hope it is clearer – endlessend2525 Jul 07 '17 at 14:10

1 Answers1

0

Try this it will give your Expected Result

SELECT ID
    ,SellDate
    ,ISNULL(Record, '') AS Record
    ,ISNULL(BarCode, '') AS BarCode
    ,ISNULL(Amount, '') AS Amount

FROM
(

SELECT ID, SellDate, 
 CASE WHEN ISNUMERIC(Record)=0 THEN Record ELSE NULL END AS Record
,CASE WHEN ISNUMERIC(Record)=1  AND 
        LEN(CASE WHEN ISNUMERIC(Record)=1 THEN Record ELSE NULL END)>1 THEN Record  ELSE NULL END AS BarCode
,CASE WHEN LEN(CASE WHEN ISNUMERIC(Record)=1 THEN Record ELSE NULL END)=1 THEN Record ELSE NULL END AS Amount           
 FROM #temptab
 )DT

Result

ID  SellDate    Record      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