Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Advise... Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 11:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 219, Visits: 831
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.
Post #1509498
Posted Tuesday, October 29, 2013 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 13,481, Visits: 12,336
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)
Post #1509525
Posted Tuesday, October 29, 2013 1:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 3,662, Visits: 7,993
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509537
Posted Tuesday, October 29, 2013 1:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 219, Visits: 831
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...

Post #1509540
Posted Tuesday, October 29, 2013 1:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 219, Visits: 831
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!
Post #1509555
Posted Tuesday, October 29, 2013 1:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 3,662, Visits: 7,993
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509556
Posted Tuesday, October 29, 2013 2:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 219, Visits: 831
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...
Post #1509563
Posted Tuesday, October 29, 2013 2:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 219, Visits: 831
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.....
Post #1509576
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse