0
select id, ips from users;

Query result

id    ips
1     1.2.3.4,5.6.7.8
2     10.20.30.40
3     111.222.111.222,11.22.33.44
4     1.2.53.43

I'd like to run a query that produces the following output

user_id     ip
1           1.2.3.4
1           5.6.7.8
2           10.20.30.40
3           111.222.111.222
3           11.22.33.44   
4           1.2.53.43
maček
  • 76,434
  • 37
  • 167
  • 198
  • I hope you like working with temp tables and SPROCs. Something tells me this database needs to be normalized--disparately. – Brad Christie Feb 23 '11 at 20:22
  • If you can, redesign this part of the db so that it is normalized. The 1:n relationship from user_id to ip should be stored in its own table. – Ted Hopp Feb 23 '11 at 20:24
  • @Brad Christie, the database is generally ok. This is sort of a one-off normalization problem that I've faced with it. – maček Feb 23 '11 at 20:24

3 Answers3

2

If you don't mind using a cursor, here's an example:


set nocount on;
-- create sample table, @T
declare @T table(id int, ips varchar(128));
insert @T values(1,'1.2.3.4,5.6.7.8')
insert @T values(2,'10.20.30.40')
insert @T values(3,'111.222.111.222,11.22.33.44')
insert @T values(4,'1.2.53.43')
insert @T values(5,'1.122.53.43,1.9.89.173,2.2.2.1')

select * from @T

-- create a table for the output, @U
declare @U table(id int, ips varchar(128));

-- setup a cursor
declare XC cursor fast_forward for select id, ips from @T
declare @ID int, @IPS varchar(128);

open XC
fetch next from XC into @ID, @IPS
while @@fetch_status = 0
begin
        -- split apart the ips, insert records into table @U
        declare @ix int;
        set @ix = 1;
        while (charindex(',',@IPS)>0)
        begin
            insert Into @U select @ID, ltrim(rtrim(Substring(@IPS,1,Charindex(',',@IPS)-1)))
            set @IPS = Substring(@IPS,Charindex(',',@IPS)+1,len(@IPS))
            set @ix = @ix + 1
        end
        insert Into @U select @ID, @IPS

    fetch next from XC into @ID, @IPS
end

select * from @U
jim31415
  • 8,588
  • 6
  • 43
  • 64
1

I don't think this is something you want to do using a query, but you'd rather do this in your presentation logic. The database is just for storing and retrieving data. Formatting data and presenting it is something to do in your presentation layer, usually combined with PHP/ASP.NET/something else.

Maarten Terpstra
  • 416
  • 3
  • 2
0

This has come up several times before. Here's one answer:

https://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows

Community
  • 1
  • 1
Daniel Ahrnsbrak
  • 1,067
  • 8
  • 15