Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Challenge-1 text column to many columns


Okay, brainiacs, I need some help.  I have a field in a database that is
defined as text and contains some characters and some bars (|).  The bar
separates the values in the field into columns.  For example,

5235D4793912C9O|120543||385

would mean 4 columns equal to:

A: 5235D4793912C9O
B: 120543
C:
D: 385

Does anyone know how I can change this single column into the 4 columns in a
single SQL statement?  And the number of columns can vary up to a large
number, let's say 50 for now.  I really don't want to use a stored procedure
or variables if possible.

Stephanie

Look up PARSENAME function in SQL Server Books Online. You can replace '|'
with '.' and use it to extract individual values. Alternatively, you can use
CHARINDEX & SUBSTRING functions to get them too. The archives of this
newsgroup should have several examples.

--
Anith

-----------------------------------------------Reply-----------------------------------------------

Adopted from From Wikipedia:

Flotsam and jetsam are words that describe goods and concepts of potential
value that have been thrown into the ocean.
In modern usage, flotsam also includes driftwood, logs and string function
debris, and books and websites, much of which enters through the action
of shore surf, rain, wind, misguided sql pundits and vendors.

Flotsam is an increasing environmental problem, particularly for developers.
Discarded concepts and string functions and other flotsam can entangle and
drown fish,
marine mammals and programmers. Some seabirds, and particularly developers,
also
consume flotsam by mistake, leading to health problems.

Derelict is concepts which has been abandoned and deserted at sea by
those who were in charge without any hope of recovering it.

Create some data in Pubs db in S2005.

create table MsSteph (RowID int primary key,DelStr varchar(100))
insert MsSteph values(1,'5235D4793912C9O|120543||385')
insert MsSteph values(2,'666||ABC')

Make the data available to Dataphor.
www.alphora.com

Reconcile(Name("Pubs")); //

Sql without flotsam.

select
 (
  (
   numbers //table of digits (num) from 0-100.
    times //Like a cross join
     (                  //Get the count (Cnt) of the items in the list.
      MsSteph add{DelStr.Split({'|'}).Count() Cnt}
      with {ShouldSupport='false'} //S2005 doesn't know Split form spit -:)
     )
       where num<=Cnt-1
    )           //Get each item in the list with Split operator, Del is '|'.
       {RowID,num Index,DelStr.Split({'|'})[num] Str}
  )
    //Join result to a table based on a list for a character index (AlphaID)
    //Join is a natural join on Index.
     join
       ToTable({'A','B','C','D','E','F'},'AlphaID','Index')
        {RowID,Index+1 Index,AlphaID,Str}
          order by {RowID,Index};

RowID Index AlphaID Str
----- ----- ------- ---------------
1     1     A       5235D4793912C9O
1     2     B       120543
1     3     C
1     4     D       385
2     1     A       666
2     2     B
2     3     C       ABC

www.beyondsql.blogspot.com

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc