Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Advise...


Need Advise...

Author
Message
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
Hi Guys,

Need help.. Here is my sample data

LAT|BOX|5290113661|BP9744524|PHY|JAI|GREWAL|1|BI|Vacc Bill|MED|191.2||SHI||20131015|50242006001|||||6|116820411||
LAT|BARDI|TAMMY|20020 44th|PHY|MARK|MORADI|BI|Vacc Bill|MED|183.0||SHI||20131016|50242006001|||||6|116899590||
LAT|ARRE|SONIA|344 RICH|PHY|IAN|HANT|1|BI|Vacc Bill|MED|153||SHI||20131016|50242006|||||3|116786607|

AND I want to be my out put in Flat file like this.

LAT|BOX|5290113661|BP9744524|
PHY|JAI|GREWAL|1|
BI|Vaccine Bill|
MED|191.2||
SHI||20131015|50242006001|||||6|116820411||
LAT|BARDI|TAMMY|20020 44th Ave|
PHY|MARK|MORADI|
BI|Vaccine Bill|
MED|183.0||
SHI||20131016|50242006001|||||6|116899590||
LAT|ARRE|SONIA|344 RICHBELL RD|
PHY|IAN|HANTMAN|1|
BI|Vaccine Bill|
MED|153.8||
SHI||20131016|50242006|||||3|116786607|


I am using SSIS. Please any advise would be great appreciate...

Thank You.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16989
rocky_498 (10/29/2013)
Hi Guys,

Need help.. Here is my sample data

LAT|BOX|5290113661|BP9744524|PHY|JAI|GREWAL|1|BI|Vacc Bill|MED|191.2||SHI||20131015|50242006001|||||6|116820411||
LAT|BARDI|TAMMY|20020 44th|PHY|MARK|MORADI|BI|Vacc Bill|MED|183.0||SHI||20131016|50242006001|||||6|116899590||
LAT|ARRE|SONIA|344 RICH|PHY|IAN|HANT|1|BI|Vacc Bill|MED|153||SHI||20131016|50242006|||||3|116786607|

AND I want to be my out put in Flat file like this.

LAT|BOX|5290113661|BP9744524|
PHY|JAI|GREWAL|1|
BI|Vaccine Bill|
MED|191.2||
SHI||20131015|50242006001|||||6|116820411||
LAT|BARDI|TAMMY|20020 44th Ave|
PHY|MARK|MORADI|
BI|Vaccine Bill|
MED|183.0||
SHI||20131016|50242006001|||||6|116899590||
LAT|ARRE|SONIA|344 RICHBELL RD|
PHY|IAN|HANTMAN|1|
BI|Vaccine Bill|
MED|153.8||
SHI||20131016|50242006|||||3|116786607|


I am using SSIS. Please any advise would be great appreciate...

Thank You.


Your "sample data" and your desired output don't make any sense. It seems that your output has lots of information that isn't in your input. It breaks at random places. You are going to need to provide a lot more detailed information if you want help with this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18081
Here are some ideas using T-SQL.


WITH SampleData(String) AS(
SELECT 'LAT|BOX|5290113661|BP9744524|PHY|JAI|GREWAL|1|BI|Vacc Bill|MED|191.2||SHI||20131015|50242006001|||||6|116820411||' UNION ALL
SELECT 'LAT|BARDI|TAMMY|20020 44th|PHY|MARK|MORADI|BI|Vacc Bill|MED|183.0||SHI||20131016|50242006001|||||6|116899590||' UNION ALL
SELECT 'LAT|ARRE|SONIA|344 RICH|PHY|IAN|HANT|1|BI|Vacc Bill|MED|153||SHI||20131016|50242006|||||3|116786607|')
SELECT String,
   LEFT( String, CHARINDEX( '|PHY|', String)) AS LAT,
   SUBSTRING( String, CHARINDEX( 'PHY|', String), CHARINDEX( '|BI|', String) - CHARINDEX( '|PHY|', String)) AS PHY,
   SUBSTRING( String, CHARINDEX( 'BI|', String), CHARINDEX( '|MED|', String) - CHARINDEX( '|BI|', String)) AS BI,
   SUBSTRING( String, CHARINDEX( 'MED|', String), CHARINDEX( '|SHI|', String) - CHARINDEX( '|MED|', String)) AS MED,
   RIGHT( String, LEN(String) - CHARINDEX( '|SHI|', String)) AS SHI
FROM SampleData;


