any way to do this in SQL?

Question

any way to do this in SQL?

quayludious

SSC Enthusiast

Points: 145
More actions
April 22, 2010 at 2:52 pm

#99136

Hi there,
I am using MSSQL2008. I have a flat file(tab-delimited) with 9 columns. The first row contains the column headers:
B_FI B_LC B_SC E_FI E_LC E_SC T_FI T_LC T_SC
The B,E and T prefix stands for balance, exempt, tax.
The FI, LC and SC are some codes.
Ok, so here is the kicker... I need to populate a table that has 4 columns
create table tmpPort
(
intID int identity(1,1) not null,
code varchar(20) not null,
exempt float not null,
balance float not null,
tax float not null
)
So that the table will contain 9 rows of data corresponding to what is in the file
B_FIB_LCB_SCE_FIE_LCE_SCT_FIT_LCT_SC
6349.629967.293138.510.340.50.160.00 0.00 0.00
, and store it so it looks like:
NAMEEXEMPTION BALANCETAX
FI0.34 6349.620
LC0.5 9967.290
SC0.16 3138.510
If anyone has any ideas, please help where you can. Much appreciated!
edit (what the results table would look like):
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
SET IDENTITY_INSERT dbo.tmpPort ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].tmpPort([intID], , [EXEMPTION], [BALANCE], [TAX])
SELECT 1645766, N'FI', N'0.34', N'6349.62', N'0' UNION ALL
SELECT 1645767, N'LC', N'0.5', N'9967.29', N'0' UNION ALL
SELECT 1645768, N'SC', N'0.16', N'3138.51', N'0'
COMMIT;
RAISERROR (N'[dbo].[tmpPort ]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
SET IDENTITY_INSERT [dbo].[tmpPort ] OFF;

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Looks like a job for DynamicCrossTab. See the related link in my signature.

Side note: If you'd like to see a coded sample based on your data please provide ready to use sample data (e.g. using INSERT INTO...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

quayludious SSC Enthusiast Points: 145 More actions · Answer 2

updated my post, I'll check that link out.

thanks 🙂

LutzM SSC Guru Points: 107049 More actions · Answer 3

Hmmm, now we have the SQL for the result set. But how about the source data? 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

quayludious SSC Enthusiast Points: 145 More actions · Answer 4

I had attached a source file example... its the <magic happens here> part of getting the external mess to the internal hotness that eludes me...

LutzM SSC Guru Points: 107049 More actions · Answer 5

quayludious (4/22/2010)
I had attached a source file example... its the <magic happens here> part of getting the external mess to the internal hotness that eludes me...

Unfortunately, your source file is not as well prepared as your final data sample. The missing parts are CREATE and INSERT INTO...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

quayludious SSC Enthusiast Points: 145 More actions · Answer 6

ummm.... its a tab-delimited flat file... I'm trying to figure out HOW to get it into SQL and the table in the format I want.