display multiple value in one column to single row

I have below table with coulmn cityname,operatorname and prefix:- Prefix column contains multiple values with range of that.

Cityname operatorname     prefix
-------- ---------------- -----------------------------------------------------------
Kolkata  Unitech          90620-90629 82960-82969
Kolkata  Datacom          90730-90739
Kolkata  BSNL             94330-94339 94320-94325
Kolkata  Loop             91100-91109
Kolkata  Reliance Telecom 98830-98839 96810-96819 88200-88209
Mumbai   BPL Mobile       98210-98219 96640-96649 97730-97739 98700-98709 80820-80829
Mumbai   Bharti Airtel    98670-98679 98920-98929 99670-99674 99870-99878 90040-90049

but i want each preix on new row also there are range between for e.g. 9开发者_运维问答1100-91109 means range from 91100,91101 91102 91103.....till 91109..

belwo is the output i want for e.g. kolkata Unitech Wireless

Cityname  operatorname      prefix 
--------  ----------------  ------
Kolkata   Unitech Wireless  90620
Kolkata   Unitech Wireless  90621
Kolkata   Unitech Wireless  90622
Kolkata   Unitech Wireless  90623
.....
.....
.....
Kolkata   Unitech Wireless  90629
Kolkata   Unitech Wireless  82960
Kolkata   Unitech Wireless  82961
Kolkata   Unitech Wireless  82962
.....
.....
.....
Kolkata   Unitech Wireless  82969

then followed by kolkata datacom solutions and so on...

Please need help to write down query in Sql server 2008

Please suggest as early as possible.

---

I can see this solved in two logical steps:

1. Split every prefix range list into a row set of prefix ranges, i.e. every row like

   city operator prefix-range1 prefix-range2 prefix-range3 …

   gets split into

   city operator prefix-range1
city operator prefix-range2
city operator prefix-range3
city …

2. Expand every prefix range item like

   city operator Prefix1-PrefixN

   into a series of rows like this:

   city operator Prefix1
city operator Prefix2
city operator …
city operator PrefixN-1
city operator PrefixN

Below is an attempt at implementing the said logic:

WITH data (Cityname, operatorname, prefix) AS (  /* this is just a sample data definition */
  SELECT 'Kolkata', 'Unitech         ', '90620-90629 82960-82969' UNION ALL
  SELECT 'Kolkata', 'Datacom         ', '90730-90739' UNION ALL
  SELECT 'Kolkata', 'BSNL            ', '94330-94339 94320-94325' UNION ALL
  SELECT 'Kolkata', 'Loop            ', '91100-91109' UNION ALL
  SELECT 'Kolkata', 'Reliance Telecom', '98830-98839 96810-96819 88200-88209' UNION ALL
  SELECT 'Mumbai ', 'BPL Mobile      ', '98210-98219 96640-96649 97730-97739 98700-98709 80820-80829' UNION ALL
  SELECT 'Mumbai ', 'Bharti Airtel   ', '98670-98679 98920-98929 99670-99674 99870-99878 90040-90049'
),
SplitGroups AS (  /* this is where the list is split into separate ranges */
  SELECT
    d.Cityname, d.operatorname,
    StartPrefix = CAST(LEFT (x.PrefixGroup, 5) AS int),
    EndPrefix   = CAST(RIGHT(x.PrefixGroup, 5) AS int)
  FROM (
    SELECT
      Cityname, operatorname,
      prefixlist = CAST('<i>'+REPLACE(prefix, ' ', '</i><i>')+'</i>' AS xml)
    FROM data
  ) d
  CROSS APPLY (
    SELECT
      i.value('.', 'varchar(max)') AS PrefixGroup
    FROM d.prefixlist.nodes('i') x (i)
  ) x
)
SELECT  /* the final SELECT expands the ranges into single prefix rows */
  g.Cityname, g.operatorname,
  prefix = g.StartPrefix + v.number
FROM SplitGroups g
  INNER JOIN master..spt_values v on v.type = 'P'
    AND v.number BETWEEN 0 AND g.EndPrefix - g.StartPrefix

It works in my SQL Server 2008 R2 as expected, but the following assumptions have been made:

1. All the original prefix values are formatted uniformly and consistently:

   • the ranges are separated by a single space;

   • there are no spaces apart from those separating the ranges;

   • every range is an integer, followed by a hyphen (-), followed by an integer.

2. Every integer (prefix) contains exactly 5 digits.

3. Every range spans no more than 2048 prefixes, i.e. the difference between the ending prefix and the starting prefix never exceeds 2047. This is the limitation of the master..spt_values table. You can replace it with your own numbers table if you need support for more than 2048 prefixes in a range.

---

References:

• Using Common Table Expressions

• Split sql string into words (one of the many string splitting questions here on SO)

• What is the purpose of system table table master..spt_values and what are the meanings of its values?

• The "Numbers" or "Tally" Table: What it is and how it replaces a loop. ('numbers table' is the capacity in which master..spt_values has been employed by this solution)