How to reconfigure some data in a table then copy the result to another table

Hi,

I have a table that receives data from .csv file. The csv file is in a 'raw' format, the csv file actually comes from a program we have and we can't change the way it exports.

The data from this file needs to be reformatted to be used in another table that we produce reports from. I have attached the csv file in question.

Now the complicated bit, explaining what I want to happen to the data in this file.....let me give you some background info first so the file may make sense.

I work for a merchandising company, we merchandise products in supermarkets, shampoo, cereal etc etc. In the supermarket we have blocks, so lets say I'm working on the cereal block, you would have maybe 5 boxes of corn flakes at the front of the block, maybe 5 more deep on each of these 'facings', somewhere else on the block, maybe the top shelf, you would have coco pops for instance.

The file we receive contains the bar codes of these items and how many individual facings that product has, referred to in the file as 'FaceNo', it also tells us the position of the product on the block which is referred to as  'KeyNo'.

No, I'll try to explain what I need help on. The data from this file need to be changed in it's format to align with another table we have, the csv file list ALL products on the block, I really need it to 'group' duplicate UPCCodes in to one record, add the amount of facings they equal and also concatenate the KeyNo together with a comma.

Example:

Current file

Country    Store     BlockNo    UPCCode                 KeyNo    NbrFace      - and a few more lines
966           006        AE74         6001106110185       1             1
966           006        AE74         6001106110185       2             1
966           006        AE74         6001106110185       3             1
966           006        AE74         6669992222200       4             1
966           006        AE74         8835273548583       5             1
966           006        AE74         6001106110185       6             1

Now, what I need to have is the contents of the data in this table selected, reformatted and input in to another table called dbo.items. The original table is called dbo.PlanogramCode. As for the example above, the file we import just dumps it all in to the PlanogramCode table.

The reconfiguration needs to be like this

Country    Store     BlockNo    UPCCode                 KeyNo    NbrFace      - and a few more lines
966           006        AE74         6001106110185       1,2,3,6    4
966           006        AE74         6669992222200       4             1
966           006        AE74         8835273548583       5             1


I'm sure this lot may be a little confusing but I'll help explain it if needed if anyone can help.

Really appreciate any feedback or suggestions.

Regards,

Ken

 PlanogramCodeTable.sql 966-02-006-RKB-AE74.CSV
kenuk110Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
is the field keyno varchar, then :)

select t.country, t.store, t.blockno, t.upccode
 , substring( ( select ',' + x.keyno  
       from yourtable x
      where x.country = t.country
        and x.store = t.store
        and x.blockno = t.blockno
        and x.upccode = t.upccode
       order by cast(x.keyno as int)
       for xml path('')
   ), 2, 1000)  keynos 
 , ( select count(*)
       from yourtable x
      where x.country = t.country
        and x.store = t.store
        and x.blockno = t.blockno
        and x.upccode = t.upccode
   ) keycount
 from yourtable t
group by t.country, t.store, t.blockno, t.upccode

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you already, once the data is loaded, use this kind of trick to get the result in SELECT already:
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
0
 
kenuk110Author Commented:
Hi,

Thanks for the reply, I'm a novice in this area, MSSQL, is it possible to assist in helping me write the code, the link you sent I'm sure means it's 'doable' but it's like reading chinese!

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, let's start with this:

select t.Country    t.Store     t.BlockNo    t.UPCCode   , NbrFace   
, ( select i.KeyNo  
     from yourtable i  
    where i.Country = t.country
       and i.Store = t.Store 
       and i.BlockNO = t.BlockNo
       and i.UPCCode = t.UPCCode
     for XML path('')
) KeyNos
from yourtable t
group by t.Country    t.Store     t.BlockNo    t.UPCCode   , NbrFace   

Open in new window

0
 
kenuk110Author Commented:
Hi,

I tried this in a query window but I get the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'for'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, some "," missing
select t.Country   , t.Store   ,  t.BlockNo   , t.UPCCode   , NbrFace   
, ( select i.KeyNo  
     from yourtable i  
    where i.Country = t.country
       and i.Store = t.Store 
       and i.BlockNO = t.BlockNo
       and i.UPCCode = t.UPCCode
     for XML path('')
) KeyNos
from yourtable t
group by t.Country    t.Store     t.BlockNo    t.UPCCode   , NbrFace

Open in new window

0
 
kenuk110Author Commented:
It's better!! But I get a squiggle under the last t.Store bit and this error message:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 't'.

--------
select t.Country   , t.Store   ,  t.BlockNo   , t.UPCCode   , NbrFaces  
, ( select i.KeyNo  
     from dbo.PlanogramCode i  
    where i.Country = t.country
       and i.Store = t.Store
       and i.BlockNO = t.BlockNo
       and i.UPCCode = t.UPCCode
     for XML path('')
) KeyNos
from dbo.PlanogramCode t
group by t.Country    t.Store     t.BlockNo    t.UPCCode   , NbrFaces
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, same issue in the GROUP BY part:
select t.Country   , t.Store   ,  t.BlockNo   , t.UPCCode   , NbrFace   
, ( select i.KeyNo  
     from yourtable i  
    where i.Country = t.country
       and i.Store = t.Store 
       and i.BlockNO = t.BlockNo
       and i.UPCCode = t.UPCCode
     for XML path('')
) KeyNos
from yourtable t
group by t.Country    ,t.Store     t.BlockNo  ,  t.UPCCode   , NbrFace

Open in new window

0
 
kenuk110Author Commented:
Hi,

I got back results this time, this is one line of the result:

966      006      AE74      5000158061637      1      <KeyNo>3</KeyNo><KeyNo>3</KeyNo><KeyNo>3</KeyNo><KeyNo>3</KeyNo>

