Joining Substring Queries

• Greg Maruna

  Old Hand

  Points: 345

  More actions

  March 1, 2008 at 7:19 am

  #183386

  I have a table with a column [Lane] that contains two values split by a space. ("OH AL").

  I need to get a query that shows [State], CountFrom, CountTo as results. Here are the two individual queries that get To counts and From counts. How can I join these results into a single result set? This needs to be a single statement that I can paste into a GUI that accepts TSQL. Thanks.

  select substring([LANE],1,2) AS STATEFROM, COUNT(substring([LANE],1,2)) AS FROMSTATECOUNT

  FROM myTable

  GROUP BY substring([LANE],1,2)

  ORDER BY substring([LANE],1,2)

  select substring([LANE],4,2) AS STATETO, COUNT(substring([LANE],1,2)) AS TOSTATECOUNT

  FROM myTable

  GROUP BY substring([LANE],4,2)

  ORDER BY substring([LANE],4,2)

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  March 1, 2008 at 9:20 am

  #784165

  These are ugly answers but may help you along.

  To simplify test code, I added computed columns to do the splitting of Lane

  create table myTable (Lane char(5), stfr as left(lane, 2), stto as right(lane, 2))

  insert into myTable select 'ak ca'

  insert into myTable select 'ak ct'

  insert into myTable select 'nv tn'

  insert into myTable select 'nv tn'

  insert into myTable select 'nv ms'

  -- select * from myTable

  select coalesce(stfr.st, stto.st) as state, stfr.fromstcount, stto.tostcount

  from

  (

  select stfr as st, count(9) fromstcount

  from myTable

  group by stfr

  ) stfr

  full outer join

  (

  select stto as st, count(9) tostcount

  from myTable

  group by stto

  ) stto

  on stfr.st = stto.st

  order by state, stfr.fromstcount, stto.tostcount

  -- nearly the same as the query above but tells you to or from in the last column.

  select coalesce(stfr.st, stto.st) as state, coalesce(stfr.fromstcount, stto.tostcount) as cnt,

  case

  when stfr.st is not null then 'isFrom'

  when stto.st is not null then 'isTo'

  else '??'

  end as FrOrTo

  from

  (

  select stfr as st, count(9) fromstcount

  from myTable

  group by stfr

  ) stfr

  full outer join

  (

  select stto as st, count(9) tostcount

  from myTable

  group by stto

  ) stto

  on stfr.st = stto.st

  order by FrOrTo, state, cnt

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  March 1, 2008 at 10:19 am

  #784170

  Greg...

  What do you want to see for output?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  March 1, 2008 at 10:43 am

  #784171

  Instead of a JOIN, using a UNION contruct is easier and will run faster. Note that the constants for from/to are set to 0 or 1 appropriately.

  SELECTStateCode

  ,SUM(StateFromCnt) AS StateFromCnt

  ,SUM(StateToCnt) AS StateToCnt

  FROM(SELECTleft(lane, 2) AS StateCode

  ,COUNT(*)AS StateFromCnt

  ,0AS StateToCnt

  FROM#myTable

  GROUP BY left(lane, 2)

  UNION ALL

  SELECTright(lane, 2) AS StateCode

  ,0AS StateFromCnt

  ,COUNT(*)AS StateToCnt

  FROM#myTable

  GROUP BY right(lane, 2)

  )AS StateAct

  GROUP BY StateCode

  SQL = Scarcely Qualifies as a Language