Display column output over two or more rows

• Lesley Harvey

  Newbie

  Points: 7

  More actions

  March 23, 2004 at 5:01 am

  #101929

  I have a very simple select statement:

  SELECT job.job_id, job.job_desc

  FROM job

  job_id being an integer and job_desc being varchar(1000)

  What I want to do is force the output of the select statement so that the results would display the job_id and the job_desc but the job_desc must be over two rows i.e

  job_id     job_desc

  -----     ---------

  12345    This job is all about.................carries on until 500

               and the next 500 would carry on here

  Thanks, Lesley

• Rodrigo Acosta

  SSC-Insane

  Points: 21261

  More actions

  March 23, 2004 at 5:46 am

  #500006

  Can try something like this:

  SELECT Job_Id, SUBSTRING(job_Desc,1, 500) + CHAR(13) + CHAR(10) + SUBSTRING(Job_Desc, 501, LEN(Job_Desc))

  FROM Job

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  March 23, 2004 at 5:55 am

  #500009

  SELECT (CASE WHEN seq = 1

  THEN CAST(job_id1 as varchar)

  ELSE '' END) AS job_id,

  job_desc

      FROM (SELECT job_id AS job_id1, 1 as seq, LEFT(job_desc,500) AS job_desc

      FROM job

      UNION

      SELECT job_id AS job_id1, 2 as seq,

      (CASE WHEN LEN(job_desc) > 500

      THEN SUBSTRING(job_desc,501,LEN(job_desc)-500)

      ELSE ''

      END)

      FROM job) a

  ORDER BY a.job_id1, a.seq

  But wouldn't be better to do this at the presentation layer (application)

  Far away is close at hand in the images of elsewhere.
  Anon.