SQL Power of 2

• Sharon Rimer

  SSC-Addicted

  Points: 427

  More actions

  November 15, 2015 at 8:56 am

  #313130

  Hi,

  Can any one tell me how can I get from this

  CREATE TABLE #Param (Name sysname NOT NULL, Value NVARCHAR(max)NULL, DefaultValue NVARCHAR(50) null)

  INSERT #Param

  ( Name, Value, DefaultValue )

  VALUES ( '@ID', -- Name - sysname

  N'1', -- Value - nvarchar(max)

  NULL -- DefaultValue - nvarchar(50)

  ),('@XX',NULL,N'1'),('@YY','''DDD''',N'1')

  to this

  

  Thanks for the helpers

  Thank you,
  Rimer Sharon DBA

• Phil Parkin

  SSC Guru

  Points: 247001

  More actions

  November 15, 2015 at 2:50 pm

  #1839861

  Can you describe the logic please?

  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

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  November 16, 2015 at 3:38 am

  #1839900

  My guess is 2 (value, defaultValue) ** 3 (number of rows) = 8

  select [@YY],[@XX],[@ID]

  from

  (select v as [@ID]

  from #Param

  cross apply(values (value),(DefaultValue) ) t(v)

  where Name ='@ID') _1,

  (select v as [@XX]

  from #Param

  cross apply(values (value),(DefaultValue) ) t(v)

  where Name ='@XX') _2,

  (select v as [@YY]

  from #Param

  cross apply(values (value),(nullif(DefaultValue,'1')) ) t(v)

  where Name ='@YY') _3;