dimanche 10 juillet 2016

How to introduced a new column based on parent and child id combinations in SQL Server table

The entry id is unique and based on point of sale number. I would like to group all the variations of orders grouped by parent & child ID within a receipt. Using the data below I would like to introduce a new column that would allow me to group the unique combinations of parent and Child Id in SQL Management Studio.

This is my code :

SELECT
       [EntryId]
      ,[CheckNumber]  ReceiptId
      ,[ParentId]
      ,[FKItemId] ChildId
FROM POSITEMS 

Table

EntryId |     ReceiptId |   ParentId       | ChildId      
1069869 |     10064     |      0000        | 3010    
1069870 |     10064     |      3010        | 20095     
1069899 |     10064     |      3010        | 20070   
1069914 |     10064     |      3010        | 20040       
1069929 |     10064     |      0000        | 3010     
1069930 |     10064     |      3010        | 20095   
1069952 |     10064     |      3010        | 20090   
1069967 |     10064     |      3010        | 20050 

The stars denote the two unique parent child id combinations that I would like to group from the result set.

EntryId |     ReceiptId |   ParentId       | ChildId, GroupedID        
1069869 |     10064     |      0000        | 3010   , 1 
1069870 |     10064     |      3010        | 20095  , 2     
1069899 |     10064     |      3010        | 20070  , 2 
1069914 |     10064     |      3010        | 20040  , 2     
1069929 |     10064     |      0000        | 3010   , 3  
1069930 |     10064     |      3010        | 20095  , 4 
1069952 |     10064     |      3010        | 20090  , 4  
1069967 |     10064     |      3010        | 20050  , 4

Aucun commentaire:

Enregistrer un commentaire