WITH SampleData(String) AS(
SELECT 'LAT|BOX|5290113661|BP9744524|PHY|JAI|GREWAL|1|BI|Vacc Bill|MED|191.2||SHI||20131015|50242006001|||||6|116820411||' UNION ALL
SELECT 'LAT|BARDI|TAMMY|20020 44th|PHY|MARK|MORADI|BI|Vacc Bill|MED|183.0||SHI||20131016|50242006001|||||6|116899590||' UNION ALL
SELECT 'LAT|ARRE|SONIA|344 RICH|PHY|IAN|HANT|1|BI|Vacc Bill|MED|153||SHI||20131016|50242006|||||3|116786607|'),
Delimited AS(
   SELECT String Original, REPLACE( REPLACE( REPLACE( REPLACE( String, '|PHY|', ',|PHY|'), '|BI|', ',|BI|'), '|MED|', ',|MED|'), '|SHI|', ',|SHI|') String
   FROM SampleData
)
SELECT *
FROM Delimited
CROSS APPLY dbo.DelimitedSplit8K(String, ',');

WITH SampleData(String) AS(
SELECT 'LAT|BOX|5290113661|BP9744524|PHY|JAI|GREWAL|1|BI|Vacc Bill|MED|191.2||SHI||20131015|50242006001|||||6|116820411||' UNION ALL
SELECT 'LAT|BARDI|TAMMY|20020 44th|PHY|MARK|MORADI|BI|Vacc Bill|MED|183.0||SHI||20131016|50242006001|||||6|116899590||' UNION ALL
SELECT 'LAT|ARRE|SONIA|344 RICH|PHY|IAN|HANT|1|BI|Vacc Bill|MED|153||SHI||20131016|50242006|||||3|116786607|')
SELECT REPLACE( REPLACE( REPLACE( REPLACE( String, '|PHY|', '|' + CHAR(10) + 'PHY|'), '|BI|', '|' + CHAR(10) + 'BI|'), '|MED|', '|' + CHAR(10) + 'MED|'), '|SHI|', '|' + CHAR(10) + 'SHI|') String
FROM SampleData;




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
Here I update my sample data


LAT|BOX|5290113661|BP9744524|PHY|JAI|GREWAL|1|BI|VaccBill|MED|191.2||SHI||20131015|50242
06001|||||6|116820411||
LAT|BARDI|TAMMY|20020 44th|PHY|MARK|MORADI|BI|Vacc Bill|MED|183.0||SHI||20131016|50242006001|||||6|116899590||
LAT|ARRE|SONIA|344 RICH|PHY|IAN|HANT|1|BI|Vacc Bill|MED|153||SHI||20131016|50242006|||||3|116786607|

AND I want to be my out put in Flat file like this.

LAT|BOX|5290113661|BP9744524|
PHY|JAI|GREWAL|1|
BI|Vacc Bill|
MED|191.2||
SHI||20131015|50242006001|||||6|116820411||

LAT|BARDI|TAMMY|20020 44th|
PHY|MARK|MORADI|
BI|Vacc Bill|
MED|183.0||
SHI||20131016|50242006001|||||6|116899590||

LAT|ARRE|SONIA|344 RICH|
PHY|IAN|HANT|1|
BI|Vacc Bill|
MED|153||
SHI||20131016|50242006|||||3|116786607|


Here I want, Split in second rows Where ("PHY","BI","MED","SHI")... Please let me know if you need more information....

Thank You...
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
Hi Luis,

Quick question, are you using any function here?

SELECT *
FROM Delimited
CROSS APPLY dbo.DelimitedSplit8K(String, ',');

If yes, could you please post this function as well....

Thank you for your help!
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18081
I won't post the function because that would be too easy for you and you won't learn.:-)
However, I'll leave you a link to the article that explains how it works and has the code as well. It's a great article and I suggest you to read it, if you don't want to, you still have the option to just download the code.
http://www.sqlservercentral.com/articles/Tally+Table/72993/


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
I really appreciate your good thought!!!

I will definite take a look that link...I want to finish this task ASAP so I was trying to find a easy way....


Thank you...
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
One more question.....

I think I got from Sample data what I am looking, Could you please guide me if i want to through your code in Store Procedure to grab a data from real data, how I can do it? I am so confuse which code should i change it or, What I did so far, I create my store procedure, that data I need and through the data in #temp table....

Guide me Please?

Please let me know if you need more information or my question is not clear.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search