Need to bring required result

• KMPSSS

  Ten Centuries

  Points: 1052

  More actions

  July 4, 2011 at 10:49 pm

  #238972

  Hi,

  Below one is my sample Table Structure

  Declare @t table

  (

  Series varchar(50),

  S1 varchar(50),

  S2 varchar(50),

  S3 varchar(50)

  )

  Insert into @t

  Select 'Label1','','','' union

  Select 'Label2','','','' union

  Select 'Label3','','','' union

  Select 'Label4','','',''

  Select * from @t

  Output Out

  Series S1 S2 S3

  Label1

  Label2

  Label3

  Label4

  Actually Label1,2,3 were fetch from table, the value may change. So My required output

  is like below

  Series S1 S2 S3

  Label1

  Open

  Close

  Label2

  Open

  Close

  Label3

  Open

  Close

  Label4

  Open

  Close

  Need to bring Open and close between of the Every labels. How to bring achieve the above result.Kindly guide me,through sample codes

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  July 5, 2011 at 12:17 am

  #1348067

  Could do this with a cross join. Though depends a bit on what you want for values in the S1, S2, S3 columns for the "fake" rows.

  Select case x.nr when 1 then Series when 2 then 'Open' when 3 then 'Close' end Series, S1, S2, S3

  from @t

  cross join (select 1 nr union all select 2 union all select 3) x

  /T