splilitng data in column

• Diwakar-313416

  Newbie

  Points: 3

  More actions

  March 27, 2006 at 2:04 am

  #112301

  hai all,

  This is my first question to this forum.

  here is my situtation:

  I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query

  Database : sql server

  tabel name :job_allocations

  column naME :technicain code

  Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician.

  Based on the technician code user chooses column will be updated

  if single data will be TC01

  if more than one then data will be TC01:TC02:TC03

  user can choose any number of techincian for a job

  MY problem is :How to split tha when there is multiple technician and calculate cost for the job

  Ineed it in single excecution query

  Table structure

  job_allocation table

  jobcardn_fk Technician_code

  jc01 TC01

  jc02 Tco1:Tco2:Tc03......

  I need it in

  jobcardno_fk TEchnician_code

  jco1 Tc01

  jco2 Tc01

  jco2 TC02

  jc02 Tc03

  TKs ands Regards

  Diwakar.R

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 27, 2006 at 3:37 am

  #628843

  There are a few techniques. Here's my preferred method...

  --data

  declare @t table (jobcardn_fk varchar(10), Technician_code varchar(100))

  insert @t

            select 'jc01', 'TC01'

  union all select 'jc02', 'Tco1:Tco2:Tc03'

  union all select 'jc03', 'Tco17:Tco22344'

  --numbers table

  DECLARE @numbers TABLE (i INT IDENTITY(1, 1), c char(1), marker tinyint)

  INSERT @numbers SELECT TOP 101 NULL, 0 FROM master.dbo.syscolumns

  --put them together

  select

      jobcardn_fk,

      left(

          substring(Technician_code, i, 10)+':',

          charindex(':', substring(Technician_code, i, 100) + ':')-1

      ) as Technician_code

  from @numbers, @t

  where substring(':' + Technician_code, i, 1) = ':'

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.