Data translation

Say I have a flat file like this
Name ID Type Info
===== == ====== =======
Name1 5 ABCD FirstRecord
Name2 6 DEFG SecondRecord
Well I want to save that into a database table like this
CREATE TABLE FlatFileRecords
(
ColumnID UNIQUEIDENTIFIER,
RecordSetID BIGINT,
ColumnName nvarchar(100),
ColumnData NVARCHAR(300)
)
Column ID would be a unique ID for the row
RecordSetID would split apart different flat files
Column Name would be the name of the column (from header)
ColumnData would be data under the column header
so it would be stored in the table like this
{GUID1} 1 Name Name1
{GUID1} 1 ID 5
{GUID1} 1 TYPE ABCD
{GUID1} 1 INFO FirstRecord
{GUID2} 1 Name Name2
{GUID2} 1 ID 6
{GUID2} 1 TYPE DEFG
{GUID2} 1 INFO SecondRecord
so basicly its taking a flat file and splitting it up into chunks that can
be eventually reassembled... by question is there any easy to reassemble
them in SQL Server? Any kind of definition i could set up to translate them
based on ID which everything in the ID would always have the same columns,
so if a certain ID comes up it reassembles the data from the SQL data table
form into the flat file type form? just in a data table so the flat file
above would come back in a temp table? I dont know if this is possible, if
you can think of anything please let me know or if this makes no sense let
me know because I typed this up fast off the top of my head, didnt have time
to really think it through yetSounds like you want UNPIVOT.
http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
information you can throw a stick at.
Rob
"Brian Henry" wrote:

> Say I have a flat file like this
>
> Name ID Type Info
> ===== == ====== =======
> Name1 5 ABCD FirstRecord
> Name2 6 DEFG SecondRecord
>
> Well I want to save that into a database table like this
> CREATE TABLE FlatFileRecords
> (
> ColumnID UNIQUEIDENTIFIER,
> RecordSetID BIGINT,
> ColumnName nvarchar(100),
> ColumnData NVARCHAR(300)
> )
> Column ID would be a unique ID for the row
> RecordSetID would split apart different flat files
> Column Name would be the name of the column (from header)
> ColumnData would be data under the column header
> so it would be stored in the table like this
>
> {GUID1} 1 Name Name1
> {GUID1} 1 ID 5
> {GUID1} 1 TYPE ABCD
> {GUID1} 1 INFO FirstRecord
> {GUID2} 1 Name Name2
> {GUID2} 1 ID 6
> {GUID2} 1 TYPE DEFG
> {GUID2} 1 INFO SecondRecord
> so basicly its taking a flat file and splitting it up into chunks that can
> be eventually reassembled... by question is there any easy to reassemble
> them in SQL Server? Any kind of definition i could set up to translate the
m
> based on ID which everything in the ID would always have the same columns,
> so if a certain ID comes up it reassembles the data from the SQL data tabl
e
> form into the flat file type form? just in a data table so the flat file
> above would come back in a temp table? I dont know if this is possible, if
> you can think of anything please let me know or if this makes no sense let
> me know because I typed this up fast off the top of my head, didnt have ti
me
> to really think it through yet|||thanks, hopefully this is what I want, I didn't know if this was a pivot
table or not, I never really worked with them before.
anyone else have any more insight on this? thanks!
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:FCCD6FDE-8246-4DE2-ADFD-09DBD46A12B4@.microsoft.com...
> Sounds like you want UNPIVOT.
> http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
> information you can throw a stick at.
> Rob
>
> "Brian Henry" wrote:
>
>|||If you prefer not to throw things check out Rac:)
In Help see:
Ungrouped Data
3. Transposing Columns to Rows
www.rac4sql.net
"Brian Henry" <nospam@.nospam.com> wrote in message
news:u1sKFAZbGHA.4040@.TK2MSFTNGP02.phx.gbl...
> thanks, hopefully this is what I want, I didn't know if this was a pivot
> table or not, I never really worked with them before.
> anyone else have any more insight on this? thanks!
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:FCCD6FDE-8246-4DE2-ADFD-09DBD46A12B4@.microsoft.com...
reassemble
file
>|||A pivot table is one where the values in a particular column become the fiel
d
names going across the table. So effectively, you're pivoting on your
'columnname' field.
So if you unpivot what you have, then you'll get something where your column
names will be one of the fields, with 4 rows per original record.
Does this help explain why UNPIVOT is what you want?
Rob
"Brian Henry" wrote:

