Tricky data manipulation scenario

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  May 8, 2006 at 8:27 am

  #190483

  Guys,

  I have following scenario

  gid sname fname

  ________________________________________

  1 'Mathew Levey' 'Mark Bell'

  1 'San Gordon' 'Mark Bell'

  1 'Larry Gomes' 'Mark Bell'

  1 'Mathew Levey' 'Carol schnyder'

  I want to split the above data into a table A

  table A populated with rows with rows for each distinct occurence of sname and fname ignoring

  any blanks '', Also sname, fname should be identified by field role populated with 1 for sname

  and 2 for fname

  gid Name Role

  ____________________________________

  1 'Mathew Levey' 1

  1 'San Gordon' 1

  1 'Larry Gomes' 1

  1 'Mark Bell' 2

  1 'Carol Schnyder' 2

  I tried doing this by stored procedure using gid and autogenerated id (1, 1) but doesnt seem to work.

  Any ideas/suggesting how this can acheived

  Thanks

• David McFarland

  SSChampion

  Points: 11815

  More actions

  May 8, 2006 at 9:29 am

  #636551

  What do you want "field role" to be set at if the name shows up in "fname" in one place and "sname" in another?

  Also, is "gid" always equal to "1"?

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  May 8, 2006 at 9:34 am

  #636553

  Try this:

  declare @Input table(gid int, sname varchar(50), fname varchar(50))

  insert @Input values (1, 'Mathew Levey', 'Mark Bell')

  insert @Input values (1, 'San Gordon', 'Mark Bell')

  insert @Input values (1, 'Larry Gomes', 'Mark Bell')

  insert @Input values (1, 'Mathew Levey', 'Carol schnyder')

  select distinct gid, sname, 1

  from @input

  union

  select distinct gid, fname, 2

  from @input

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  May 8, 2006 at 12:59 pm

  #636596

  JeffB, I think this statement would work, I have question though

  will the data be inserted in the tablea in order that the names appear in the source table, I ask this because

  while using union clause I cannot use order by clause.

  Thanks

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  May 8, 2006 at 1:09 pm

  #636598

  SQL never guarantees the order unless an ORDER BY clause is used.  The order that they are entered or in the table is not guranteed.  Given your data, what would you like to order them on?  Is GID always 1 like in the sample data?

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  May 8, 2006 at 1:15 pm

  #636600

  If GID changes is unique to a row, then you can use the following:

  declare @Input table(gid int, sname varchar(50), fname varchar(50))

  insert @Input values (1, 'Mathew Levey', 'Mark Bell')

  insert @Input values (2, 'San Gordon', 'Mark Bell')

  insert @Input values (3, 'Larry Gomes', 'Mark Bell')

  insert @Input values (4, 'Mathew Levey', 'Carol schnyder')

  select distinct min(gid), sname, 1

  from @input

  group by sname

  union

  select min(gid), fname, 2

  from @input

  group by fname

  order by 1, 3, 2