How to alternate between two values in a column?

• azdeji

  SSCrazy

  Points: 2535

  More actions

  August 12, 2013 at 2:41 am

  Go to Answer

  #296906

  ShortTypeOutstandingShort_Id

  Long 2333.121

  Short 2333.121

  Long 2333.122

  Short 2333.122

  Long 2333.123

  Short 2333.123

  Long 2333.124

  Short 2333.124

  Long 2333.125

  Short 2333.125

  Is also possible to generate sql test data like the one above for 10000 rows without a table? By generating 10000 numbered rows in the Short_ID column at runtime would populate the other columns.

  All but two columns will have the same values repeating and ShortType will alternate between “Long” and “Short”.

  Short_Id numbers will be repeat twice up to 10000

  SQL Code so far

  Select

  Long' AS ShortType ---- Long,Short everyother row

  ,'2333.12' AS Outstanding

  ,' ' AS Short_ID ?

  Thanks for the help.

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  August 12, 2013 at 4:20 am

  Answer

  #1640499

  Try this. Thanks to Jeff Moden (and probably others) for the compounding CTEs.

  WITH E1(N) AS (

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

  ), --10E+1 or 10 rows

  E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

  E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

  rows as (

  select rn = row_number() over (order by N)

  from e4)

  select shortType = (case (rn / 2) * 2 when rn then 'Short' else 'Long' end),

  Outstanding = 2333.12,

  ShortId = (rn + 1) / 2

  from rows

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 12, 2013 at 4:20 am

  #1640500

  DECLARE @Outstanding DECIMAL(10,2) = 2333.12

  ;WITH Tens AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t (n)),

  iTally AS (SELECT n = 0 FROM Tens a, Tens b, Tens c, Tens d)

  SELECT

  d.ShortType,

  Outstanding = @Outstanding,

  t.Short_Id

  FROM (VALUES ('Long'),('Short')) d (ShortType)

  CROSS JOIN

  (SELECT Short_Id = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM iTally) t

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• azdeji

  SSCrazy

  Points: 2535

  More actions

  August 12, 2013 at 3:31 pm

  #1640717

  Thank you both, just what I need.