Solved

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

Posted on 2010-11-22
32
267 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:kenuk110
  • 16
  • 14
32 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34186183
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
 

Author Comment

by:kenuk110
ID: 34186214
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34186250
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
 

Author Comment

by:kenuk110
ID: 34186298
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34186323
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
 

Author Comment

by:kenuk110
ID: 34186384
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34186545
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
 

Author Comment

by:kenuk110
ID: 34186617
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34186641
interesting... for me, it does not return the data as "xml" ...?
0
 

Author Comment

by:kenuk110
ID: 34186662
Is there something I should check at my end or do you think this will not work?
0
 

Author Comment

by:kenuk110
ID: 34186707
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
 

Author Comment

by:kenuk110
ID: 34193936
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34216349
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34216356
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
 

Author Comment

by:kenuk110
ID: 34216411
Hi,

Just going to try it ! REALLY appreciate this.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:kenuk110
ID: 34217446
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34217465
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
 

Author Comment

by:kenuk110
ID: 34217473
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34217503
>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34217507
I had changed a bit the sql, to avoid the trailing "," in the list
0
 

Author Comment

by:kenuk110
ID: 34217519
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34217540
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
 

Author Comment

by:kenuk110
ID: 34217563
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34217580
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
 

Author Comment

by:kenuk110
ID: 34217581
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
 

Author Comment

by:kenuk110
ID: 34217588
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34217596
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
 

Author Comment

by:kenuk110
ID: 34217603
Ahhh yeah! I can't thank you enough for this, life saver.
0
 

Author Closing Comment

by:kenuk110
ID: 34217619
Perfect solution. I can't thank you enough.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34217663
btw, please try

ORDER BY min(Keyno)

for your ordering .,.. might help ...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
back end of ssas cube views or tables? 2 24
sql server query? 6 26
using t-sql EXISTS 8 23
replication - alerts? 4 19
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now