> thanks, hopefully this is what I want, I didn't know if this was a pivot
> table or not, I never really worked with them before.
> anyone else have any more insight on this? thanks!
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:FCCD6FDE-8246-4DE2-ADFD-09DBD46A12B4@.microsoft.com...
>
>|||thanks a lot! Now to test all this out in actualy SQL
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:BCFB0265-009D-4D19-891D-CB0FEFBBD1D2@.microsoft.com...
>A pivot table is one where the values in a particular column become the
>field
> names going across the table. So effectively, you're pivoting on your
> 'columnname' field.
> So if you unpivot what you have, then you'll get something where your
> column
> names will be one of the fields, with 4 rows per original record.
> Does this help explain why UNPIVOT is what you want?
> Rob
> "Brian Henry" wrote:
>|||I'm not really sure how to "unpivot" my table back into its original form,
I'm probably just not understanding the syntax correctly...
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:BCFB0265-009D-4D19-891D-CB0FEFBBD1D2@.microsoft.com...
>A pivot table is one where the values in a particular column become the
>field
> names going across the table. So effectively, you're pivoting on your
> 'columnname' field.
> So if you unpivot what you have, then you'll get something where your
> column
> names will be one of the fields, with 4 rows per original record.
> Does this help explain why UNPIVOT is what you want?
> Rob
> "Brian Henry" wrote:
>|||The more and more I look into this the more I can't figure out how unpivot
would work... I don't know why I think that, but I can't figure out how
you'd pivot the ColumnHeader text field to the column name from each row...
could you possibly provide an example of this? Here is an example table and
data... and what it should look like in the end
DDL
--
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Test](
[FileFormatID] [bigint] NOT NULL,
[FileID] [bigint] NOT NULL,
[RowID] [bigint] NOT NULL,
[ColumnName] [nvarchar](50) NOT NULL,
[ColumnData] [nvarchar](max) NOT NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[Test]') AND name = N'IX_Test')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Test]
(
[FileFormatID] ASC,
[ColumnName] ASC,
[FileID] ASC,
[RowID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
Sample Data
---
1 1 1 ID 20123
1 1 1 Name Test Co Corp.
1 1 1 Value $123.23
1 1 2 ID 20124
1 1 2 Name Acme Co.
1 1 2 Value $235.56
1 1 3 ID 20125
1 1 3 Name Indy Co.
1 1 3 Value $431.21
When you say I want with the data above the record with the fileformat ID 1
and File ID 1 you should get back
ID NAME VALUE
20123 Test Co Corp. $123.23
20124 Acme Co. $235.56
20125 Indy Co. $431.21
If I understand you right I SHOULD be able to do this with UNPIVOT, I just
dont get how...|||I see that it can convert columns to rows, but I dont need that path, I need
the opposite, converting rows to columns|||I've done stuff like this before but slightly differently. Presuming your
flat file has been imported into the server already, I've added a couple of
other columns to make sure you capture where the data came from in the first
place:
DROP TABLE #raw_data
CREATE TABLE #raw_data
(
raw_data_id INT UNIQUE IDENTITY NOT NULL,
raw_name VARCHAR(50) NOT NULL,
raw_id INT NOT NULL,
raw_type CHAR(4) NOT NULL,
raw_info VARCHAR(50) NOT NULL
)
SET NOCOUNT ON
INSERT INTO #raw_data VALUES ( 'Name1', 5, 'ABCD', 'FirstRecord' )
INSERT INTO #raw_data VALUES ( 'Name2', 6, 'DEFG', 'SecondRecord' )
SET NOCOUNT OFF
-- SELECT *
-- FROM #raw_data
DROP TABLE #FlatFileRecords
CREATE TABLE #FlatFileRecords
(
ffr_id INT UNIQUE IDENTITY( 5000000, 1 ) NOT NULL,
source_file_id INT NOT NULL,
raw_data_id INT NOT NULL,
column_name SYSNAME,
column_data NVARCHAR(300)
)
-- Split the records
INSERT INTO #FlatFileRecords ( source_file_id, raw_data_id, column_name,
column_data )
SELECT
1 AS source_file_id, -- Hard-typed file id unless you store it in #raw_data
raw_data_id,
'raw_name',
raw_name
FROM #raw_data
INSERT INTO #FlatFileRecords ( source_file_id, raw_data_id, column_name,
column_data )
SELECT
1 AS source_file_id, -- Hard-typed file id unless you store it in #raw_data
raw_data_id,
'raw_type',
raw_type
FROM #raw_data
INSERT INTO #FlatFileRecords ( source_file_id, raw_data_id, column_name,
column_data )
SELECT
1 AS source_file_id, -- Hard-typed file id unless you store it in #raw_data
raw_data_id,
'raw_info',
raw_info
FROM #raw_data
-- List results
SELECT *
FROM #FlatFileRecords
ORDER BY raw_data_id, column_name
Have a look and let me know what you think. Please note, the above is
sample code only and not intended as a fully fledged working code!
Damien
"Brian Henry" wrote:

> Say I have a flat file like this
>
> Name ID Type Info
> ===== == ====== =======
> Name1 5 ABCD FirstRecord
> Name2 6 DEFG SecondRecord
>
> Well I want to save that into a database table like this
> CREATE TABLE FlatFileRecords
> (
> ColumnID UNIQUEIDENTIFIER,
> RecordSetID BIGINT,
> ColumnName nvarchar(100),
> ColumnData NVARCHAR(300)
> )
> Column ID would be a unique ID for the row
> RecordSetID would split apart different flat files
> Column Name would be the name of the column (from header)
> ColumnData would be data under the column header
> so it would be stored in the table like this
>
> {GUID1} 1 Name Name1
> {GUID1} 1 ID 5
> {GUID1} 1 TYPE ABCD
> {GUID1} 1 INFO FirstRecord
> {GUID2} 1 Name Name2
> {GUID2} 1 ID 6
> {GUID2} 1 TYPE DEFG
> {GUID2} 1 INFO SecondRecord
> so basicly its taking a flat file and splitting it up into chunks that can
> be eventually reassembled... by question is there any easy to reassemble
> them in SQL Server? Any kind of definition i could set up to translate the
m
> based on ID which everything in the ID would always have the same columns,
> so if a certain ID comes up it reassembles the data from the SQL data tabl
e
> form into the flat file type form? just in a data table so the flat file
> above would come back in a temp table? I dont know if this is possible, if
> you can think of anything please let me know or if this makes no sense let
> me know because I typed this up fast off the top of my head, didnt have ti
me
> to really think it through yet
>
>