Pivoting 1 column table

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Pivoting 1 column table

Pivoting 1 column table

Rate Topic

Display Mode

Topic Options

Author

Message

dant12

Posted Saturday, November 07, 2009 12:34 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 77, Visits: 120

Hello all. First off, im not sure if this is the forum i should be posting this on. If it's not im sorry and please direct me to the correct one.

The scenario im in right now got me puzzled as to what to do (and boy, i like being puzzled).

Ok, so i have 5 .csv files to import, all separated by TAB (\t) with different amount of columns.

The application we have in the company import the files into a single table with the amount of columns i specify in the application, but the problem is: i need to specify the maximum amount of columns(22) but instead of inserting nulls for the columns missing on the files that have lesser columns the app just keeps inserting and filling those columns, which completly ruins queries i need to run later on.

Now, i can tell the application to import everything into a single varchar column.

what i did then was to add a new column to the table to know which file is which worked well if a cursor.

i then added a CHAR(9)(tab) before and after each line so i could separate every field with a tally table

so now i got a 1 column table with everything i need separated by file type, but i need to pivot it back into actual columns so i can create 5 temp tables to work with.

im not sure this is the correct approach, perhaps something simpler can be done but at this moment i cant think of any and would love some ideas

Thiago Dantas

--
Thiago Dantas

Post #815468

Jeff Moden

Posted Saturday, November 07, 2009 8:30 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 18,291, Visits: 12,310

Double post... no discussion here, please. Keep it all together at the following URL... thanks.
http://www.sqlservercentral.com/Forums/Topic815495-338-1.aspx

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #815506

« Prev Topic | Next Topic »

Permissions