I'm not sure if you are testing the results here, the KeyNo bit looks strange??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
interesting... for me, it does not return the data as "xml" ...?
0
 
kenuk110Author Commented:
Is there something I should check at my end or do you think this will not work?
0
 
kenuk110Author Commented:
Hi again, I ran the query again, it comes back with the correct result, still the XML isn't coming back okay and also it's not adding the amount of facings 'NbrFaces' so if it finds two of the same UPCCode it should add up the amount to equal 2. Not sure if it could do the first part of the script then add the number of faces from the xml bit?

Heres the result of a duplicate UPCCode:

966      02      006      AE74      50158911      1      <KeyNo>16</KeyNo><KeyNo>17</KeyNo>
0
 
kenuk110Author Commented:
Hi,

Sorry to be a pain, is there anything I can do here myself to try to get the end result? If there is anything I can tell you or give you just ask.

Really appreciate your help here.

Regards,

Ken
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this test script, to be run on a test database:
create table yourtable ( country varchar(3), store varchar(3), Blockno varchar(4), upccode varchar(20), keyno int, nbrface int )
go
set nocount on
insert into yourtable values('966           ','006        ','AE74         ','6001106110185       ',1             ,1)
insert into yourtable values('966           ','006        ','AE74         ','6001106110185       ',2             ,1)
insert into yourtable values('966           ','006        ','AE74         ','6001106110185       ',3             ,1)
insert into yourtable values('966           ','006        ','AE74         ','6669992222200       ',4             ,1)
insert into yourtable values('966           ','006        ','AE74         ','8835273548583       ',5             ,1)
insert into yourtable values('966           ','006        ','AE74         ','6001106110185       ',6             ,1)
go
select t.country, t.store, t.blockno, t.upccode
 , ( select cast(x.keyno as varchar(10)) + ','
       from yourtable x
      where x.country = t.country
        and x.store = t.store
        and x.blockno = t.blockno
        and x.upccode = t.upccode
       for xml path('')
   ) keynos 
 from yourtable t
group by t.country, t.store, t.blockno, t.upccode

go

drop table yourtable

Open in new window


it returns this output:
country store blockno upccode              keynos
------- ----- ------- -------------------- -----------
966     006   AE74    6001106110185        1,2,3,6,
966     006   AE74    6669992222200        4,
966     006   AE74    8835273548583        5,

Open in new window


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
gotcha ....
I now see where I was wrong ...
I had posted, initially, in the subselect:
 select i.KeyNo  ... 

Open in new window

to get it to work (avoid the xml stuff)
 select i.KeyNo + ','  ... 

Open in new window

in case the KeyNo was int, the cast is needed (see example above)

sorry for that...
0
 
kenuk110Author Commented:
Hi,

Just going to try it ! REALLY appreciate this.
0
 
kenuk110Author Commented:
Hi,

It works perfectly, I'm confused about the last bit though, the Select i.KeyNo bit, do I have to change the big script you gave me?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the last post just explains what was wrong in my initial suggestion, which resulted in the XML output, instead of just the comma-delimited list ...
0
 
kenuk110Author Commented:
I thin kI know what you are saying, if I want the KeyNo to be 'int' just use the cast bit otherwise just leave that out?

One more thing, I needed it to add the amount of 'facings' and return the amount back in the results, so if there were 2 duplicate UPCcodes, it would return 2 back, or if there were three, 3 etc. Is this possible?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I thin kI know what you are saying, if I want the KeyNo to be 'int' just use the cast bit otherwise just leave that out?
no, because the comma-delimited list must be varchar.

for the "count":
select t.country, t.store, t.blockno, t.upccode
 , substring( ( select ',' + cast(x.keyno as varchar(10)) 
       from yourtable x
      where x.country = t.country
        and x.store = t.store
        and x.blockno = t.blockno
        and x.upccode = t.upccode
       for xml path('')
   ), 2, 1000)  keynos 
 , ( select count(*)
       from yourtable x
      where x.country = t.country
        and x.store = t.store
        and x.blockno = t.blockno
        and x.upccode = t.upccode
   ) keycount
 from yourtable t
group by t.country, t.store, t.blockno, t.upccode

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I had changed a bit the sql, to avoid the trailing "," in the list
0
 
kenuk110Author Commented:
BRILLIANT!!!!!!!

It works perfectly! I really cant thank you enough for this. One last thing, and just say NO if it's not possible, when I ORDER BY KeyNo, it doesn't put it in number order, as in, 1,2,3... it goes 1,10,11.. is there something I should do to make this happen. Again, if it's a lot of work I'll ask another question??
0
 
kenuk110Author Commented:
I ran the script but it doesn't put them in number order, I had an ODER BY keynos at the end but i took that out as I saw you had it in your script, it doesn't seem to order it though. It's fine though if it's a pain.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hm.... at which level did you put the ORDER BY?
resp at which level do you want the order?
sample data will help to explain ...
0
 
kenuk110Author Commented:
Oh, sorry about this, I also needed to output this info in to a table called dbo.iems. Is that possible? This seems to be growing in requests but I just remember mentioning it in the original question?
0
 
kenuk110Author Commented:
I just put the ORDER BY at the end of the script, after the group by bit. Forget this though, I didn't ask this originally anyway, sorry to have brought it up!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I posted initially, we first wanted to SELECT to see to get the correct results.
so, the order by issue apart, we now have a nice <SELECT ... > statement.

to "insert" that into another table, you can simply feed the insert by the select statement in question:
 INSERT INTO dbo.iems <SELECT ...> 

Open in new window

0
 
kenuk110Author Commented:
Ahhh yeah! I can't thank you enough for this, life saver.
0
 
kenuk110Author Commented:
Perfect solution. I can't thank you enough.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
btw, please try

ORDER BY min(Keyno)

for your ordering .,.. might help ...
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.