Need Advise...

Question

Need Advise...

rocky_498

SSCertifiable

Points: 6532
More actions
October 29, 2013 at 11:40 am

#278644

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

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,

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 SSCertifiable Points: 6532 More actions · Answer 3

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 SSCertifiable Points: 6532 More actions · Answer 4

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 SSC Guru Points: 183698 More actions · Answer 5

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 SSCertifiable Points: 6532 More actions · Answer 6

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 SSCertifiable Points: 6532 More actions · Answer 7

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.....