start a new select in a case function

• csyz

  SSC Journeyman

  Points: 96

  More actions

  May 29, 2017 at 2:42 am

  #330742

  Dear NG
  I have the following issue. I need to know the code of the parent from every possible value in a table. I start with following select case:

  select
  case parentPhase = 1 then code
  else
  --> here I would Need a new select query
  -->variable1 = Parentphase
  -->select Code where Bold_ID=variable1
  end as Code
  from zzztest where Bold_ID=5144535

  here is the table. Every help is welcome.- best regards Chris
  

  CREATE TABLE zzzTest(

  Bold_ID int,Code varchar(255),Parentphase int)

  INSERT INTO zzzTest

  values ('5144471', '1000317_EV', '1');

  insert into zzztest

  values ('5144535', '100317_SUPPORT', '5144471');

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 29, 2017 at 6:46 am

  #1944486

  I think you need to use a recursive CTE in this case, so that all possible relations are created through joining to the data itself .
  if you are not familiar with recursive CTE's Dwain Camps has a decent article here: http://www.sqlservercentral.com/articles/T-SQL/90955/
  your example has only a single parent-child pair, but this would go deeper, if the rest of the data is more hierarchical that the example.

  
/*--Results
Bold_ID  Code    Parentphase
5144471  1000317_EV  1
5144535  100317_SUPPORT 5144471
*/
IF OBJECT_ID('[dbo].[zzzTest]') IS NOT NULL
DROP TABLE [dbo].[zzzTest]
GO
CREATE TABLE zzzTest(
  Bold_ID int,Code varchar(255),Parentphase int)

  INSERT INTO zzzTest
values ('5144471', '1000317_EV', '1');
insert into zzztest
values ('5144535', '100317_SUPPORT', '5144471');

  --get the magically created hierarchy
;WITH cte
AS (
SELECT Bold_ID, Code,Parentphase FROM zzzTest WHERE Parentphase = 1
UNION ALL
SELECT t1.Bold_ID, t1.Code,t1.Parentphase
FROM zzztest t1
INNER JOIN cte c1
  ON c1.Bold_ID = t1.Parentphase
)
 
SELECT *
FROM cte
ORDER BY Parentphase

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!